Signup as a Tutor

As a tutor you can connect with more than a million students and grow your network.

SQL SERVER: How To Pass Parameters To The Stored Procedure?

Amitava Majumder
18/07/2017 0 0

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.
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 [dbo].[ShippedOrder] @salesid VARCHAR(50),@custid VARCHAR(50)
SELECT a.[SalesOrderID], a.CustomerID, c.[Name],b.[OrderQty],a.[ShipDate]
FROM [SalesLT].[SalesOrderHeader] a INNER JOIN [SalesLT].[SalesOrderDetail] b
ON a.[SalesOrderID] = b.[SalesOrderID]
INNER JOIN [SalesLT].[Product] c ON b.ProductID = c.ProductID and a.[SalesOrderID]=@salesid and a.CustomerID=@custid
Now let us see two different methods to call SP.

Method 1:
Let us run following statements, where the order of the parameters is a different order.

-- Execute SP
EXEC [AdventureWorksLT2012].[dbo].ShippedOrder 71780,30113
EXEC [AdventureWorksLT2012].[dbo].ShippedOrder 30113,71780

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.In the first statement it will show the result but in second statement no output will come.

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 AdventureWorksLT2012.dbo.ShippedOrder @salesid = 71780, @custid = 30113
EXEC AdventureWorksLT2012.dbo.ShippedOrder @custid = 30113,@salesid = 71780

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.

0 Dislike
Follow 0

Please Enter a comment


Other Lessons for You

Write A Query To Get Nth Highest Salary
WITH CTE AS ( SELECT EmpID, EmpName, EmpSalary, RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC) FROM dbo.Salary ) SELECT EmpID, EmpName, EmpSalary FROM CTE WHERE RN = @NthRowUse of...
Union Based SQL Injection | DVWA (Legal)
Union Based Injection:Technology: phpDatabase: MysqlThe main objective of this injection is to access database, of the website, by just given some malicious sql inputs in front end and get an access of...

Palvinder Singh | 11 Jan

0 0
VbScript Dictionary Objects
Dictionary objects are key value pairs object.One of the use of dictionary can be to find unique elements in a given set of data. Below is the example of the methods that can be used in dictionary: Set...

Praful | 19/12/2017

0 0
What Is Power Query?
Power Query is an Excel add-in that can be used for data discovery, reshaping the data and combining data coming from different sources. Power Query is one of the Excel add-ins provided as part of Microsoft...
PowerPivot For Excel
PowerPivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous...

Looking for MS SQL Training?

Find best MS SQL Training in your locality on UrbanPro.

Do you offer MS SQL Training?

Create Free Profile »

Find Best MS SQL Training?

Find Now »