Passing in a set of values to a SP
-
I'm trying to pass in a set of values into a select statement in a SP. Unfortunately, I can't declare several variables because the number of values in the IN() statement varies based on the user entry.
--------------------------------------------------------------------
For example:
Declare
@Departments varchar(10)
Select * from EmployeeProfileTable where Dept in (@Departments)
--------------------------------------------------------------------
The departments can vary anywhere from one dept such as 'Housekeeping' to a set of values such as 'HOUSEKEEPING','ACCOUNTING','BUSINESSDEV','MANAGEMENT','DIETARY' and so on.
Note: I'm executing the SP and passing in the values from VB. Our DBA suggested we can try passing in as a string of all those values. It worked in the query analyzer. However, I tried to execute SP from VB, I got ODBC call failed error. Any suggestions, help??? Thanks.
-
Hi, I execute the sp in VB just like you are saying, the only different that I see is that the string is different I used it like this:
strCadena = "('HOUSEKEEPING','ACCOUNTING','BUSINESSDEV','MANAGEMENT','DIETARY')"
I don´t know if your are inserting ( ) into the string, I think that is the only thing that you are missing.
I hope this help you.
Ana
Ana -
If you post the code would be easier for us to help.
Andy
-
From what you've posted, dynamic SQL is really the only answer. Robert Marda explains this in one of his articles on dynamic SQL:
http://www.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp
K. Brian Kelley
http://www.truthsolutions.com/
K. Brian Kelley
@kbriankelley -
Thanks everyone for the valuable feedback. I took bkelly's advice and read the article on dynamic sql and it worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply