?
Solved

Passing a comma-separated list as a Stored Procedure parameter

Posted on 2008-06-23
10
Medium Priority
?
1,308 Views
Last Modified: 2009-10-15
Hello experts,

I have the followinf stored procedure;-
SELECT TOP (@top) * FROM
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records_Retention
     ) AS a
     WHERE num >= @start

Now I need to filter the result, i.e. add a where clase to it like so;-
SELECT TOP (@top) * FROM
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records_Retention where field1 = blue or field2 = blue or field3=blue
     ) AS a
     WHERE num >= @start

The thing is that often the search term may be a comma delimited list like so;-

SELECT TOP (@top) * FROM
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records_Retention where field1 = 'blue, red, green, yello' or field2 = 'blue, red, green, yello' or field3='blue, red, green, yello'
     ) AS a
     WHERE num >= @start


and sometimes the search keyword may be blank, how do I adjust my stored procedure to be able to handle both situations efficiently.

Thank you
0
Comment
Question by:claracruz
  • 6
  • 2
  • 2
10 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21846263

SELECT TOP (@top) * FROM 
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records_Retention 
          WHERE field1 IN ('blue, red, green, yello') 
          or field2 IN ('blue', 'red', 'green', 'yello') 
          or field3 IN ('blue', 'red', 'green, 'yello') 
     ) AS a
     WHERE num >= @start
 
 
If you want to return all when searchterm is NULL then:
 
SELECT TOP (@top) * FROM 
     (
          SELECT *,
          ROW_NUMBER() OVER (ORDER BY record_id) AS num
          FROM Records_Retention 
          WHERE field1 IN (@SearchTerm) 
          or field2 IN (@SearchTerm) 
          or field3 IN (@SearchTerm) 
          or @SearchTerm IS NULL
     ) AS a
     WHERE num >= @start

Open in new window

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21846516
Populate a in-memory table with the list of criteria and use that in your where. Assuming your parameter is @parm:

declare @iPos int, @Len int
declare @List table(Item varchar(100))
Select @parm = Ltrim(@parm)
Select @Len = Len(@parm)
if @len > 0  
     select @parm = @parm + ','

select @iPos = charindex(',', @parm)
while @iPos > 0
      begin
      Insert @List values(LTrim(left(@parm, @iPos - 1)))
      select @Parm = substring(@parm, @ipos + 1, len(@parm))
      select @iPos = charindex(',', @parm)
      end

Then your Where clause becomes:

Where field1 in (select Item from @List) ORField2 in  (select Item from @List) OR @Len = 0
0
 
LVL 4

Author Comment

by:claracruz
ID: 21847002
hi guys,

thank you.. which is more efficient of the two techniques please and why?

Thanks,

Clara
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:claracruz
ID: 21847116
hi rickchild,
I get the following error if the @searchTerm parameter is null value;-

Microsoft OLE DB Provider for SQL Server error '80040e10'

Procedure or Function 'sp_GetRecords' expects parameter '@SearchTerm', which was not supplied.

/Records/testasp, line 360

I guess its occuring as no value is being passed to @SearchTerm... how do I work around that?

Thanks,

Clara
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21847564
You could set a default value for the parameter when you declare it.

DECLARE @searchTerm varchar(1000) = 'ALL'

And then modify your statement to contain

or @SearchTerm = 'ALL'

The only problem with my method is that you will need to pass the string properly formatted with quotes.
An alternative is to use a table valued function, to send a standard delimited list of data to the function.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21847621
<WHERE field1 IN (@SearchTerm) >
You can't supply a comma separated list to the IN operator - it doesn't work.

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21847631
Sorry, missed a piece...
<WHERE field1 IN (@SearchTerm) >
You can't supply a comma separated list in a variable to the IN operator - it doesn't work.

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21847687
Set the default for the parameter when defining it - then you can get the full list by executing without supplying a parameter at all:

exec inCSV

Is that what you meant?
alter procedure InCSV(@parm varchar(max) = '')
As 
Begin
declare @iPos int, @Len int
declare @List table(Item varchar(100)) 
Select @parm = Ltrim(@parm)
Select @Len = Len(@parm)
if @len > 0  
     select @parm = @parm + ',' 
 
select @iPos = charindex(',', @parm)
while @iPos > 0
	begin
	Insert @List values(LTrim(left(@parm, @iPos - 1)))
	select @Parm = substring(@parm, @ipos + 1, len(@parm))
	select @iPos = charindex(',', @parm)
	end
select * from records_retention
WHere Field1 IN(select item from @list) or field2 in (select item from @list)
OR Field1 IN(select item from @list) or field2 in (select item from @list)
OR Field1 IN(select item from @list) or field2 in (select item from @list)
OR @len = 0
End

Open in new window

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21847703
Sorry again! Cut and paste error, the where clause shoud be:

WHere Field1 IN(select item from @list) or field2 in (select item from @list)
OR Field2 IN(select item from @list) or field2 in (select item from @list)
OR Field3 IN(select item from @list) or field2 in (select item from @list)
OR @len = 0
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 21847745
I must be on another planet today:

WHere Field1 IN(select item from @list)
OR Field2 IN(select item from @list)
OR Field3 IN(select item from @list)
OR @len = 0
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question