Solved

Passing a comma-separated list as a Stored Procedure parameter

Posted on 2008-06-23
10
1,303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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 ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 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