CODEWITHSUNDEEP
sqlsql-server
Diogo Santos
Diogo Santos

Reputation: 830

Inserting into a Table the result between a variable and a table parameter

Having the following procedure:

CREATE PROCEDURE [dbo].[Gest_Doc_SampleProc] 
    @Nome nvarchar(255), 
    @Descritivo nvarchar(255), 
    @SampleTable AS dbo.IDList READONLY
AS

DECLARE @foo int;

SELECT @foo=a.bar FROM TableA a WHERE a.Nome=@Nome

IF NOT EXISTS (SELECT a.bar FROM TableA a WHERE a.Nome=@Nome)
BEGIN
    INSERT INTO TableA VALUES (@Nome,@Descritivo)
    INSERT INTO TableB VALUES (scope_identity(),@SampleTable)
END

I am trying, as shown, inserting into TableB all the values of SampleTable, together with the scope_identity.

SampleTable is as:

CREATE TYPE dbo.SampleTable
AS TABLE
(
  ID INT
);
GO

How can I correctly achieve this?

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The right way to do this type of work is the OUTPUT clause. Although technically not needed for a single row insert, you might as well learn how to do it correctly. And even what looks like a single row insert can have an insert trigger that does unexpected things.

PROCEDURE [dbo].[Gest_Doc_SampleProc] (
    @Nome nvarchar(255),
    @Descritivo nvarchar(255), 
    @SampleTable AS dbo.IDList
) READONLY AS
BEGIN
    DECLARE @ids TABLE (id int);
    DECLARE @foo int;

    SELECT @foo = a.bar
    FROM TableA a
    WHERE a.Nome = @Nome;

    IF NOT EXISTS (SELECT 1 FROM TableA a WHERE a.Nome = @Nome)
    BEGIN
       INSERT INTO TableA (Nome, Descritive)
            OUTPUT Inserted.id  -- or whatever the id is called
            INTO @ids;
           VALUES (@Nome,@Descritivo)


       INSERT INTO TableB (id, sampletable)
           SELECT id, @SampleTable
           FROM @ids;
    END;
END;  -- Gest_Doc_SampleProc

In addition to using OUTPUT, this code also adds column lists to the INSERTs. That is another best practice.

Upvotes: 2

Related Questions