We help IT Professionals succeed at work.

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

Medium Priority
295 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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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" ...
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Please pass parameter values in single parameter with separator like {; : ,}. Split the parameter in SP and use appropriate.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>Split the parameter in SP and use appropriate.
How do you split strings in SQL?
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>A custom function right?<<
That is correct.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.