Multi value parameters

Can anyone give me the syntax to pass mutliple parameters into a stored
procedure? I know that you have to use IN, but I cannot get it to work. Thank
you in advance.Create your Dataset, select the Proc and execute it in Reporting Services
Designer. It will create your parameters for you, then you can go into the
Report > Parameters section and adjust the settings from there. What are you
using IN for? Can you post some code that you are using?
"KimB" wrote:
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work. Thank
> you in advance.|||As a side note, RS 2005 supports multi-value parameters natively, so no need
of an expression-based query.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work.
> Thank
> you in advance.|||I'm trying to pass a multi-value to a stored procedure as well but it isn't
working. The documentation says that using a SP with multi-value isn't
supported. Huh? Most of my reports use SPs.
Can this be done? Is there a workaround?
This was the #1 feature I was waiting for in RS2005.
Thanks in advance!
"Teo Lachev [MVP]" wrote:
> As a side note, RS 2005 supports multi-value parameters natively, so no need
> of an expression-based query.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > procedure? I know that you have to use IN, but I cannot get it to work.
> > Thank
> > you in advance.
>
>|||What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> I'm trying to pass a multi-value to a stored procedure as well but it
> isn't
> working. The documentation says that using a SP with multi-value isn't
> supported. Huh? Most of my reports use SPs.
> Can this be done? Is there a workaround?
> This was the #1 feature I was waiting for in RS2005.
> Thanks in advance!
>
> "Teo Lachev [MVP]" wrote:
>> As a side note, RS 2005 supports multi-value parameters natively, so no
>> need
>> of an expression-based query.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > Can anyone give me the syntax to pass mutliple parameters into a stored
>> > procedure? I know that you have to use IN, but I cannot get it to work.
>> > Thank
>> > you in advance.
>>|||I understand now-- thanks-- and this worked great for me.
It's too bad the documentation doesn't provide this simple information given
that the multi-value feature is bound to be a highly-desired feature.
Thanks again!
"Bruce L-C [MVP]" wrote:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > I'm trying to pass a multi-value to a stored procedure as well but it
> > isn't
> > working. The documentation says that using a SP with multi-value isn't
> > supported. Huh? Most of my reports use SPs.
> >
> > Can this be done? Is there a workaround?
> >
> > This was the #1 feature I was waiting for in RS2005.
> >
> > Thanks in advance!
> >
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> As a side note, RS 2005 supports multi-value parameters natively, so no
> >> need
> >> of an expression-based query.
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> >> > procedure? I know that you have to use IN, but I cannot get it to work.
> >> > Thank
> >> > you in advance.
> >>
> >>
> >>
>
>|||I've been using the charlist_to_table function to return my parameters in a
table and it works well but I can't get it to return more than 800 rows--
even if the string sent contains more than 800 entries.
I checked and RS is sending ALL of the selected entries (868 in this case)
but the function is only returning 800 rows.
I used a similiar function from elsewhere on the net and it also returned
only 800 rows.
Is anyone aware of a limitation here-- I don't see any limitation in the
function?
Thanks!
"Michael Creamer" wrote:
> I understand now-- thanks-- and this worked great for me.
> It's too bad the documentation doesn't provide this simple information given
> that the multi-value feature is bound to be a highly-desired feature.
> Thanks again!
>
> "Bruce L-C [MVP]" wrote:
> > What doesn't work has nothing really to do with RS but has to do with Stored
> > Procedures in SQL Server. You cannot do the following in a stored procedure.
> >
> > Let's say you have a Parameter called @.MyParams
> >
> > Now you can map that parameter to a multi-value parameter but if in your
> > stored procedure you try to do this:
> >
> > select * from sometable where somefield in (@.MyParams)
> >
> > It won't work. Try it. Create a stored procedure and try to pass a
> > multi-value parameter to the stored procedure. It won't work.
> >
> > What you can do is to have a string parameter that is passed as a multivalue
> > parameter and then change the string into a table.
> >
> > This technique was told to me by SQL Server MVP, Erland Sommarskog
> > For example I have done this
> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >
> > So note this is NOT an issue with RS, it is strictly a stored procedure
> > issue.
> >
> > Here is the function:
> > CREATE FUNCTION charlist_to_table
> > (@.list ntext,
> > @.delimiter nchar(1) = N',')
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > str varchar(4000),
> > nstr nvarchar(2000)) AS
> >
> > BEGIN
> > DECLARE @.pos int,
> > @.textpos int,
> > @.chunklen smallint,
> > @.tmpstr nvarchar(4000),
> > @.leftover nvarchar(4000),
> > @.tmpval nvarchar(4000)
> >
> > SET @.textpos = 1
> > SET @.leftover = ''
> > WHILE @.textpos <= datalength(@.list) / 2
> > BEGIN
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > SET @.textpos = @.textpos + @.chunklen
> >
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >
> > WHILE @.pos > 0
> > BEGIN
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > END
> >
> > SET @.leftover = @.tmpstr
> > END
> >
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > ltrim(rtrim(@.leftover)))
> > RETURN
> > END
> >
> > GO
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > isn't
> > > working. The documentation says that using a SP with multi-value isn't
> > > supported. Huh? Most of my reports use SPs.
> > >
> > > Can this be done? Is there a workaround?
> > >
> > > This was the #1 feature I was waiting for in RS2005.
> > >
> > > Thanks in advance!
> > >
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > >> need
> > >> of an expression-based query.
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > >> > Thank
> > >> > you in advance.
> > >>
> > >>
> > >>
> >
> >
> >|||It is working now.
I had been using a similar function, UtilSplit, but I've now switched to the
charlist_to_table function and all the rows are being returned correctly.
"Michael Creamer" wrote:
> I've been using the charlist_to_table function to return my parameters in a
> table and it works well but I can't get it to return more than 800 rows--
> even if the string sent contains more than 800 entries.
> I checked and RS is sending ALL of the selected entries (868 in this case)
> but the function is only returning 800 rows.
> I used a similiar function from elsewhere on the net and it also returned
> only 800 rows.
> Is anyone aware of a limitation here-- I don't see any limitation in the
> function?
> Thanks!
> "Michael Creamer" wrote:
> > I understand now-- thanks-- and this worked great for me.
> >
> > It's too bad the documentation doesn't provide this simple information given
> > that the multi-value feature is bound to be a highly-desired feature.
> >
> > Thanks again!
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What doesn't work has nothing really to do with RS but has to do with Stored
> > > Procedures in SQL Server. You cannot do the following in a stored procedure.
> > >
> > > Let's say you have a Parameter called @.MyParams
> > >
> > > Now you can map that parameter to a multi-value parameter but if in your
> > > stored procedure you try to do this:
> > >
> > > select * from sometable where somefield in (@.MyParams)
> > >
> > > It won't work. Try it. Create a stored procedure and try to pass a
> > > multi-value parameter to the stored procedure. It won't work.
> > >
> > > What you can do is to have a string parameter that is passed as a multivalue
> > > parameter and then change the string into a table.
> > >
> > > This technique was told to me by SQL Server MVP, Erland Sommarskog
> > > For example I have done this
> > > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> > >
> > > So note this is NOT an issue with RS, it is strictly a stored procedure
> > > issue.
> > >
> > > Here is the function:
> > > CREATE FUNCTION charlist_to_table
> > > (@.list ntext,
> > > @.delimiter nchar(1) = N',')
> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > > str varchar(4000),
> > > nstr nvarchar(2000)) AS
> > >
> > > BEGIN
> > > DECLARE @.pos int,
> > > @.textpos int,
> > > @.chunklen smallint,
> > > @.tmpstr nvarchar(4000),
> > > @.leftover nvarchar(4000),
> > > @.tmpval nvarchar(4000)
> > >
> > > SET @.textpos = 1
> > > SET @.leftover = ''
> > > WHILE @.textpos <= datalength(@.list) / 2
> > > BEGIN
> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > > SET @.textpos = @.textpos + @.chunklen
> > >
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >
> > > WHILE @.pos > 0
> > > BEGIN
> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > END
> > >
> > > SET @.leftover = @.tmpstr
> > > END
> > >
> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > > ltrim(rtrim(@.leftover)))
> > > RETURN
> > > END
> > >
> > > GO
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > > isn't
> > > > working. The documentation says that using a SP with multi-value isn't
> > > > supported. Huh? Most of my reports use SPs.
> > > >
> > > > Can this be done? Is there a workaround?
> > > >
> > > > This was the #1 feature I was waiting for in RS2005.
> > > >
> > > > Thanks in advance!
> > > >
> > > >
> > > > "Teo Lachev [MVP]" wrote:
> > > >
> > > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > > >> need
> > > >> of an expression-based query.
> > > >>
> > > >> --
> > > >> HTH,
> > > >> ---
> > > >> Teo Lachev, MVP, MCSD, MCT
> > > >> "Microsoft Reporting Services in Action"
> > > >> "Applied Microsoft Analysis Services 2005"
> > > >> Home page and blog: http://www.prologika.com/
> > > >> ---
> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > > >> > Thank
> > > >> > you in advance.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||I had passed on your question to the author of the charlist_to_table
function. I'll tell him nevermind.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:540D9E63-DD7F-478B-89E1-D36B6BF1FC4B@.microsoft.com...
> It is working now.
> I had been using a similar function, UtilSplit, but I've now switched to
> the
> charlist_to_table function and all the rows are being returned correctly.
> "Michael Creamer" wrote:
>> I've been using the charlist_to_table function to return my parameters in
>> a
>> table and it works well but I can't get it to return more than 800 rows--
>> even if the string sent contains more than 800 entries.
>> I checked and RS is sending ALL of the selected entries (868 in this
>> case)
>> but the function is only returning 800 rows.
>> I used a similiar function from elsewhere on the net and it also returned
>> only 800 rows.
>> Is anyone aware of a limitation here-- I don't see any limitation in the
>> function?
>> Thanks!
>> "Michael Creamer" wrote:
>> > I understand now-- thanks-- and this worked great for me.
>> >
>> > It's too bad the documentation doesn't provide this simple information
>> > given
>> > that the multi-value feature is bound to be a highly-desired feature.
>> >
>> > Thanks again!
>> >
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What doesn't work has nothing really to do with RS but has to do with
>> > > Stored
>> > > Procedures in SQL Server. You cannot do the following in a stored
>> > > procedure.
>> > >
>> > > Let's say you have a Parameter called @.MyParams
>> > >
>> > > Now you can map that parameter to a multi-value parameter but if in
>> > > your
>> > > stored procedure you try to do this:
>> > >
>> > > select * from sometable where somefield in (@.MyParams)
>> > >
>> > > It won't work. Try it. Create a stored procedure and try to pass a
>> > > multi-value parameter to the stored procedure. It won't work.
>> > >
>> > > What you can do is to have a string parameter that is passed as a
>> > > multivalue
>> > > parameter and then change the string into a table.
>> > >
>> > > This technique was told to me by SQL Server MVP, Erland Sommarskog
>> > > For example I have done this
>> > > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> > >
>> > > So note this is NOT an issue with RS, it is strictly a stored
>> > > procedure
>> > > issue.
>> > >
>> > > Here is the function:
>> > > CREATE FUNCTION charlist_to_table
>> > > (@.list ntext,
>> > > @.delimiter nchar(1) = N',')
>> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > > str varchar(4000),
>> > > nstr nvarchar(2000)) AS
>> > >
>> > > BEGIN
>> > > DECLARE @.pos int,
>> > > @.textpos int,
>> > > @.chunklen smallint,
>> > > @.tmpstr nvarchar(4000),
>> > > @.leftover nvarchar(4000),
>> > > @.tmpval nvarchar(4000)
>> > >
>> > > SET @.textpos = 1
>> > > SET @.leftover = ''
>> > > WHILE @.textpos <= datalength(@.list) / 2
>> > > BEGIN
>> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
>> > > @.chunklen)
>> > > SET @.textpos = @.textpos + @.chunklen
>> > >
>> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >
>> > > WHILE @.pos > 0
>> > > BEGIN
>> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > > END
>> > >
>> > > SET @.leftover = @.tmpstr
>> > > END
>> > >
>> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > > ltrim(rtrim(@.leftover)))
>> > > RETURN
>> > > END
>> > >
>> > > GO
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
>> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
>> > > > I'm trying to pass a multi-value to a stored procedure as well but
>> > > > it
>> > > > isn't
>> > > > working. The documentation says that using a SP with multi-value
>> > > > isn't
>> > > > supported. Huh? Most of my reports use SPs.
>> > > >
>> > > > Can this be done? Is there a workaround?
>> > > >
>> > > > This was the #1 feature I was waiting for in RS2005.
>> > > >
>> > > > Thanks in advance!
>> > > >
>> > > >
>> > > > "Teo Lachev [MVP]" wrote:
>> > > >
>> > > >> As a side note, RS 2005 supports multi-value parameters natively,
>> > > >> so no
>> > > >> need
>> > > >> of an expression-based query.
>> > > >>
>> > > >> --
>> > > >> HTH,
>> > > >> ---
>> > > >> Teo Lachev, MVP, MCSD, MCT
>> > > >> "Microsoft Reporting Services in Action"
>> > > >> "Applied Microsoft Analysis Services 2005"
>> > > >> Home page and blog: http://www.prologika.com/
>> > > >> ---
>> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > > >> > Can anyone give me the syntax to pass mutliple parameters into a
>> > > >> > stored
>> > > >> > procedure? I know that you have to use IN, but I cannot get it
>> > > >> > to work.
>> > > >> > Thank
>> > > >> > you in advance.
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >

multi parameters

Hi,
I want to keyin many values in a parameter textbox, and I try the syntax
like following but it doesn't work, and response the syntax error, why?
SELECT * FROM Product
WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value +"))
Is any mistake in my syntax or how should I modify it?
Thanks!
AngiAngi,
I presume your query string is actually
="SELECT * FROM Product" +
"WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
Parameters!Month.Value + "))"
and that you enter values into the parameter field seperated by commas, i.e. 10,11,12
Then it should work. If you still have a problem, try using & instead of + for concatenation.
Regards
Chris McGuigan
"angi" wrote:
> Hi,
> I want to keyin many values in a parameter textbox, and I try the syntax
> like following but it doesn't work, and response the syntax error, why?
> SELECT * FROM Product
> WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value +"))
> Is any mistake in my syntax or how should I modify it?
>
> Thanks!
> Angi
>
>|||Chris,
Thanks to you first.
Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
Dataset SQL dialog or StoreProcedure?
Third, my situation like following..
If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
Excute function be disable, and in Preview, there is an error msg "syntax
error near '=' "
Really have no idea >"<
Thanks again, Chris.
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> Angi,
> I presume your query string is actually
> ="SELECT * FROM Product" +
> "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> Parameters!Month.Value + "))"
> and that you enter values into the parameter field seperated by commas,
i.e. 10,11,12
> Then it should work. If you still have a problem, try using & instead of +
for concatenation.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Hi,
> >
> > I want to keyin many values in a parameter textbox, and I try the syntax
> > like following but it doesn't work, and response the syntax error, why?
> >
> > SELECT * FROM Product
> > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > Parameters!Month.Value +"))
> >
> > Is any mistake in my syntax or how should I modify it?
> >
> >
> > Thanks!
> > Angi
> >
> >
> >|||Chris,
Thank you very much!
I know what's the problem with my syntax, I break(use Enter) the syntax, so
when I preview the Report appears syntax error!
With "int" data type, we can enter data value 1,2,5 directly, and I change
to "char" data type, the value must be write as '1','2','5'
Any convenience way to enter "char" value?
And I'm not VB programmer and the newbie of RS, but if there is any chance
to learn, I must to be learning more.
Thanks for your time and your kind. :)
Regards!
Angi
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> Hi Angi,
> To write dynamic queries like this, you need to be in the 'Generic Query
Designer' in the 'Data' tab of the report. By default you are in the
'Graphical Query Designer'. To switch, press the button to the left of the
'Run' button (!).
> A dynamic query is actually a SQL query built up in a Visual Basic string.
> I'm guessing you're not a VB programmer, if that's the case you don't have
to learn too much. String handling in VB is similar to string handling in
SQL. The big difference is that SQL strings are delimited by a single quote
('), in VB it's double quotes (").
> I hope that helps.
> Regards
> Chris McGuigan
> "angi" wrote:
> > Chris,
> >
> > Thanks to you first.
> > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > Dataset SQL dialog or StoreProcedure?
> > Third, my situation like following..
> > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > Excute function be disable, and in Preview, there is an error msg
"syntax
> > error near '=' "
> >
> > Really have no idea >"<
> >
> > Thanks again, Chris.
> >
> > Angi
> >
> >
> > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
o¡Ps?D
> > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > Angi,
> > > I presume your query string is actually
> > > ="SELECT * FROM Product" +
> > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > Parameters!Month.Value + "))"
> > >
> > > and that you enter values into the parameter field seperated by
commas,
> > i.e. 10,11,12
> > >
> > > Then it should work. If you still have a problem, try using & instead
of +
> > for concatenation.
> > >
> > > Regards
> > > Chris McGuigan
> > > "angi" wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to keyin many values in a parameter textbox, and I try the
syntax
> > > > like following but it doesn't work, and response the syntax error,
why?
> > > >
> > > > SELECT * FROM Product
> > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value +"))
> > > >
> > > > Is any mistake in my syntax or how should I modify it?
> > > >
> > > >
> > > > Thanks!
> > > > Angi
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Angi,
The only way to make the entry of strings easier for the end user is to try and put the quotes in for them, when you build the SQL string.
Say the WHERE part of your SQL looks like this;
"WHERE Item IN (" + Parameters!Item.Value + ")"
Add a single quote before and after the parameter, and replace any commas with a comma surrounded by single quotes like this;
"WHERE Item IN ('" + Replace(Parameters!Item.Value, ",", "','") + "')"
Note where the single quotes are!
This would convert an entry of 1,2,3 into '1','2','3'
Just watch out for spaces around the commas, that may stop the where clause working properly. You can get round this but that will require more code.
Regards
Chris McGuigan
"angi" wrote:
> Chris,
> Thank you very much!
> I know what's the problem with my syntax, I break(use Enter) the syntax, so
> when I preview the Report appears syntax error!
> With "int" data type, we can enter data value 1,2,5 directly, and I change
> to "char" data type, the value must be write as '1','2','5'
> Any convenience way to enter "char" value?
> And I'm not VB programmer and the newbie of RS, but if there is any chance
> to learn, I must to be learning more.
> Thanks for your time and your kind. :)
> Regards!
> Angi
> "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :D5C8406C-7B44-48DB-99E5-ABF8F21ECAC2@.microsoft.com...
> > Hi Angi,
> > To write dynamic queries like this, you need to be in the 'Generic Query
> Designer' in the 'Data' tab of the report. By default you are in the
> 'Graphical Query Designer'. To switch, press the button to the left of the
> 'Run' button (!).
> >
> > A dynamic query is actually a SQL query built up in a Visual Basic string.
> > I'm guessing you're not a VB programmer, if that's the case you don't have
> to learn too much. String handling in VB is similar to string handling in
> SQL. The big difference is that SQL strings are delimited by a single quote
> ('), in VB it's double quotes (").
> >
> > I hope that helps.
> > Regards
> > Chris McGuigan
> >
> > "angi" wrote:
> >
> > > Chris,
> > >
> > > Thanks to you first.
> > > Second, this Syntax doesn't work in DataSet, so this syntax is wrote in
> > > Dataset SQL dialog or StoreProcedure?
> > > Third, my situation like following..
> > > If I type ="Select.... Month.Value + "))" in Dataset Query dialog, the
> > > Excute function be disable, and in Preview, there is an error msg
> "syntax
> > > error near '=' "
> > >
> > > Really have no idea >"<
> > >
> > > Thanks again, Chris.
> > >
> > > Angi
> > >
> > >
> > > "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> ?gco?l¢D
> o¡Ps?D
> > > :FD113147-87AB-4C71-BD35-FF27F771A23B@.microsoft.com...
> > > > Angi,
> > > > I presume your query string is actually
> > > > ="SELECT * FROM Product" +
> > > > "WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > Parameters!Month.Value + "))"
> > > >
> > > > and that you enter values into the parameter field seperated by
> commas,
> > > i.e. 10,11,12
> > > >
> > > > Then it should work. If you still have a problem, try using & instead
> of +
> > > for concatenation.
> > > >
> > > > Regards
> > > > Chris McGuigan
> > > > "angi" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I want to keyin many values in a parameter textbox, and I try the
> syntax
> > > > > like following but it doesn't work, and response the syntax error,
> why?
> > > > >
> > > > > SELECT * FROM Product
> > > > > WHERE (Year = 2003) AND (MonthNumberOfYear IN (" +
> > > > > Parameters!Month.Value +"))
> > > > >
> > > > > Is any mistake in my syntax or how should I modify it?
> > > > >
> > > > >
> > > > > Thanks!
> > > > > Angi
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

Multi Parameter Question Part II

Hi,
I use the following syntax select multi parameter like 1,2,5 and if the
parameter is empty then get all data.
="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
OrderID IN (" + Parameters!ID.Value + ")")
And my problem is..
IF I want to select 3 parameters, ID, Name, Date and use that syntax,
how should I modify the Syntax? or use another way, like procedure? or the
RS can't support my situation?
Thanks!
AngiI would suggest using stored procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I use the following syntax select multi parameter like 1,2,5 and if the
> parameter is empty then get all data.
> ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> OrderID IN (" + Parameters!ID.Value + ")")
> And my problem is..
> IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> how should I modify the Syntax? or use another way, like procedure? or the
> RS can't support my situation?
> Thanks!
> Angi
>|||Stored Procedure can use @. to execute parameter, like
CREATE PROCEDURE sp_ActualVsQuota @.CalendarYear char(4)
But can Stored Procedure execute this parameter function -> (" +
Parameters!CalendarYear.Value + ")?
Cause I want to use multi parameter, and how to define it?
Thanks
Angi
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||About this issue, is any sample could offer?
Thanks!
"Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> I would suggest using stored procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "angi" <angi@.microsoft.com> wrote in message
> news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I use the following syntax select multi parameter like 1,2,5 and if the
> > parameter is empty then get all data.
> > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > OrderID IN (" + Parameters!ID.Value + ")")
> >
> > And my problem is..
> > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > how should I modify the Syntax? or use another way, like procedure? or
the
> > RS can't support my situation?
> >
> > Thanks!
> > Angi
> >
> >
>|||Choose CommandType=StoredProcedure in the data pane of report designer,
choose the stored procedure from the dropdown, and run it. You will be
prompted for parameters.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"angi" <angi@.microsoft.com> wrote in message
news:%23pLpmb6cEHA.1656@.TK2MSFTNGP09.phx.gbl...
> About this issue, is any sample could offer?
> Thanks!
> "Lev Semenets [MSFT]" <levs@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :eqbqWoUcEHA.3824@.TK2MSFTNGP10.phx.gbl...
> > I would suggest using stored procedure.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "angi" <angi@.microsoft.com> wrote in message
> > news:uMNSz2JcEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I use the following syntax select multi parameter like 1,2,5 and if
the
> > > parameter is empty then get all data.
> > > ="SELECT OrderID FROM Orders " & IIF(Parameters!ID.Value="","","WHERE
> > > OrderID IN (" + Parameters!ID.Value + ")")
> > >
> > > And my problem is..
> > > IF I want to select 3 parameters, ID, Name, Date and use that syntax,
> > > how should I modify the Syntax? or use another way, like procedure? or
> the
> > > RS can't support my situation?
> > >
> > > Thanks!
> > > Angi
> > >
> > >
> >
> >
>

Multi Column FTS (2005) Query

Hi
I am trying to get my query syntax to work given a typical query I expect to
passed into my FTS procedure.
The typical search string could be Italy investment. The FTS must search
for Italy filtering by investment.
With an FTS index on CountryName and Item (item is where the word investment
resides)
Some t-sql like this would do the trick:
SELECT CountryName, ItemName
FROM SearchView
WHERE CONTAINS(*, 'italy AND investment')
Given that a single textbox is displayed to allow users to type their search
criteria, how would one seperate such a query so that country names were
searched against the CountryName FTS column and other (non country names)
searched against the FTS Item column?
Should I have several queries to determine if countries exist? If so, then
apply those country names in the AND of the WHERE clause, then how would I
then parse to remove the country names from the intial query string so that
the remaining words can be searched against the FTS Item column?
To add to the delimea, I'm using the FORMSOF(THESAURUS...) functionality, so
a user can query investment Europe (getting countries in the thesaurus file
matching Europe)
Craig
Unfortunately what you would have to do is the following
select *From SearchView where contains(CountryName, 'Italy') and
contains(ItenName,'investment')
This would offer the best performance, you could also do the following
select * from SearchView where CountryName='Italy' and
contains(ItenName,'investment')
unfortunately you will have to parse the search string to see which
countries are present in it, and then build your query accordingly.
Hilary

> To add to the delimea, I'm using the FORMSOF(THESAURUS...)
functionality, so
> a user can query investment Europe (getting countries in the
thesaurus file
> matching Europe)
> Craig
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

mssql: insert into syntax

Hello
Can anyone help me translate this from access so that it can work in mssql
(i need to get next value, but cannot use identity as if row is deleted,
another must get new next column number which would be same as deleted one)
Access;
INSERT INTO table
SELECT
(IIF(code<>Null,MAX(code)+1,1) AS code,
0 AS usercode
FROM table

I tried this in mssql but will not work:
INSERT INTO table
SELECT
CASE
WHEN code IS NULL THEN 1
ELSE MAX(code)+1
END
AS code,
0 AS usercode
FROM tableHi

You may be better of using an identity column. It is not guaranteed to be
contiguous but usually is the ordinal value that is required. This is
similar to the autoincrementing number in access. See the topic "Identity
(Property)" in books online for more information.

John

"Andre" <spam@.spam.com> wrote in message news:de9g0c$ih4$1@.ss405.t-com.hr...
> Hello
> Can anyone help me translate this from access so that it can work in mssql
> (i need to get next value, but cannot use identity as if row is deleted,
> another must get new next column number which would be same as deleted
> one)
> Access;
> INSERT INTO table
> SELECT
> (IIF(code<>Null,MAX(code)+1,1) AS code,
> 0 AS usercode
> FROM table
> I tried this in mssql but will not work:
> INSERT INTO table
> SELECT
> CASE
> WHEN code IS NULL THEN 1
> ELSE MAX(code)+1
> END
> AS code,
> 0 AS usercode
> FROM table|||On Sun, 21 Aug 2005 11:02:42 +0200, Andre wrote:

(snip)
>Access;
>INSERT INTO table
>SELECT
> (IIF(code<>Null,MAX(code)+1,1) AS code,
>0 AS usercode
>FROM table

Hi Andre,

As John says: Consider using IDENTITY (the SQL Server equivalent of what
Access calls "autonumber").

If there are reason's why you can't use IDENTITY, then use

SELECT COALESCE(MAX(code), 0) + 1 AS code
FROM table

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||will try this
thx

(by the way, i mentioned I cannot use identity as it would not preserve
correct order if a middle row is deleted
and it would not allow end-user to change it)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:6fqgg1d3f7bpim4ct0bril93j6vkedhgek@.4ax.com...
> On Sun, 21 Aug 2005 11:02:42 +0200, Andre wrote:

> If there are reason's why you can't use IDENTITY, then use
> SELECT COALESCE(MAX(code), 0) + 1 AS code
> FROM table|||On Sun, 21 Aug 2005 14:19:39 +0200, Andre wrote:

>will try this
>thx
>(by the way, i mentioned I cannot use identity as it would not preserve
>correct order if a middle row is deleted
>and it would not allow end-user to change it)

Hi Andre,

That's a logical result of the "raison d'etre" of the IDENTITY
attribute. You should use IDENTITY only to generate a unique numeric
value that can be used in place of the "real" key in foreign key
relationships. For instance, if a Foo is identified by the combination
of FooName, FooDate and FooWeight, the tables Foo and Bar *could* look
like this:

CREATE TABLE Foo
(FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (FooName, FooDate, FooWeight)
)
CREATE TABLE Bar
(BarNo int NOT NULL,
FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (BarNo),
FOREIGN KEY (FooName, FooDate, FooWeight)
REFERENCES Foo (FooName, FooDate, FooWeight)
ON UPDATE CASCADE
ON DELETE NO ACTION
)

Or, you could use IDENTITY to create a surrogate key and have your
tables like this:

CREATE TABLE Foo
(FooID int NOT NULL IDENTITY,
FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (FooID),
UNIQUE (FooName, FooDate, FooWeight)
)
CREATE TABLE Bar
(BarNo int NOT NULL,
FooID int NOT NULL IDENTITY,
-- other columns,
PRIMARY KEY (BarNo),
FOREIGN KEY (FooID) REFERENCES Foo (FooID)
ON DELETE NO ACTION
)

This gives Bar a smaller footprint, and will speed up te joins (but at
the expense of a higher number of required joins). Note that a Foo is
still identified by it's "real" key. Also note that you might just as
well keep the "real" key as PRIMARY KEY and declare the identity column
to be UNIQUE (that will affect how your indexes look, so this is a
choice that affects performance).

An important issue to keep in mind is that the end user never sees the
identity value in this case. The end user will only see the "real" key,
as determined when investigating the business' information needs.

Your mention of preserving order when rows are deleted makes me think
that you want to use IDENTITY to get a ranking. In that case: don't. The
only thing MS guarantees about IDENITY is that it will be a unique value
in it's table (proivided you never override the generated values or
reset the seed). If you need a rank, you can either:
a) Compute it whenever you query the data. Use a view if you don't want
to retype the same query logic over and over again, or
b) Compute and store it; recompute ranks after each modification; this
one is dangerous (one uncontrolled modification can ruin the scheme) and
can slow down modification operations - only use it if you query the
data (including the rank) much more often than you modify the data.

Your mention of end users changing the value makes me think that you
don't want a ranking after all - but if have no idea what you do want to
use it for. Can you explain the purpose of this? I'm asking partly out
of curiosity, partly because I have the feeling that you're about to
make an error that either you or your successor will regret - I might be
wrong (I hope so!), but if I'm not, you better change your plan now,
before it is too late!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have an accounting program which I am moving to mssql.
Now, I have tables which contain various documents (bills, inputs/outputs
etc).
Each document in its group must be in order
1,2,3...
there can be no omissions.
Now, I must permit to some users to delete documents (if these have been
entered by mistake), but also to permit
them to change their numbers. But these numbers must be unique in their
respective tables.
And, when user is creating new document, program must give him next number
(serial number if you wish).

So, since user might delete a middle row, using identity would mean that he
could, later when he creates document, give it the
number he previously deleted. I could set identity to allow change, but I
don't want to.

My programs currently run on access and mysql. I am adding mssql but didn't
expect so much trouble with sql syntax
Coalesce was mentioned in previous post: it does not work
I need simple

INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM
table
or
INSERT INTO table SELECT MAX(fieldvalue)+1 AS fieldvalueFROM table

if this is not possible on mssql, I will have to create on insert trigger or
lock table while creating new entry and first get value, then insert it into
table (1 query, 1 insert - lock, since two users might at the same time
create new: while information fieldvalue+1 travels to first user, second
executes same query and gets same
number as first has not made insert yet)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Your mention of preserving order when rows are deleted makes me think
> that you want to use IDENTITY to get a ranking. In that case: don't. The
> only thing MS guarantees about IDENITY is that it will be a unique value

Eh, Andr says he does not want to use IDENTITY, so you tell him not to
use it?

Anyway, if you say:

INSERT tbl (...)
SELECT ...
ORDER BY ...

and tbl has an IDENTITY column, the message I have, is indeed that there
is a guarantee that the IDENTITY values will reflect the ORDER BY clause.

However, this does not apply to SELECT INTO.

In any case, it is obvious from Andre's description of his business problem
that he should stay away from IDENTITY.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Andre (spam@.spam.com) writes:
> Coalesce was mentioned in previous post: it does not work

Please defined "does not work". Do you get an error message, do you
get unexpected result, does heaven fall down on your or what?

In any case, this seem to work:

CREATE TABLE andre (id int NOT NULL PRIMARY KEY,
somedata varchar(230) NOT NULL)
go
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is some data'
FROM andre
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is some other data'
FROM andre
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is any data'
FROM andre
go
SELECT * FROM andre ORDER BY id
go
DROP TABLE andre

> I need simple
> INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM

Since isnull() is proprietary to SQL Server, while coalesce() is
ANSI-SQL and you support other DBMS's, coalesce() would be a better
choice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

You can try it as

INSERT INTO table
SELECT ISNULL(MAX(code),0)+1, 0 FROM table

Please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||Hi Andre

>From your description it sounds like you have to re-order everything if
you remove an intermediate document. This will not lead to a very
scalable application.

John|||Thank you all for help.

Problem was somewhere else:

I tried
INSERT INTO table x AS fieldx, y AS fieldy FROM table
while correct (for MSSQL obviously) is:
INSERT table (fieldx,fieldy) SELECT x,y FROM table

again, thank you for your time

p.s.: I wonder why are there such differences between sql syntax for various
databases (as in: what is the point of standard which is ignored)|||Andre (spam@.spam.com) writes:
> Thank you all for help.
> Problem was somewhere else:
> I tried
> INSERT INTO table x AS fieldx, y AS fieldy FROM table
> while correct (for MSSQL obviously) is:
> INSERT table (fieldx,fieldy) SELECT x,y FROM table

As far as I know the latter is also compliant with ANSI standards.
(Save for the fact that ANSI mandates INTO, while this is optional in
MS SQL Server.) The first syntax is something I've never seen before.
Does it work anywhere?

> p.s.: I wonder why are there such differences between sql syntax for
> various databases (as in: what is the point of standard which is
> ignored)

Indeed, just because it is the standard, does not mean that it is
implemnented everywhere. However, the basics of a regular INSERT
statement is something I would expect to work everywhere.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 21 Aug 2005 23:34:40 +0200, Andre wrote:

>I have an accounting program which I am moving to mssql.
>Now, I have tables which contain various documents (bills, inputs/outputs
>etc).
>Each document in its group must be in order
>1,2,3...
>there can be no omissions.
>Now, I must permit to some users to delete documents (if these have been
>entered by mistake), but also to permit
>them to change their numbers. But these numbers must be unique in their
>respective tables.
>And, when user is creating new document, program must give him next number
>(serial number if you wish).
(snip)

Hi Andre,

There are some conflicting requirements. If there may be no omissions,
than you must either not allow users to delete a document (or rather:
don't allow them to delete A ROW - if the corresponding document is
deleted, keep the row but set a column to indicate that the document is
deleted) - or you must renumber all documents each time a document is
deleted to make sure that there never are gaps. Of course, if these
numbers are visible to the user and used to identify the documents, then
renumbering them will wreak havoc to the ability to relate rows in the
database to the actual documents. And if the users don't see the
numbers, then why bother with trying to keep them without omissions?

Also, if you allow users to change the document number, you are actually
guaranteed to get omissions. Sooner or later, someone will type the
number 42 because he's a Douglas Adams fan.

If the actual requirement is to use a number that is PREFERABLY
increasing and without gaps, and that users can optionally change to
reuse the number of a deleted document, I'd say: do a SELECT MAX(..)
query in the front end; increase by one and prepopulate the number field
in your frontend with that number. The user can either accept this
default or type a different number. The number that is in the field when
the user submits his data entry is sent to the database in an INSERT ...
VALUES statement.

(snip)
>I could set identity to allow change, but I
>don't want to.

So instead, you try to create your own solution that behaves exactly as
IDENTITY after setting it to allow change, but less scalable?

(snip)
>Coalesce was mentioned in previous post: it does not work
>I need simple
>INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM
>table

This should work. If you replace ISNULL with COALESCE, it should still
work. If it doesn't then please provide more information.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually, it is only important to fetch new table entry as CODE+1
Gaps are OK if user makes them. Skipping order number of CODE by user is
also OK.
But program must always return CODE+1 when new row is added.
User may change the number to any he wishes (except existing one) and the
next new row will be +1
This, of course, is not my idea. I would forbid deleting documents (even law
requires them to be void, not deleted - we are talking about accounting
program)
but then nobody would purchase my program.

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ipfkg118so3erd23b2sqa65kcsbosi585f@.4ax.com...
> On Sun, 21 Aug 2005 23:34:40 +0200, Andre wrote:
> There are some conflicting requirements. If there may be no omissions,
> than you must either not allow users to delete a document (or rather:
> don't allow them to delete A ROW - if the corresponding document is
> deleted, keep the row but set a column to indicate that the document is
> deleted) - or you must renumber all documents each time a document is
> deleted to make sure that there never are gaps. Of course, if these
> numbers are visible to the user and used to identify the documents, then
> renumbering them will wreak havoc to the ability to relate rows in the
> database to the actual documents. And if the users don't see the
> numbers, then why bother with trying to keep them without omissions?
> Also, if you allow users to change the document number, you are actually
> guaranteed to get omissions. Sooner or later, someone will type the
> number 42 because he's a Douglas Adams fan.
> If the actual requirement is to use a number that is PREFERABLY
> increasing and without gaps, and that users can optionally change to
> reuse the number of a deleted document, I'd say: do a SELECT MAX(..)
> query in the front end; increase by one and prepopulate the number field
> in your frontend with that number. The user can either accept this
> default or type a different number. The number that is in the field when
> the user submits his data entry is sent to the database in an INSERT ...
> VALUES statement.|||On Tue, 23 Aug 2005 13:28:57 +0200, Andre wrote:

>Actually, it is only important to fetch new table entry as CODE+1
>Gaps are OK if user makes them. Skipping order number of CODE by user is
>also OK.
>But program must always return CODE+1 when new row is added.
>User may change the number to any he wishes (except existing one) and the
>next new row will be +1
>This, of course, is not my idea. I would forbid deleting documents (even law
>requires them to be void, not deleted - we are talking about accounting
>program)
>but then nobody would purchase my program.

Hi Andre,

I stick with my previous recommendation.

1. Fetch MAX(code)+1 with a non-locking query when opening the screen.
Either display it as default value in the code field, or keep the code
field blank.

2. When details are entered, attempt to insert the row, with code as
entered on the screen; if no value is entered, use the MAX(code)+1 from
the previous call.

3. If a row with the chosen key value exists, further action is decided
by the front end:

3a. If key was entered by user: error message.
3b. If user didn't override the default, re-insert row with MAX(code)+1
as new code; show warning message that code has been changed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

MSSQL Select Syntax Help

I have a table that has unit id, date, time, etc. I would like to select each unit id with the last date it has in the table. The result should have each unit listed once with the latest date in the table.

For Example:

unit id Date Time
00100 01/12/2007 8:00
00100 01/12/2007 8:45
00200 01/12/2007 8:50
00100 01/13/2007 13:30
00300 01/13/2007 13:45
00100 01/14/2007 11:00
00200 01/14/2007 11:30

the result should be:

00100 01/14/2007 11:00
00200 01/14/2007 11:30
00300 01/13/2007 13:45

Try this (Let's call your table "T")

SELECT [unit id],Max([Date Time])

FROM T

GROUP BY [unit id]

|||

Thanks for the response.

That is very close to what I need. But, if I had additional fields that I needed such as address and speed how would I include those in the select statement. I really couldn't use max because that would give me the highest speed. I need the address and speed that goes with the MAX(DATE time)

Sorry I didn't include that bit of information first.

|||

In that case, one option would be to take advantage of derived table concept as follows (Note:[Date Time] column is assumed to have unique values)...

SELECT T.* FROM T INNER JOIN (SELECT [unit id],[Date Time]=Max([Date Time]) FROM T GROUP BY [unit id]) as A ON T.[Date Time]=A.[Date Time]

|||

Thanks giddyup.

Your suggestion helped out a lot.

This is what I got to work.

SELECT * FROM log as t1
JOIN (SELECT [vehicle number],Max(Date+' '+time) as maxdate FROM log GROUP BY [vehicle number]) AS t2
ON t1.[vehicle number] = t2.[vehicle number] and t2.maxdate = t1.date+' '+t1.time
order by t1.[vehicle number]