SQL Server – How to Insert Multiple Values into Stored Procedure

sql serversql-server-2008-r2t-sql

I have 2 System Stored Procedures.

1) exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID=000000

The first proc retrieves a column called "localUpdateID" with 30 000 rows.
In the second proc you have specify the retrieved row from the first proc at "@localUpdateID=000000"

Copying each one of the 30 000 rows and pasting it into the second proc will take forever.

I have tried:

1) exec spDeleteUpdate @localUpdateID = exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID IN ('00000', '00001')

Best Answer

You need to update your first stored procedure so it returns data using an OUT parameter.

You can create a temporary table variable that you will use to temporarily store the output IDs of your first stored procedure and then send them to the second.

It should end up looking something like this:

declare @OutputIdList table(localUpdateId int)
exec spGetObsoleteUpdatesToCleanup @OutputIdList=@OutputIDs OUTPUT
exec spDeleteUpdate select localUpdateId  from @OutputIdList

Microsoft has a blog post that explains how to give OUT parameters to stored procedures.

Here's some copy-pasted data from it.

    CREATE PROCEDURE Myproc
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc

OUTPUT parameter

Your stored procedure is defined as having a parameter of @OrderCount with a direction of OUTPUT

If you wanted to use the stored procedure within a tool, SSMS, .NET, whatever, it'd look something like

DECLARE @orderCount int = 0;
EXECUTE dbo.TestStoredProcSSVariable @orderCount OUTPUT;
SELECT @orderCount As OrderCountVariable;

It is valid to run the above without specifying OUTPUT but look at the value of @orderCount. It changes from 1435 to 0.

The same holds true when you're using the Execute SQL Task within SSIS. You must specify that the parameter is on OUTPUT and also specify it in the Parameter mappings tab.

Also specify the variable you want to map and use the OUTPUT direction there. Here I've mapped the result into an SSIS Variable of type Int32 called orderCount

Single Result Set

You have the first part of this correct - you've specified that the result set is Single Row.

You'll note that I am using EXECUTE dbo.TestStoredProcSSVariable ? as you must specify an input value or the proc call will break (at least as you've defined it). You could have hard coded a value in lieu of the ? like 0

Then, on the Result Set tab, here I'm mapping up the first column (zeroth ordinal) to a Variable called orderCountb

If you run the provided stored procedure, you will not get a value in orderCountb. Why? Because you aren't returning anything from the stored procedure call. I added a final statement inside the stored procedure of

SELECT @OrderCount AS OrderCount;

Do it yourself

You can explore either approach using the following biml. What is biml? The Business Intelligence Markup Language is the operating system for BI. Why you care about it is that it will allow you to transform some XML into an SSIS package. All you need to do is download and installed the free addon BIDS Helper

After installing BIDS Helper,

Right click on the project and select Add new Biml file
replace the contents of the file with the following XML
Fix the values in line 5. Update the Data Source to a real server and Provider to align with your SSIS version. Looking at your screenshot, this will likely be SQLNCLI10.1
Right click on BimlScript.biml and choose Generate SSIS Packages

Bimlscript.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection
            Name="tempdb"
            ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
            />
    </Connections>
    <Packages>
        <Package
            Name="dba_114775"
            ConstraintMode="Linear"
        >
            <Tasks>
                <ExecuteSQL
                    ConnectionName="tempdb"
                    Name="SQL Make procedure">
                    <DirectInput>
                        <![CDATA[IF EXISTS
(
    SELECT
        *
    FROM
        sys.procedures AS P 
        INNER JOIN 
            sys.schemas AS S
            ON S.schema_id = P.schema_id
    WHERE
        S.name = 'dbo'
        AND P.name = 'TestStoredProcSSVariable'
)
BEGIN
    DROP PROCEDURE dbo.TestStoredProcSSVariable
END
GO
CREATE PROCEDURE dbo.TestStoredProcSSVariable
(
    @OrderCount int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    SET @OrderCount = 1135;
    SELECT @OrderCount AS OrderCount;
END

GO

]]>
                    </DirectInput>

                </ExecuteSQL>
                <Container Name="SEQC Result set" ConstraintMode="Linear">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Make procedure.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                    <Tasks>
                        <ExecuteSQL
                              ConnectionName="tempdb"
                              ResultSet="SingleRow"
                              Name="SQL SingleRow">
                            <DirectInput>EXECUTE dbo.TestStoredProcSSVariable ?;</DirectInput>
                            <Results>
                                <Result VariableName="User.orderCountb" Name="0" />
                            </Results>
                            <Parameters>
                                <Parameter DataType="Int32" VariableName="User.orderCountb" Name="0" />
                            </Parameters>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
                            <DirectInput>SELECT 1;</DirectInput>
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input 
                                        OutputPathName="SQL Placeholder.Output" 
                                        EvaluationOperation="ExpressionAndConstraint" 
                                        EvaluationValue="Success" 
                                        Expression="@[orderCount] &lt; 5" />
                                </Inputs>
                            </PrecedenceConstraints>
                        </ExecuteSQL>
                    </Tasks>
                </Container>
                <Container Name="SEQC Output Parameter" ConstraintMode="Linear">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SQL Make procedure.Output"></Input>
                        </Inputs>
                    </PrecedenceConstraints>
                    <Tasks>
                        <ExecuteSQL
                              ConnectionName="tempdb"
                              Name="SQL Output parameter">
                            <DirectInput>EXECUTE dbo.TestStoredProcSSVariable ? OUTPUT;</DirectInput>
                            <Parameters>
                                <Parameter 
                                    DataType="Int32" 
                                    VariableName="User.orderCount" 
                                    Name="0" 
                                    Direction="Output" />
                            </Parameters>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                        <ExecuteSQL ConnectionName="tempdb" Name="Execute SQL Task 2">
                            <DirectInput>SELECT 1;</DirectInput>
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input 
                                        OutputPathName="SQL Placeholder.Output" 
                                        EvaluationOperation="ExpressionAndConstraint" 
                                        EvaluationValue="Success" 
                                        Expression="@[orderCount] &lt; 5" />
                                </Inputs>
                            </PrecedenceConstraints>
                        </ExecuteSQL>

                    </Tasks>
                </Container>
                <ExecuteSQL ConnectionName="tempdb" Name="SQL Placeholder">
                    <DirectInput>SELECT 1;</DirectInput>
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="SEQC Result set.Output" />
                            <Input OutputPathName="SEQC Output Parameter.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </ExecuteSQL>
            </Tasks>
            <Variables>
                <Variable DataType="Int32" Name="orderCount">-1</Variable>
                <Variable DataType="Int32" Name="orderCountb">-1</Variable>
            </Variables>
        </Package>
    </Packages>
</Biml>

Enjoy the following SSIS package

SQL Server – INSERT INTO Inserting Multiple Times

You are doing a cartesian product here:

...
FROM dbo.[DataSource], @ids i

you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it:

INSERT INTO dbo.[Users]
        ( 
          first_name,
          surname ,
          address_id            
        )

SELECT  first_name, surname, Address.address_id,        
FROM dbo.[DataSource]
INNER JOIN dbo.Address ON Address.address = DataSource.Address

A further consideration; this will work well as long as you don't have two users with the same address, or if it is desirable that two users with the same address have the same address_id.

Best Answer

Related Solutions

SSIS Variable – Assigning Stored Procedure Results

OUTPUT parameter

Single Result Set

Do it yourself

SQL Server – INSERT INTO Inserting Multiple Times

Related Question