CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Kuntady Nithesh
Kuntady Nithesh

Reputation: 11721

Table variable not working in dynamic SQL

My trigger is as below ,

Alter TRIGGER [dbo].[LogTable_InsertTrigger] on [dbo].[std_table]  AFTER  INSERT
as

    DECLARE  @ColName varchar(50), @QueryText nvarchar(max)

    declare @inserted  TABLE(
      [CountryID] [int] NOT NULL,
      [Country] [nvarchar](255) NOT NULL,
      [RegionId] [int] NULL
    )
    insert into @inserted
    select * from inserted

    DECLARE objCursor CURSOR FAST_FORWARD FOR
    select  ColName from dbo.getColumnNames('std_table')  

    OPEN objCursor 
    FETCH NEXT FROM objCursor INTO @ColName
    WHILE @@FETCH_STATUS = 0
    BEGIN
      set @QueryText= '
         insert into dbo.LogTable
         (StandardType,Attribute,Action,NEwValue,UserId,ModifiedDate)
        select ''Country'','''+@ColName+''',''Insert'','+@ColName+',1,getdate()
        from @inserted'

       EXEC sp_executesql   @QueryText  

       FETCH NEXT FROM objCursor INTO @ColName
    END
    CLOSE objCursor
    DEALLOCATE objCursor

When I try to insert to table std_table in DA Layer I get the exception Must declare the table variable "@inserted".

I couldn't use the inserted table directly because I am writing a dynamic query, inside which magic tables wont work. So I am trying to dump the data in inserted table to a temp table and to access from that.

I tried with

select * 
into #inserted
from inserted

This works, but since my application is accessed by many users over network this will cause data issues. So I can't use this.

Upvotes: 2

Views: 2715

Answers (1)

anon
anon

Reputation:

Several things wrong here.

  1. That is a table variable, not a user defined table type.
  2. If #temp tables work, why do you think that will cause data issues for multiple users? Each user will get their own version of the #temp table.
  3. If you know there are exactly three columns and you can hard-code the table variable declaration, why do you need to then generate the three sets of inserts dynamically? Aren't the column names CountryID,Country,RegionID?

If you really need to do this dynamically then it seems like you could do this an easier way without an explicit cursor - not that this is necessarily a bad thing or that it will perform any worse than the below, but the cursor is just much more verbose and ugly code:

ALTER TRIGGER [dbo].[LogTable_InsertTrigger] 
ON [dbo].[std_table]  
AFTER  INSERT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT * INTO #t FROM inserted;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'';

  SELECT @sql = @sql + CHAR(13) + CHAR(10) + N'INSERT INTO dbo.LogTable
        (StandardType,Attribute,Action,NewValue,UserId,ModifiedDate)
        SELECT ''Country'','''+ColName+''',''Insert'','+ColName+',1,GETDATE() 
     FROM #t;'
  FROM dbo.GetColumnNames('std_table');

  EXEC sp_executesql @sql;
END
GO

Upvotes: 1

Related Questions