March 26, 2003 at 1:19 pm
I am trying to use a parameter
@xportchoice NvarChar(1000)
(then use it:)
AS
Select CompanyID, CompanyName
FROM CompanyTable
WHERE CompanyName IN (@xportChoices).
(When I type in the choices:)
WHERE CompanyName IN ('ABC','DEF','GHI')
...the SP works fine, however when I pass in a value using the @xportChoices parameter The StoredProcedure Fails, except if I pass only ONE value in.
('ABC','DEF') Doesn't work but ('ABC') does. I think it is treating the entire @xportChoices as ONE string...
How can I get it to Recognize the Comma and single quotes in the IN Statement?
March 26, 2003 at 1:27 pm
Check here :
Because some one just asked a very similar question and was answered right.
March 26, 2003 at 4:35 pm
That solution did not work as I have a String of text...
IN ('Company1, 'Company2', 'Company3')
March 27, 2003 at 1:37 am
The solution presented in the other thread does work for your solution, you just need to create a dynamic sql statement of your query and concatenate your string of values in the IN-operator. However, dynamic sql is normally not the best solution. Another solution is to create a temp table and insert your choices there, then join this temp table with your CompanyTable to restrict rows to those with CompanyNames in the temp table.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 27, 2003 at 1:41 am
Just remembered possibly the best article on this matter, written by Swedish MVP Erland Sommarskog: http://www.algonet.se/~sommar/dynamic_sql.html
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 27, 2003 at 4:22 am
The problem is only one value can be submitted with one variable. If you need to submit multiples and the possible number is finite (meaning you know how many) then your best method is to do like so.
CREATE PROCEDURE xxx
@xportchoice1 NvarChar(1000) = 'somevaluethatwon'texist', -- Or use NULL
@xportchoice2 NvarChar(1000) = 'somevaluethatwon'texist', -- Or use NULL
@xportchoice3 NvarChar(1000) = 'somevaluethatwon'texist', -- Or use NULL
@xportchoice4 NvarChar(1000) = 'somevaluethatwon'texist', -- Or use NULL
@xportchoice5 NvarChar(1000) = 'somevaluethatwon'texist' -- Or use NULL
AS
SET NOCOUNT ON
Select CompanyID, CompanyName
FROM CompanyTable
WHERE CompanyName IN ( @xportChoices1, @xportChoices2, @xportChoices3, @xportChoices4, @xportChoices5 )
Any parameter not submitted will be defaulted to the value after the =, by using something that would not exists or NULL if it would not exists then running would pull the values that are good.
If the number of inputs is not finite then try create the biggests parameter you can and submit all comma seperated into the SP. In there create a Temp table with a column for the single values and using a where clause with charindex go thru and pull each value off the variable based on the comma position and insert into the temp table. Then do your in statement as SELECT colx FROM #temptbl.
I had an example in another thread but cannot find it right off.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply