Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: using a paramater in sql of a sproc

    I am trying to use the @lookupTable in an inner join and it doesnt work, what am i doing wrong?

    ALTER PROC [dbo].[Triggers_On_Tables]
    @lookupTable as varchar(100)
    AS
    SELECT otabl.name
    , otr.name
    , State=
    Case When (otr.Status & 2048) >0 Then 'Disabled'
    When (otr.Status & 2048) =0 Then 'Enabled'
    End
    FROM SYSOBJECTS AS otabl
    INNER JOIN SYSOBJECTS AS otr
    ON otabl.id = otr.parent_obj
    INNER JOIN @lookupTable AS mk
    ON mk.tablename=otabl.name
    WHERE otr.type = 'tr'
    AND mk.impacted = 1
    GO

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You are assuming that the variable will be placed in the query as a reference, rather than as a string. Bad assumption, and one that is made by most new developers for some reason. You will need to use Dynamic SQL, instead.

    Code:
    ALTER PROC [dbo].[Triggers_On_Tables]
    @lookupTable as varchar(100)
    AS
    declare @sql nvarchar(4000)
    
    set @sql = 'SELECT otabl.name
    , otr.name
    , State=
    Case When (otr.Status & 2048) >0 Then 'Disabled'
    When (otr.Status & 2048) =0 Then 'Enabled'
    End
    FROM SYSOBJECTS AS otabl
    INNER JOIN SYSOBJECTS AS otr
    ON otabl.id = otr.parent_obj
    INNER JOIN ' + @lookupTable + ' AS mk
    ON mk.tablename=otabl.name
    WHERE otr.type = ''tr''
    AND mk.impacted = 1'
    
    exec (@sql)
    
    GO
    EDIT: Missed a close quote.

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    thanks i thought that might be it !

Posting Permissions

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