Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2001
    Posts
    36

    Exclamation Unanswered: Procedure problem

    Here is a procedure :

    create proc sptry ( @str varchar(100) )
    as
    select name from sysobjects where name in ( @str )

    When I execute it as :

    exec sptry "Categories,CK_Discount"

    I am not able to get even a single row where as I thaught I should have gotten two rows.
    Any ideas how to pass multiple strings concatenated in one string to a in clause ?

    Thanks.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The SELECT statement does not distinguish the string as having to arguments "Categories" and "CK_Discount", it sees it as one string "Categories,CK_Discount".

    To have the SELECT statement work in the form as

    select name from sysobjects where name in ('Categories','CK_Discount') you will have to use dynamic SQL and contruct the string

    Code:
    create proc sptry ( @str varchar(100) ) 
    as 
    exec ('select name from sysobjects where name in (' + @str + ')') 
    
    exec sptry "'titles','authors'"
    Notice the quotes around the values when being passed into the procedure

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •