Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    4

    Question Unanswered: Dynamic SQL statements in a stored procedure

    Hi

    I have a small problem writing a stored procedure in a SQL Server 2000 database.

    I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server don´t generate a resulting SQL-statement.

    Example:

    CREATE PROCEDURE spDynStatement
    AS

    DECLARE @sPartOfSQLStatement NVARCHAR(100)

    -- Some T-SQL that generates the dynamic part of the SQL-statement
    -- .
    -- .
    -- .

    -- As substitute I insert the string expression
    SET @sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''

    -- SELECT @sPartOfSQLStatement results in: '1' , '1.5'

    SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @sPartOfSQLStatement ) -- does not work

    SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
    GO

    Thankfull for ideas on how to solve my problem,

    Peter

  2. #2
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    Try this

    DECLARE @sSQLStatement VARCHAR(4000)

    SET @sSQLStatement = 'SELECT * FROM BBNOrganization WHERE OrgStructureID IN(' + '''1''' + ', ' + '''1.5''' + ')'

    EXEC @sSQLStatement

Posting Permissions

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