Passing a comma-separated list as a Stored Procedure parameter

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
LVL 4
claracruzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rickchildCommented:

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
MikeTooleCommented:
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
claracruzAuthor Commented:
hi guys,

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

Thanks,

Clara
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

claracruzAuthor Commented:
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
rickchildCommented:
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
MikeTooleCommented:
<WHERE field1 IN (@SearchTerm) >
You can't supply a comma separated list to the IN operator - it doesn't work.

0
MikeTooleCommented:
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
MikeTooleCommented:
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
MikeTooleCommented:
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
MikeTooleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.