Dynamic SQL and IN list - Thanks!

From: Amelia (anonymous_at_discussions.microsoft.com)
Date: 06/10/04

  • Next message: anonymous_at_discussions.microsoft.com: "Dynamic SQL and IN list - Thanks!" 
    Date: Wed, 9 Jun 2004 17:34:41 -0700

    I am trying to get dynamic SQL working with an IN list and
    can't find any answers in the SQL Server Help.

    1)I can get a simple parameter working as below

    declare @sSql nvarchar(500)
    declare @sParameters nvarchar(500)
    declare @sPremises varchar(10)

    set @sSql = 'Select * from PREMISE where premises =
    @sPremises'
    set @sParameters = '@sPremises varchar(10)'

    EXEC sp_executesql @sSql, @sParameters, @sPremises = '25'

    2)BUT I cannot get this to work

    declare @sSql nvarchar(500)
    declare @sParameters nvarchar(500)
    declare @sPremises varchar(10)

    set @sSql = 'Select * from PREMISE where premises IN
    (@sPremises)'
    set @sParameters = '@sPremises varchar(50)'

    EXEC sp_executesql @sSql, @sParameters, @sPremises = '25,
    10'

    3) I also tried the following which executes but with no
    results.

    declare @sSql nvarchar(500)
    declare @sParameters nvarchar(500)
    declare @sPremises nvarchar(50)

    set @sSql = 'Select * from PREMISE where premises IN ('+
    @sPremises +')'
    set @sPremises = '25, 10'

    EXEC sp_executesql @sSql

    Is there a way to do this - Thanks for the assistance
    :0) A.

  • Next message: anonymous_at_discussions.microsoft.com: "Dynamic SQL and IN list - Thanks!"