Stored procedure with mulitple items passed in string

This might be an idea:

SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number

Where iter$simple_intlist_to_tbl(@ids) is a function that returns a table.

But what if the string passed were empty or no string passed.

I used to have a dropdown that passed a value or none if "All" were selected
in my dropdown.

So my stored procedure would have @ids defined as:

@ids varchar = null

So I can do the following:

Select...
Where (@ids is null or P.ProductID = @ids)

This would allow nothing to be passed and not be part of the filtering.

Is there a way to do the above Select statement in this way - where nothing
is passed.

Otherwise, it seems that dynamic sql may be the best option.

Thanks,

Tom

"John Bell" wrote in message

>
> "tshad" wrote in message
>
>>I am getting multiple selections from a ListBox and want to send it to a
>>stored procedure to use in a where clause. Something like:
>>
>> SELECT Name, Address
>> FROM Company
>> WHERE Status in ("Active", "Pending")
>>
>> I would probably send this some sort of delimited sring.
>>
>> Would I have to use dynamic sql to handle this?
>>
>> Thanks,
>>
>> Tom
> Tom
>
> If you searched google you would get thousands of examples such as
>
> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/18/passing-...array-o
>
> and the most comprehensive of articles at
> http://www.sommarskog.se/arrays-in-sql.html
>
> If you are using SQL 2008 then the Table Valued Parameter feature is also
> available.
>
> John
 >> Stay informed about: Stored procedure with mulitple items passed in string 

Another way
DECLARE @t VARCHAR(50)

SET @t = 'white,smith'

SELECT *

FROM authors

WHERE ',' + @t + ',' LIKE '%,' + au_lname + ',%'



"tshad" wrote in message

> This might be an idea:
>
> SELECT P.ProductName, P.ProductID
> FROM Northwind..Products P
> JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
>
> Where iter$simple_intlist_to_tbl(@ids) is a function that returns a table.
>
> But what if the string passed were empty or no string passed.
>
> I used to have a dropdown that passed a value or none if "All" were
> selected in my dropdown.
>
> So my stored procedure would have @ids defined as:
>
> @ids varchar = null
>
> So I can do the following:
>
> Select...
> Where (@ids is null or P.ProductID = @ids)
>
> This would allow nothing to be passed and not be part of the filtering.
>
> Is there a way to do the above Select statement in this way - where
> nothing is passed.
>
> Otherwise, it seems that dynamic sql may be the best option.
>
> Thanks,
>
> Tom
>
> "John Bell" wrote in message
>
>>
>> "tshad" wrote in message
>>
>>>I am getting multiple selections from a ListBox and want to send it to a
>>>stored procedure to use in a where clause. Something like:
>>>
>>> SELECT Name, Address
>>> FROM Company
>>> WHERE Status in ("Active", "Pending")
>>>
>>> I would probably send this some sort of delimited sring.
>>>
>>> Would I have to use dynamic sql to handle this?
>>>
>>> Thanks,
>>>
>>> Tom
>> Tom
>>
>> If you searched google you would get thousands of examples such as
>>
>> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/18/passing-...array-o
>>
>> and the most comprehensive of articles at
>> http://www.sommarskog.se/arrays-in-sql.html
>>
>> If you are using SQL 2008 then the Table Valued Parameter feature is also
>> available.
>>
>> John
>
>
 >> Stay informed about: Stored procedure with mulitple items passed in string 

You used double quotes which made them into data element names and not
strings. THIS IS FUNDAMENTAL!! You are at the point where you cannot
ask a question. Please, please, please, do your homework BEFORE you
come to class.

>> I would probably send this some sort of delimited string. <<

NEVER. Dr. Codd. Foundation of RDBMS. First Normal Form (1NF).
Etc. DUH!

>> Would I have to use dynamic sql [sic: SQL] to handle this? <<

YOU might, but a good SQL programmer would not. All you are getting
here are kludges around FUNDAMENTAL errors (i.e stick a rock under it
and it will look level). I hope that you are not working in a place
where your ignorance can kill people.
 >> Stay informed about: Stored procedure with mulitple items passed in string 

--CELKO-- (jcelko212@earthlink.net) writes:
> You used double quotes which made them into data element names and not
> strings. THIS IS FUNDAMENTAL!! You are at the point where you cannot
> ask a question. Please, please, please, do your homework BEFORE you
> come to class.
>
>>> I would probably send this some sort of delimited string. <<
>
> NEVER. Dr. Codd. Foundation of RDBMS. First Normal Form (1NF).
> Etc. DUH!
>
>>> Would I have to use dynamic sql [sic: SQL] to handle this? <<
>
> YOU might, but a good SQL programmer would not. All you are getting
> here are kludges around FUNDAMENTAL errors (i.e stick a rock under it
> and it will look level). I hope that you are not working in a place
> where your ignorance can kill people.

You stinking a*hole. Learn to behave before you come to class to teach or
whatever you are pretending to do. If you can't answer the question, just
move to the next post, and skip your insults.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Stored procedure with mulitple items passed in string 

tshad (tfs@dslextreme.com) writes:
> This might be an idea:
>
> SELECT P.ProductName, P.ProductID
> FROM Northwind..Products P
> JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
>
> Where iter$simple_intlist_to_tbl(@ids) is a function that returns a table.
>
> But what if the string passed were empty or no string passed.
>
> I used to have a dropdown that passed a value or none if "All" were
> selected in my dropdown.
>
> So my stored procedure would have @ids defined as:
>
> @ids varchar = null
>
> So I can do the following:
>
> Select...
> Where (@ids is null or P.ProductID = @ids)
>
> This would allow nothing to be passed and not be part of the filtering.
>
> Is there a way to do the above Select statement in this way - where
> nothing is passed.
>
> Otherwise, it seems that dynamic sql may be the best option.

SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
WHERE @ids IS NULL OR
EXISTS (SELECT * FROM
iter$simple_intlist_to_tbl(@ids) i
WHERE P.ProductID = i.number)

Or simply:

IF @ids IS NULL
-- plain SELECT
ELSE
-- SELECT like above.

The latter as the advantage of using an index when you specify a list. But
it's not tenable, if you have several options. In that case dynamic SQL
may be way do go. If you haven't looked at my article on
http://www.sommarskog.se/dyn-search-2005.html, you may intereted in
doing so.






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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Stored procedure with mulitple items passed in string 

A true master class of how to wreck the little reputation you had left and
make sure you are never employed by anybody who wants balanced and
un-opinionated advice.

What a complete ignorant troll you are --celko--.

You really are an embarrassment within the industry - is it any wonder
nobody wants you in their work places?

--ROGGIE--


"--CELKO--" wrote in message

> You used double quotes which made them into data element names and not
> strings. THIS IS FUNDAMENTAL!! You are at the point where you cannot
> ask a question. Please, please, please, do your homework BEFORE you
> come to class.
>
>>> I would probably send this some sort of delimited string. <<
>
> NEVER. Dr. Codd. Foundation of RDBMS. First Normal Form (1NF).
> Etc. DUH!
>
>>> Would I have to use dynamic sql [sic: SQL] to handle this? <<
>
> YOU might, but a good SQL programmer would not. All you are getting
> here are kludges around FUNDAMENTAL errors (i.e stick a rock under it
> and it will look level). I hope that you are not working in a place
> where your ignorance can kill people.
 >> Stay informed about: Stored procedure with mulitple items passed in string