Stored Proc Execute Assignment

You have two choices to make this work. Either you can use a Single Result Set or you can use the OUTPUT parameter. You're currently using neither correctly.

OUTPUT parameter

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

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

It is valid to run the above without specifying 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

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 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

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

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

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,

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

Bimlscript.biml

Enjoy the following SSIS package

answered Sep 11 '15 at 22:27

After so many years of existence of the stored procedures, I still see developers struggling to execute the stored procedure. Every other day I receive a question in an email asking how to pass parameters to the stored procedure.

Today, I decided to blog about it so next time when I receive the email, I can just link to it.

The most common mistakes developers do is to execute stored procedures as follows which generates the error.

EXEC NameofSP(FirstParam,SecondParam)

I can clearly understand why the above script preferred by developers. As most of the programming languages use the above syntax for calling a function, the developers often think it is the way to call stored procedures. However, if you try to call an SP with above syntax, it will give you an error.

There are two different methods to call stored procedures. Let us see them here, however before we do that, let us first create a sample SP which accepts two parameters.

Creating Stored Procedure

-- Create Stored Procedure CREATE PROCEDURE TestParams @FirstParam VARCHAR(50), @SecondParam VARCHAR(50) AS SELECT @FirstParam FirstParam, @SecondParam SecondParam GO

Now let us see two different methods to call SP.

Method 1:

Let us run following two statements, where the order of the parameters is a different order.

-- Execute SP EXEC TestParams 'First', 'Second' GO EXEC TestParams 'Second', 'First' GO

When you run above script, it will show us result where the order of the columns is changed as SP assigns the first params to the first parameters inside.

However, if you want to assign your passed parameters to specific variable inside SQL Server, you will have to mention that parameter as mentioned in the second method.

Method 2:

In this method when we execute the stored procedure, we also include the variable to which we want to assign parameter. This way is the certain way to assign value to parameter inside a SP.

-- Execute SP EXEC TestParams @FirstParam = 'First', @SecondParam = 'Second' GO EXEC TestParams @SecondParam = 'Second', @FirstParam = 'First' GO

Now when you execute this SP, you will get results in the same order in both the case as we have specified the parameters to assign when we passed variable values.

Let us cleanup by dropping this SP.

-- Clean up DROP PROCEDURE TestParams GO

Reference: Pinal Dave (https://blog.sqlauthority.com)




0 Replies to “Stored Proc Execute Assignment”

Lascia un Commento

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *