passing parameter from stored proc to crystal

Im using crystal 11.5.

Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.

Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.

In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.

What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.

How do I get crystal to prompt me when I add the stored proc to the report?

Passing Out Parameter to Sybase Stored Proc from RS

I am defining the Output parameter from Sybase Stored Proc.
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql

passing multiple values to a stored procedure

hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:

> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name lik
e
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
>
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values[vbcol=seagreen]
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)[/vbcol
]
thanks
red
"Simon Worth" wrote:
[vbcol=seagreen]
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> list
> selected)
> like
> values
>
>|||Did you see my post? WHY ARE YOU YELLING?
http://www.aspfaq.com/
(Reverse address to reply.)

> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
>
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many[vbcol=seagreen]
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:

> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> THERE
>
>

passing multiple values to a stored procedure

hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
?
thanks
red
Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:

> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> ?
> thanks
> red
|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list[vbcol=seagreen]
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
selected)[vbcol=seagreen]
like[vbcol=seagreen]
values
>
|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:

> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> list
> selected)
> like
> values
>
>
|||Did you see my post? WHY ARE YOU YELLING?
http://www.aspfaq.com/
(Reverse address to reply.)

> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE[vbcol=seagreen]
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
@.name3,........@.nameN)[vbcol=seagreen]
> thanks
> red
> "Simon Worth" wrote:
you[vbcol=seagreen]
you[vbcol=seagreen]
comma-separated[vbcol=seagreen]
name[vbcol=seagreen]
|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,[vbcol=seagreen]
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> THERE
> @.name3,........@.nameN)
in[vbcol=seagreen]
> you
if[vbcol=seagreen]
> you
> comma-separated
> name
many
>
|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:

> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> THERE
>
>

passing multiple values to a stored procedure

hi
my query in the stored proc is like ::
select * from tableA where name like @.name
where @. name is varchar type
but the input i get from a list box(where multiple values can be selected)
i think i have to write the query like
selct * from tableA where name like @.name1 or name like @.name2 or name like
@.name3...
but the problem is that i have no idea before hand to tell how many values
will be sent as params...
can some one help me
'
thanks
redDo you really mean LIKE @.name? This is the same as = @.name. Maybe you
meant LIKE '%'+@.name+'%'
Anyway, if you are using = it will be much more efficient than LIKE if you
have an index on name. And you can pass in a (limited) comma-separated list
and translate it to a table, and join on that table. See
http://www.aspfaq.com/2248 for an example.
A
On 3/3/05 8:25 PM, in article
18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
<red60man@.discussions.microsoft.com> wrote:
> hi
> my query in the stored proc is like ::
> select * from tableA where name like @.name
> where @. name is varchar type
> but the input i get from a list box(where multiple values can be selected)
> i think i have to write the query like
> selct * from tableA where name like @.name1 or name like @.name2 or name like
> @.name3...
> but the problem is that i have no idea before hand to tell how many values
> will be sent as params...
> can some one help me
> '
> thanks
> red|||You could try passing the selected text fields back as comma seporated
values, and then you can do an IN statement
something like
selct * from tableA where name IN (@.name1, @.name2, @.name3)
That assumes that you really didn't want to use the like statement as in
your example you didn't provide any type of wildcard character
--
Simon Worth
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> meant LIKE '%'+@.name+'%'
> Anyway, if you are using = it will be much more efficient than LIKE if you
> have an index on name. And you can pass in a (limited) comma-separated
list
> and translate it to a table, and join on that table. See
> http://www.aspfaq.com/2248 for an example.
> A
>
> On 3/3/05 8:25 PM, in article
> 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> <red60man@.discussions.microsoft.com> wrote:
> > hi
> >
> > my query in the stored proc is like ::
> >
> > select * from tableA where name like @.name
> > where @. name is varchar type
> >
> > but the input i get from a list box(where multiple values can be
selected)
> >
> > i think i have to write the query like
> > selct * from tableA where name like @.name1 or name like @.name2 or name
like
> > @.name3...
> >
> > but the problem is that i have no idea before hand to tell how many
values
> > will be sent as params...
> >
> > can some one help me
> > '
> >
> > thanks
> > red
>|||HI,
BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS THERE
SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> selct * from tableA where name IN (@.name1, @.name2, @.name3,........@.nameN)
thanks
red
"Simon Worth" wrote:
> You could try passing the selected text fields back as comma seporated
> values, and then you can do an IN statement
> something like
> selct * from tableA where name IN (@.name1, @.name2, @.name3)
> That assumes that you really didn't want to use the like statement as in
> your example you didn't provide any type of wildcard character
> --
> Simon Worth
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > Do you really mean LIKE @.name? This is the same as = @.name. Maybe you
> > meant LIKE '%'+@.name+'%'
> >
> > Anyway, if you are using = it will be much more efficient than LIKE if you
> > have an index on name. And you can pass in a (limited) comma-separated
> list
> > and translate it to a table, and join on that table. See
> > http://www.aspfaq.com/2248 for an example.
> >
> > A
> >
> >
> > On 3/3/05 8:25 PM, in article
> > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > <red60man@.discussions.microsoft.com> wrote:
> >
> > > hi
> > >
> > > my query in the stored proc is like ::
> > >
> > > select * from tableA where name like @.name
> > > where @. name is varchar type
> > >
> > > but the input i get from a list box(where multiple values can be
> selected)
> > >
> > > i think i have to write the query like
> > > selct * from tableA where name like @.name1 or name like @.name2 or name
> like
> > > @.name3...
> > >
> > > but the problem is that i have no idea before hand to tell how many
> values
> > > will be sent as params...
> > >
> > > can some one help me
> > > '
> > >
> > > thanks
> > > red
> >
>
>|||Did you see my post? WHY ARE YOU YELLING?
--
http://www.aspfaq.com/
(Reverse address to reply.)
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED|||In your application format the selected text fields to be returned to the
stored procedure as comma seporated values.
So if the user selects John Doe, Jane Smith, and Fred Flintstone
format the selections to come back as 1 paramater
Var = Selection1 & ", " & Selection2 & ", " & Selection3
Value of Var = "John Doe, Jane Smith, Fred Flintstone"
In the stored procedure, the select statement would look somewhat like this,
but enhanced
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = '''John Doe'''
set @.var1 = @.var1 + ', ''Snow White'''
set @.var1 = @.var1 + ', ''Fred Flintstone'''
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> HI,
> BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
THERE
> SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > selct * from tableA where name IN (@.name1, @.name2,
@.name3,........@.nameN)
> thanks
> red
> "Simon Worth" wrote:
> > You could try passing the selected text fields back as comma seporated
> > values, and then you can do an IN statement
> > something like
> > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> >
> > That assumes that you really didn't want to use the like statement as in
> > your example you didn't provide any type of wildcard character
> >
> > --
> > Simon Worth
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
you
> > > meant LIKE '%'+@.name+'%'
> > >
> > > Anyway, if you are using = it will be much more efficient than LIKE if
you
> > > have an index on name. And you can pass in a (limited)
comma-separated
> > list
> > > and translate it to a table, and join on that table. See
> > > http://www.aspfaq.com/2248 for an example.
> > >
> > > A
> > >
> > >
> > > On 3/3/05 8:25 PM, in article
> > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > <red60man@.discussions.microsoft.com> wrote:
> > >
> > > > hi
> > > >
> > > > my query in the stored proc is like ::
> > > >
> > > > select * from tableA where name like @.name
> > > > where @. name is varchar type
> > > >
> > > > but the input i get from a list box(where multiple values can be
> > selected)
> > > >
> > > > i think i have to write the query like
> > > > selct * from tableA where name like @.name1 or name like @.name2 or
name
> > like
> > > > @.name3...
> > > >
> > > > but the problem is that i have no idea before hand to tell how many
> > values
> > > > will be sent as params...
> > > >
> > > > can some one help me
> > > > '
> > > >
> > > > thanks
> > > > red
> > >
> >
> >
> >|||Oh sorry, I messed that example up.. My appologies
declare @.SQL nvarchar(200)
declare @.var1 as varchar(100)
set @.var1 = @.ParameterPassed
set @.SQL = 'select * from TableName where CustName in ('
set @.SQL = @.SQL + @.Var1 + ')'
EXECUTE sp_executesql @.SQL
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:u3SV14OIFHA.3072@.tk2msftngp13.phx.gbl...
> In your application format the selected text fields to be returned to the
> stored procedure as comma seporated values.
> So if the user selects John Doe, Jane Smith, and Fred Flintstone
> format the selections to come back as 1 paramater
> Var = Selection1 & ", " & Selection2 & ", " & Selection3
> Value of Var = "John Doe, Jane Smith, Fred Flintstone"
>
> In the stored procedure, the select statement would look somewhat like
this,
> but enhanced
> declare @.SQL nvarchar(200)
> declare @.var1 as varchar(100)
> set @.var1 = '''John Doe'''
> set @.var1 = @.var1 + ', ''Snow White'''
> set @.var1 = @.var1 + ', ''Fred Flintstone'''
> set @.SQL = 'select * from TableName where CustName in ('
> set @.SQL = @.SQL + @.Var1 + ')'
> EXECUTE sp_executesql @.SQL
>
> --
> Simon Worth
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:88D7BA6D-9830-4813-BFDD-C308DDBD39DC@.microsoft.com...
> > HI,
> >
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
> > > selct * from tableA where name IN (@.name1, @.name2,
> @.name3,........@.nameN)
> >
> > thanks
> > red
> >
> > "Simon Worth" wrote:
> >
> > > You could try passing the selected text fields back as comma seporated
> > > values, and then you can do an IN statement
> > > something like
> > > selct * from tableA where name IN (@.name1, @.name2, @.name3)
> > >
> > > That assumes that you really didn't want to use the like statement as
in
> > > your example you didn't provide any type of wildcard character
> > >
> > > --
> > > Simon Worth
> > >
> > >
> > > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > > news:BE4D38BE.1F30%ten.xoc@.dnartreb.noraa...
> > > > Do you really mean LIKE @.name? This is the same as = @.name. Maybe
> you
> > > > meant LIKE '%'+@.name+'%'
> > > >
> > > > Anyway, if you are using = it will be much more efficient than LIKE
if
> you
> > > > have an index on name. And you can pass in a (limited)
> comma-separated
> > > list
> > > > and translate it to a table, and join on that table. See
> > > > http://www.aspfaq.com/2248 for an example.
> > > >
> > > > A
> > > >
> > > >
> > > > On 3/3/05 8:25 PM, in article
> > > > 18FCBB9A-D1D5-4661-A94A-F6949AD175F6@.microsoft.com, "red60man"
> > > > <red60man@.discussions.microsoft.com> wrote:
> > > >
> > > > > hi
> > > > >
> > > > > my query in the stored proc is like ::
> > > > >
> > > > > select * from tableA where name like @.name
> > > > > where @. name is varchar type
> > > > >
> > > > > but the input i get from a list box(where multiple values can be
> > > selected)
> > > > >
> > > > > i think i have to write the query like
> > > > > selct * from tableA where name like @.name1 or name like @.name2 or
> name
> > > like
> > > > > @.name3...
> > > > >
> > > > > but the problem is that i have no idea before hand to tell how
many
> > > values
> > > > > will be sent as params...
> > > > >
> > > > > can some one help me
> > > > > '
> > > > >
> > > > > thanks
> > > > > red
> > > >
> > >
> > >
> > >
>|||i am sorry, i was not yelling .....my caps was on and i didnt notice it
"Aaron [SQL Server MVP]" wrote:
> Did you see my post? WHY ARE YOU YELLING?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
> > BUT I AM NOT SURE HOW MANY INPUT PARAMATERS ARE GOING TO BE PASSED IS
> THERE
> > SOMETHING LIKE AN ARRAY TYPE PARAMETER WHICH CAN BE USED
>
>

Passing multiple values in 1 varchar variable in a stored proc IN

I'm trying to pass a parameter to a stored procedure with the following quer
y:
sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
The stored procedure is as follows:
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (@.Field2)
Even though @.Field2 should be passed in as multiple string values seperated
by a comma (thus creating a valid "IN" statement) it isn't functioning that
way. If I set the value of @.Field2 = 'ABC' (a single value) the statement
works as desired.ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes:
> I'm trying to pass a parameter to a stored procedure with the following
> query:
> sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
Before we go the actual question, permit me to point out two things. Or
three.
1) Don't name your stored procedures sp_something, the sp_ prefix is
reserved for system stored procedures, and SQL Server first looks
in master for procedures with names like this.
2) In SQL Server you use ' to quote strings. If the setting
QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
This is very handy with nested strings, but alas, there is
functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
so best practice is to use ' only. Note that this setting is ON
by default in many contexts.)
3) And since you appear to get away with this, I suspect that you use
Enterprise Manager to edit your stored procedures. (EM has this
setting off by default for some inexplicable reason.) EM is a very
poor tool to edit stored procedures. You are better off using
Query Analyzer.

> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN (@.Field2)
> Even though @.Field2 should be passed in as multiple string values
> seperated by a comma (thus creating a valid "IN" statement) it isn't
> functioning that way. If I set the value of @.Field2 = 'ABC' (a single
> value) the statement works as desired.
I am afraid that the answers from Mark Williams led you astray. And
the posting from Celko equally less helpful, as he uses syntax that
does not work on SQL Server. (But he claims it to be portable!)
Anyway, the way to do this, is to use a function that unpacks the
list into a table. I have a loooong article on this on my web site,
but this link brings you directly to a solution:
http://www.sommarskog.se/arrays-in-...ist-of-strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing multiple values in 1 varchar variable in a stored proc IN

This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w of any SQL language class. SQL is not your original
procedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.

> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing multiple values in 1 varchar variable in a stored proc

Although my earlier posts weren't "perfect," I tested my later posting prett
y
thoroughly in a sample db, and it produces the desired results. In addition,
the code shown is almost exactly the same code as shown at
http://www.aspfaq.com/show.asp?id=2248
which was reference by Aaron Bertrand in a thread very similar to this one.
http://www.microsoft.com/technet/co...94-b4fd1783d45e
So please explain to me how I am leading him "astray."
"Erland Sommarskog" wrote:

> ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes
:
> Before we go the actual question, permit me to point out two things. Or
> three.
> 1) Don't name your stored procedures sp_something, the sp_ prefix is
> reserved for system stored procedures, and SQL Server first looks
> in master for procedures with names like this.
> 2) In SQL Server you use ' to quote strings. If the setting
> QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
> This is very handy with nested strings, but alas, there is
> functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
> so best practice is to use ' only. Note that this setting is ON
> by default in many contexts.)
> 3) And since you appear to get away with this, I suspect that you use
> Enterprise Manager to edit your stored procedures. (EM has this
> setting off by default for some inexplicable reason.) EM is a very
> poor tool to edit stored procedures. You are better off using
> Query Analyzer.
>
> I am afraid that the answers from Mark Williams led you astray. And
> the posting from Celko equally less helpful, as he uses syntax that
> does not work on SQL Server. (But he claims it to be portable!)
> Anyway, the way to do this, is to use a function that unpacks the
> list into a table. I have a loooong article on this on my web site,
> but this link brings you directly to a solution:
> http://www.sommarskog.se/arrays-in-...ist-of-strings.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>Thank you Mark Williams for you thorough answers. Your final solution worke
d
perfectly! If you're ever in Rochester, look me up. I owe you a drink.
For the rest of you who were helpful, thank you as well. I will take all of
your advice to heart. I am relatively new to stored procedures on MS SQL
Server, and thank you for your advice.
For our "newbie"-slinging friend, --CELKO--; I hope you can more helpful in
the future. You seem to know what you're talking about, but it gets lost in
the white noise of your apparent hostility.
To all, Happy New Year.
"Mark Williams" wrote:
> Although my earlier posts weren't "perfect," I tested my later posting pre
tty
> thoroughly in a sample db, and it produces the desired results. In additio
n,
> the code shown is almost exactly the same code as shown at
> http://www.aspfaq.com/show.asp?id=2248
> which was reference by Aaron Bertrand in a thread very similar to this one
.
> http://www.microsoft.com/technet/co...94-b4fd1783d45e
> So please explain to me how I am leading him "astray."
> "Erland Sommarskog" wrote:
>sql

Passing multiple values in 1 varchar variable in a stored proc

So in my case, how do I differentiate between the string intended for @.Field
1
from the ones intended for @.Field2?
sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
or do I do something like this:
sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
encased in a double qoute to identify it as one string to be passed over.
THANK YOU! :)
"Mark Williams" wrote:
> You'll need double single-quotes (that's really clear!) to get what you wa
nt
> For example
> DECLARE @.foo varchar(50)
> SET @.foo = '''ABC'', ''DEF'', ''GHI''' --those are all single quotes!
> SELECT @.foo
> returns
> 'ABC', 'DEF', 'GHI'
>
> "ckeaton@.inergex.com" wrote:
>I'm not sure why it's not working when I try to pass the value as it stands
in your most recent example.
It's not finding the individual values which leads me to believe that
something about how the construct is being presented to the database is wron
g.
It should end out looking like this...
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN ('ABC', 'DEF', 'GHI');
But it appears not to be doing that.
Thank you for your suggestions.
Frustrating, because this seems like such a simple thing, but it's tripping
me up! :)
"Mark Williams" wrote:
> You would want something like the second example, but with all single quot
es
> again:
> sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
> "ckeaton@.inergex.com" wrote:
>|||> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is
> wrong.
Maybe you could show us your code? (I, for one, only see three messages so
far in this thread. It appears to have been orphaned in some way.)|||It looks like the statement is evaluating the variable as a single string.
For example, try the following in the Northwind sample database
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1, @.var2)', N'@.var1 varchar(50), @.var2 varchar(50)
',
@.var1='Konbu',@.var2='Tofu'
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1)', N'@.var1 varchar(50)',
@.var1='''Konbu'',''Tofu'''
The first one works, but the second doesn't.
If you wanted to pass your list of coupons as just comma-seperated, you
could do something like this
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
DECLARE @.coupons TABLE (coupon char(3))
DECLARE @.coupon char(3)
WHILE CHARINDEX(',',@.Field2)<>0
BEGIN
INSERT INTO @.coupons (coupon) VALUES (LEFT(@.Field2, CHARINDEX(',',@.Field2)
- 1))
SET @.Field2=SUBSTRING(@.Field2, CHARINDEX(',', @.Field2) + 1,LEN(@.Field2))
END
INSERT INTO @.coupons (coupon) VALUES (@.Field2)
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (SELECT coupon FROM @.coupons)
--alternatively AND EXISTS (SELECT coupon FROM @.coupons WHERE
coupon=P.coupon)
The procedure would be called like
sp_Monkey '24601','ABC,DEF,GHI'
The procedure splits the comma-seperated Field2 and inserts each value into
the table variable @.coupons, which is reference later.
"ckeaton@.inergex.com" wrote:

> I'm not sure why it's not working when I try to pass the value as it stand
s
> in your most recent example.
> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is wr
ong.
> It should end out looking like this...
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN ('ABC', 'DEF', 'GHI');
> But it appears not to be doing that.
> Thank you for your suggestions.
> Frustrating, because this seems like such a simple thing, but it's trippin
g
> me up! :)
>|||You would want something like the second example, but with all single quotes
again:
sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
"ckeaton@.inergex.com" wrote:
> So in my case, how do I differentiate between the string intended for @.Fie
ld1
> from the ones intended for @.Field2?
> sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
> or do I do something like this:
> sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
> encased in a double qoute to identify it as one string to be passed over.
> THANK YOU! :)
> "Mark Williams" wrote:
>

Passing multiple selections to a stored proc parameter

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@.OrderList varchar(500)
)
RETURNS
@.ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @.OrderID varchar(10), @.Pos int

SET @.OrderList = LTRIM(RTRIM(@.OrderList))+ ','
SET @.Pos = CHARINDEX(',', @.OrderList, 1)

IF REPLACE(@.OrderList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.OrderID = LTRIM(RTRIM(LEFT(@.OrderList, @.Pos - 1)))
IF @.OrderID <> ''
BEGIN
INSERT INTO @.ParsedList (OrderID)
VALUES (CAST(@.OrderID AS int)) --Use Appropriate conversion
END
SET @.OrderList = RIGHT(@.OrderList, LEN(@.OrderList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.OrderList, 1)

END
END
RETURN
END
GO

I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_criI see you parsing the list, but I don't see where you select any records based upon it. So where would your ALL logic be incorporated?sql

passing in a value to use as a column 'as name' in a stored proc

Hi,
I want to hand into a store procude the column name to use in the returned
result set...
create proc sample
@.colName as nvarcher(20)
as
select col1 as @.colname, col2 from table1..
But this produces an error... saying incorrect syntax near @.colname
is there a way to do want i am trying to do here?
ThanksThe curse and blessings of dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Martin C K Poon
Senior Analyst Programmer
====================================
"Aussie Rules" <AussieRules@.nospam.nospam> bl
news:uvQ1pgOjGHA.3572@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I want to hand into a store procude the column name to use in the returned
> result set...
> create proc sample
> @.colName as nvarcher(20)
> as
> select col1 as @.colname, col2 from table1..
> But this produces an error... saying incorrect syntax near @.colname
> is there a way to do want i am trying to do here?
> Thanks
>
>
>|||Thanks for Martin's informative inputs.
Hi Aussie,
I agree with Martin that you would need to consider using the dynamic SQL
execution. And in SQL Server the "exec" or "execute" keyword to execute
dynamic generated T-SQL statements:
#EXECUTE
http://msdn.microsoft.com/library/e...asp?frame=true
BTW, dynamic sql will have additional performance overhead comparing to
static T-SQL execution. Also, when we use string concatenate to generate
dynamic dynamic T-SQL statement, we would also take care of SQL injection
issue:
#SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this also helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Aussie,
Have you got any progress or new ideas on this issue or does our replies
help you some? If there is still anything we can help, please feel free to
post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Passing in a Parameter

Hi All,
I have a Stored Proc and would like to pass in a VARCHAR parameter. The
problem is that the query will need to use it in a IN clause...
DECLARE @.PARAM AS VARCHAR(20)
SET @.PARAM = 'Dan', 'Mike', 'Lisa'
SELECT *
FROM Orders
WHERE Name IN (@.PARAM)
is there a away around this?
Thanks,
Kunkel
On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:

>Hi All,
>I have a Stored Proc and would like to pass in a VARCHAR parameter. The
>problem is that the query will need to use it in a IN clause...
>DECLARE @.PARAM AS VARCHAR(20)
>SET @.PARAM = 'Dan', 'Mike', 'Lisa'
>SELECT *
>FROM Orders
>WHERE Name IN (@.PARAM)
>is there a away around this?
Hi Kunkel,
Several. And they are all described and compared at
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||thanks for that link! it was very helpful. but after i implemented the
articles ideas, i came across this solution:
DECLARE @.Var AS VARCHAR(100)
SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
DECLARE @.SQL AS varchar(1000)
SET @.SQL = 'SELECT *
FROM ORDERS
WHERE
NAME IN (' + @.Var + ')'
Exec(@.SQL)
"Hugo Kornelis" wrote:

> On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:
>
> Hi Kunkel,
> Several. And they are all described and compared at
> http://www.sommarskog.se/arrays-in-sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 28 Apr 2005 15:30:02 -0700, Kunkel wrote:

>thanks for that link! it was very helpful. but after i implemented the
>articles ideas, i came across this solution:
>DECLARE @.Var AS VARCHAR(100)
>SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
>DECLARE @.SQL AS varchar(1000)
>SET @.SQL = 'SELECT *
>FROM ORDERS
>WHERE
>NAME IN (' + @.Var + ')'
>Exec(@.SQL)
Hi Kunkel,
This is dynamic SQL, and I believe that this techinique is discussed at
Erland's site as well. Please don't do this if you can avoid it. It is a
severe breach of security - you give malicious users the ability to
inject SQL.
Erland has a great article with an in-depth explanation of pros and cons
of dynamic SQL as well: http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Passing datetime variable to stored proc as parameter

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

As a stopgap, I'm building the exec statement in an expression - converting the dates to single quoted strings.

I would still like to see the correct way to pass date parameters, so if anybody can help it would be much appreciated.

Thankee.

Mike

|||

Any chance your passing the value in as type "DBTimestamp"? Try changing the parameter to type DATE

This looks like a bug to me:

setup:

1. Extract date from query (SSIS will show type as DBTIMESTAMP..)

2. Pass that same date taken from SQL Server as a parameter of type DBTimestamp back into the same SQL Server (using same connection) and it will error with message like you have above.

I tried changing the stored procedure to take nvarchar(2000) so I could see what it was passing for the DBTimestamp parameter to SQL and it was passing '' (empty set.. not to be confused with NULL)

passing boolean to stored proc as SQLDBtype.bit not working

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@.bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.If you can execute the stored procedure with bit value in database, that means the value passed from application is not right. You may check the input value by inserting into a temp. table or as a return value to the calling app.|||thank you, that is what I thought.

I have printed out the value of the param in the trace and it is 0 which I find odd as that should be accepted as a valid bit, unless I'm totally missing something.

Is there not a way to pass 'true' or 'false' to the SQLDBtype.bit and have SQL Server convert it to 1 or 0?

still working on it here.
thanks again for your input|||What kind of control is "rblProMembCSNM"?|||thank you,

I took your advice and created a temp table and insert all of my values, after all that it was a different parameter I was passing and I was not escaping the ' ...... very frustrating to find that out after everything but I did learn quite a bit in my research.

Passing a string report parameter to a stored proc

I have created a report that uses a SQL stored procedure. I am passing 3
parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
created 3 report parameters and I am passing them to the stored proc via the
parameters tab for the dataset. In visual studio the closest option to
NVARCHAR I have for the report parameter is string. If I run the sp from the
data area and pass it the params it works fine but when I try to preview the
report and enter the params I get no data (also no error message or anything)
I'm thinking I may have to do some sort of conversion but I'm not sure what
it would be. Any help would be aprrciated.Be sure the store proc parameter is defined in your Report parameters list.
"Jan Shulse" wrote:
> I have created a report that uses a SQL stored procedure. I am passing 3
> parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
> created 3 report parameters and I am passing them to the stored proc via the
> parameters tab for the dataset. In visual studio the closest option to
> NVARCHAR I have for the report parameter is string. If I run the sp from the
> data area and pass it the params it works fine but when I try to preview the
> report and enter the params I get no data (also no error message or anything)
> I'm thinking I may have to do some sort of conversion but I'm not sure what
> it would be. Any help would be aprrciated.

Passing a comma delimited string of parameters to a stored proc

Hello,

I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.

But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

I would recommend Dr. Lisa to you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

|||

Hi,

You could create a function that would convert your string into a table and then use this table in you stored procedure.

Here is an example of a function that would convert comma separated list into a table:

http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html

Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:

SELECT <Fields>

FROM <Table> a

JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id

HTH,

|||

I usually use MVP Jens Suesmeyer's SPLIT function; it can be found here:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

passing a binary value to sql server 2005

I have been pulling my hair out on this.

I am trying to pass a binary value from an ASP.NET app to a VERY simple stored proc, and I can NOT get it to work!

Here is some of my code:

Code Snippet

ALTERPROCEDURE [dbo].[sp_SelectAppointments] @.PID VarChar(50)

AS

BEGIN

--set @.PID = 0x00000000000000B1

--PLEASE NOTE HERE THAT THE ABOVE VALUE(WHEN UNCOMMENTED) RETURNS RECORDS

--WHEN THE VALUE IS IN QUOTES (AS IT WOULD BE) IT DOES NOT WORK

SETNOCOUNTON;

SELECT Appt_Date, Appt_Description, Rn_Appointments_Id

FROM Rn_Appointments

WHERE project = @.PID --Project is a binary field.

END

I created a Dataset in VS2005, and here is the code that passes the value to the Dataset:

Code Snippet

Dim PID As String = Request.QueryString("pid")

PID = "0x00000000000000B1"

Dim da2 AsNew sp_SelectAppointmentsTableAdapter

GridView1.DataSource = da2.GetData2(PID)

GridView1.DataBind()

What am I missing? Can anybody please help me out here?

Thank you.

Steve

You are capturing the incoming parameter as varchar() and they attempting to equate a varchar() to a binary in the WHERE clause filter.

Have you attempted with a binary input parameter datatype?

Or, convert the parameter to binary inside the procedure?

ALTERPROCEDURE [dbo].[sp_SelectAppointments] @.PID binary

or

WHERE project = cast( @.PID as binary )

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variab
le
> number of parameters? I'm creating a stored proc to populate a Suggested P
O
> form. It may require filtering on one or more vendor IDs (string data). I'
ve
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it loo
ks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variable
> number of parameters? I'm creating a stored proc to populate a Suggested PO
> form. It may require filtering on one or more vendor IDs (string data). I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it looks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com