SQL SERVER – How to Pass Parameters to the Stored Procedure?

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.

SQL SERVER - How to Pass Parameters to the Stored Procedure? paramsp

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.

Solarwinds

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.

SQL SERVER - How to Pass Parameters to the Stored Procedure? sp1

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.

SQL SERVER - How to Pass Parameters to the Stored Procedure? sp2

Let us cleanup by dropping this SP.

-- Clean up
DROP PROCEDURE TestParams
GO

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Running CHECKDB with Minimum Repair Level
Next Post
SQL SERVER – How to See Scripts Executing in sp_executesql?

Related Posts

3 Comments. Leave new

  • Hi Dave,

    Thank you for this post! Quick question, is it possible to execute a stored procedure using Sql Command Parameters? For an example

    string query = “exec myStoredProcedure @tokenGuid = @usertoken”
    sqlcommand sqlcmd = new sqlCommand(query)
    sqlcmd.AddWithValue(@usertoken, token)
    sqlcmd.Execute();

    I’m trying to protect against SQL Injection, so I read that I should use a paramterized SQL Query. But I’m having trouble calling the stored procedure this way. I get an error of ‘Must declare the scalar variable “@userToken”

    Reply
  • Try putting the variable in double quotes:

    sqlcmd.AddWithValue(“@usertoken”, token)

    Reply
  • thanks for your help ……….Im using pivot table how can i make a parameter for searing by name and between date and date with vb.net

    Reply

Leave a Reply

Menu