Find the best tutors and institutes for MS SQL Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Search for topics

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

0 Dislike
Follow 0

Please Enter a comment

Submit

Other Lessons for You

Importance of practical oriented IT training
Hello, Nowadays, there are enormous opportunities in IT field. But the essential thing about IT is that we should have the expertise and best skills of latest IT technologies to survive in IT industry.Moreover,...

Yess InfoTech | 27 Mar

0 0
0
The ABCs Of Success For A Software Developer
“There is more to know for career advancement rather than focusing on codes and its challenges to become a successful developer”. A developer is the one who creates software application by...
ASP.NET Syllabus
Part 1: Getting Started with ASP.NET Lesson 1: Overview Of Asp.Net Lesson 2: Adding Controls To A Web Page Lesson 3: Web Page Processing Lesson 4: Validating User Input Lesson...

Arvind Yadav | 05 Mar

1 0
0
MS-SQL Database
1. Select: 01-Select All Given a City table, whose fields are described as: +-------------+----------+ | Field | Type | +-------------+----------+ | ID | int(11) | | Name ...

Prashant Kumar | 27 Feb

0 0
0
The Best Option JavaScript Language For Developers
We already know about JavaScript. But today I will be discussing JavaScript features and JavaScript development process. I was wonder about JavaScript last 10 years. Because JavaScript can give us frontend...

Shibaji Debnath | 19 Feb

0 0
0

Find Best MS SQL Training?

Find Now »

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more