?
Solved

How write store procedure that accepts mutliple parameter for a single column?

Posted on 2011-04-20
12
Medium Priority
?
287 Views
Last Modified: 2012-05-11
I have a table TABLE1. It has 4 columns. COL1, COL2, COL3, COL4. I want to select data from this TABLE1 by passing input parameters from my ASP.Net application and this store procedure return data result back to the application.
This store procedure will have a SELECT SQL. For column COL1, I might be pass more than one input parameter. The number input parameters are not consistent, they might be one or more than one. I want my store procedure to be able to take one or more than one input parameter for a single column. How can I do this?
0
Comment
Question by:ASPNet_Learner
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432324
You can use the IN keyword. Pass the parameter as Varchar. Then in stored procedure

Select * From ... Where COL1 IN (@param)

Now you can pass param as "1" or you can pass "1, 3" or you can pass "1, 5, 11, 23" ...
0
 
LVL 15

Expert Comment

by:Praveen Venu
ID: 35432327
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35432345
Please pass parameter values in single parameter with separator like {; : ,}. Split the parameter in SP and use appropriate.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432364
>Split the parameter in SP and use appropriate.
How do you split strings in SQL?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35432493
CodeCruiser,
>>How do you split strings in SQL? <<
You use a function, this avoids the necessity to resort to use Dynamic SQL as in your suggestion.

ASPNet_Learner,
Without using Dynamic SQL your choices are:
1. A delimited string.
2. Xml
3. (SQL Server 2008 only) Table Valued Parameter.

Pick your poison.
0
 

Accepted Solution

by:
ASPNet_Learner earned 0 total points
ID: 35432500
PatelAlpesh:
Should I pass the single parameter in 'WHERE COL1={@COL1}' or 'WHERE COL1 IN{@COL1)'
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35432910
>You use a function, this avoids the necessity to resort to use Dynamic SQL as in your suggestion.
A custom function right? The reason I asked was that may be there is a built in function that I am not aware of.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35437063
>>A custom function right?<<
That is correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35437076
ASPNet_Learner,
>>I added this to my WHERE cluase
WHERE (@COL1 = '' OR CHARINDEX(CONVERT(VARCHAR(10), COL1),@COL1) > 0)
and passed input parameters as
',1,2,3,'
and it worked<<

If the table is large than this type of query should be avoided at all costs, as it will be a dog.  If on the other hand you have at most a few hundred rows and you do not expect to grow, then it should not prove to be a problem.
0
 

Author Comment

by:ASPNet_Learner
ID: 35440102
acperkins:

ASPNet_Learner,
Without using Dynamic SQL your choices are:
1. A delimited string.
2. Xml
3. (SQL Server 2008 only) Table Valued Parameter.


How do I use a delimited string?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35440797
1. You create a Table-Valued UDF that accepts a delimited string and returns a table.
2. In your Stored Procedure you Join against this function and pass in the delimited string.
0
 

Author Closing Comment

by:ASPNet_Learner
ID: 35458460
I added this to my WHERE cluase
WHERE (@COL1 = '' OR CHARINDEX(CONVERT(VARCHAR(10), COL1),@COL1) > 0)
and passed input parameters as
',1,2,3,'
 and it worked
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
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.
Suggested Courses

862 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