Solved

Passing a comma-separated list as a Stored Procedure parameter

Posted on 2008-06-23
10
1,305 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
Industry Leaders: 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

615 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