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