Solved

Passing a comma-separated list as a Stored Procedure parameter

Posted on 2008-06-23
10
1,302 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 125 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

828 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