IN and parameter query
-
I'm after some help, this might turn out to be very easy. But I now hurting my small brain.
I have list out the below in 3 steps to try and explain.
Step1
update tbl_fred SET Completed='1' where UniSeq IN (1,2,3,4,5)
Runs fine
Step 2
make update into a Stored Proc
@UNISEQ nvarchar(250) AS update tbl_fred SET Completed='1' where Uniseq In (@uniseq)
Then call the query from SQL Analyzer tool..... exec sp_fred 1 ...... works fine.
Step 3
exec sp_fred '1,2,3,4,5' .............because there could be a whole lot of values.
Error converting data type nvarchar to numeric and if I try again this time without the single quotes... I get... Procedure or function sp_fred has too many arguments specified.
************************************
Now I understand why, but can't for the life of me seem to be able to fix it.
Could somebody please help?
-
To solve your problem, you need to make use of dynamic SQL inside your stored procedure. Your stored procedure may look like this:
DECLARE @vSQLStmt VARCHAR(1000)
SET @vSQLStmt = 'update tbl_fred SET Completed=''1'' where Uniseq In (' + @uniseq + ')'
EXECUTE (@vSQLStmt)
-
The problem is:
SQL is looking at your passed variable 1,2,3,4,5 as a LITERAL and NOT a NUMERIC ARRAY.
What you want to do cannot be accomplished by what you are trying to do.
There are 2 schools of thought to get around this
1. Write Dynamic SQL
2. Pass your "array" as records into a #TEMP table and then do your SELECT IN off of that...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Use the function descibed here http://www.sqlservercentral.com/scripts/contributions/157.asp
to convert your comma separated string into a table.
Example:
Select * from table_a where id IN (Select * from fn_split(@list) )
Or search this site for several other similar functions.
Edit: corrected syntax in example.
-
You guys are the best.....
Very grateful.The dyamic code works great and my asp page now updates the datebase and marks off completed entries.
Top notch!!!
-
>>The dyamic code works great
Until your 1st SQL Injection attack from the ASP page ...
-
Please read this if don't wanna lose your server to an attacker :
The Curse and Blessings of Dynamic SQL
I would strongly suggest that you use the split function as it is much more safe.
-
Try using charindex() eg:
declare @v1 varchar(100)
set @v1 = '1,2,3,4,5' --your incoming list
set @v1 = ','+@v1+','
select * from YourTable where charindex(','+YourField+',',@v1) > 0
-
It's a nice trick but it's the slowest of 'em all. The best is still the split function.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply