Passing values into a Dynamic Stored Procedure

Question

I'm trying to build something like the following sproc. However I can't seem to be able to let the trigger know about my scoped variables:

CREATE PROCEDURE delete_me
    @Name VARCHAR(25),
    @Value DECIMAL(13,4),
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = '
      CREATE TRIGGER temptrig ON TheTable FOR INSERT
      AS 
      DECLARE @Name VARCHAR(25)
      INSERT AnotherTable
      VALUES (@@IDENTITY, @Name)'

    EXEC sp_executeSQL @SQL

    INSERT INTO TheTable
    (
        Value
    )
    VALUES
    (
        @value,
    )

    DROP TRIGGER temptrig

    SELECT SCOPE_IDENTITY()
END

How can I make the trigger learn about @Name?

What are trying to do? Not technically, but what is the *real* problem you're trying to solve. — gbn, Jun 28 '11 at 11:46
I need to get hold of the identity value in TheTable. However, because of FK relationships, that has to be the first table to insert values to. I'm pretty newb when it comes to sql server... — Manast, Jun 28 '11 at 11:52

Aaron Bertrand · Answer 1 · 2011-06-28T11:40:33.063

3

Have you considered what happens here if two users run this procedure at the same time? And why do you need to create a temporary trigger for this? Why not just insert into AnotherTable directly, if all you're going to do is drop the trigger afterward?

DECLARE @sql NVARCHAR(MAX) = N'
      CREATE TRIGGER temptrig ON TheTable FOR INSERT
      AS 
          INSERT AnotherTable
          VALUES (@@IDENTITY, ''' + @Name + ''')';

EDIT

Getting rid of the trigger, you can just do this:

CREATE PROCEDURE dbo.delete_me
    @Name  VARCHAR(25),
    @Value DECIMAL(13,4),
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO TheTable
    (
        Value
    )
    VALUES
    (
        @value
    );

    INSERT INTO AnotherTable(columns) 
        SELECT SCOPE_IDENTITY(), @Name);
END

edited Jun 28 '11 at 11:40

answered Jun 28 '11 at 11:28

Aaron Bertrand

272,866
37
466
490

Hmm... in fact I haven't considered that possibility no. The trigger here is helping me keep track of the identity variable. Because I need to first insert to TheTable and that's the Identity value I need returned. – Manast Jun 28 '11 at 11:34
But you do bring an important point. I'll need to scratch this. – Manast Jun 28 '11 at 11:36
1

**you don't need two insert statements, you can everything necessary in a single insert (see my answer for how)**. Also, your procedure does not return the identity value in a result set, like the OP's does. – KM. Jun 28 '11 at 12:30
@Manast: if you just want get the identity variable, please see KM's answer with INSERT...OUTPUT – EricZ Jun 28 '11 at 13:10
@KM, your single statement solution using OUTPUT makes an assumption about the version of SQL Server that is being used. When a version isn't specified, I try to provide code samples that don't rely on a more recent version. – Aaron Bertrand Jun 28 '11 at 15:19
@Aaron Bertrand, oh come on ;-) your answer won't work on SQL Server 7.0, you'd have to use @@IDENTITY for that version! SQL Server 2005 is hardly recent (where OUTPUT was implemented). In general, I tend to do the opposite. I find that most people using SQL Server 2000 and older know they are on an ancient version and most on-line help isn't written for them, so they mention their version in the question. – KM. Jun 29 '11 at 12:14
@KM our opinion may differ. You made a point of illustrating why my solution was inferior; I was just pointing out that yours isn't perfect, either. :-) – Aaron Bertrand Jun 29 '11 at 13:14
@Aaron Bertrand, your answer still got more points than mine did. ;-) – KM. Jun 29 '11 at 14:58

KM. · Accepted Answer · 2011-06-28T12:38:00.433

if you are trying to insert into two tables and return a result set, you can do all of that in ONE INSERT STATEMENT!!, see here:

CREATE PROCEDURE delete_me
    @Name VARCHAR(25),
    @Value DECIMAL(13,4),
AS
BEGIN
    SET NOCOUNT ON;

    --insert row into TheTable
    INSERT INTO TheTable  
    (
        Value
    )
    --insert row into AnotherTable
    OUTPUT INSERTED.IdentityColumn, INSERTED.col2...
        INTO AnotherTable
    --return result set
    OUTPUT INSERTED.IdentityColumn
    VALUES
    (
        @value,
    )

END 
GO

working sample code script:

DECLARE @TheTable     table (RowID int identity ( 1,1) primary key, RowValue1 varchar(5), RowValue2 varchar(5))
DECLARE @AnotherTable table (RowID int, RowValue1 varchar(5), RowValue2 varchar(5))

--insert row into TheTable
INSERT INTO @TheTable  
(
    RowValue1,RowValue2
)
--insert row into AnotherTable
OUTPUT INSERTED.RowID, INSERTED.RowValue1, INSERTED.RowValue2
    INTO @AnotherTable
--return result set
OUTPUT 'From Insert' AS WOW, INSERTED.RowID
VALUES
(
    'aaa','bbb'
)


SELECT * FROM @TheTable
SELECT * FROM @AnotherTable

OUTPUT:

WOW         RowID
----------- -----------
From Insert 1

(1 row(s) affected)

RowID       RowValue1 RowValue2
----------- --------- ---------
1           aaa       bbb

(1 row(s) affected)

RowID       RowValue1 RowValue2
----------- --------- ---------
1           aaa       bbb

(1 row(s) affected)

EDIT FYI, this is valid as well:

DECLARE @MyIdentity int --create a local variable to store the identity

INSERT INTO table1 (... ) values (... ) --generate the identity value

SET @MyIdentity=SCOPE_IDENTITY --capture the identity value

INSERT INTO table2 (... ) values (...@MyIdentity ) --use stored identity value

SELECT @MyIdentity --return a result set of the stored identity value

I would advise against using a trigger and stick to the single INSERT as I describe in my answer. However, if you need to pass a value into a trigger, you can use CONTEXT INFO: http://stackoverflow.com/questions/2873104/pass-tenant-id-via-sql-server-connection/2873266#2873266 — KM., Jun 28 '11 at 13:36

score 0 · Answer 3 · answered Jun 28 '11 at 11:29

Is this what you want to accomplish?

   CREATE PROCEDURE delete_me
       @Name VARCHAR(25),
       @Value DECIMAL(13,4),
   AS
   BEGIN
        SET NOCOUNT ON;

       DECLARE @sql NVARCHAR(MAX) = '
         CREATE TRIGGER temptrig ON TheTable FOR INSERT
         AS 
         INSERT AnotherTable
         VALUES (@@IDENTITY, ''' + @Name + ''')'

       EXEC sp_executeSQL @SQL

       INSERT INTO TheTable
       (
            Value
       )
       VALUES
       (
           @value,
       )

       DROP TRIGGER temptrig

       SELECT SCOPE_IDENTITY()
   END

I hope this isn't production code. You're wide open for a [SQL Injection][1] attack. Use parameters instead of string concatenation. [1]: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain — Anthony Faull, Jun 28 '11 at 13:55

Passing values into a Dynamic Stored Procedure

3 Answers3