January 7, 2011 at 1:31 pm
Hello guys,
I want to pass comma saperated list into the stored procedure and I am referencing the link below to porduce my code:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Now, i did write my stored procedure something like this:
alter PROCEDURE [dbo].[usp_Get_token_list] 'token, contacts'
-- Add the parameters for the stored procedure here
@test-2 as varchar(200)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql varchar(600)
SET @sql = 'select * from Templates_Replacement_Token_Definitions
where
Table_reference
in
(' + @test-2 + ')'
exec(@sql)
END
GO
and it gives me following error:
Invalid column name 'token'.
I thought that it would NOT be the column name but the value against which the column would be searched. Can anybody please explain me what is wrong with this code and how to correct this?
January 7, 2011 at 1:35 pm
You need to put single-quotes around the values in your delimited list. Like this:
'value1','value2','value3'
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 1:43 pm
Well, this values are being passed as varchar(200) which is one of the parameter in the stored procedure.
If I do 'value1', 'value2', then it gives me too many parameters error because it is considering them as as saperate values.
January 7, 2011 at 1:45 pm
Then you'll need to escape the single-quotes in the parameter value. You do that by using two single-quotes in a row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 1:53 pm
Something like this? : ''token, contacts''
January 10, 2011 at 7:25 am
No. Like this:
''value1'',''value2'',''value3''
Note that those are NOT quotation marks, they are double-apostrophes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply