CODEWITHSUNDEEP
sql-servert-sql
Manast
Manast

Reputation: 3

Passing values into a Dynamic Stored Procedure

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?

Upvotes: 0

Views: 417

Answers (3)

KM.
KM.

Reputation: 103589

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

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

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

Upvotes: 3

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11359

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

Upvotes: 0

Related Questions