[ Join Now (FREE) | Log In]

How to create/execute a stored procedure in a SQL Server Database

by Jagadish Pulakhandam on 9/23/2011 8:17:56 AM
Rated 0 from 0 votes
Brief: Demonstrates on creating a new stored procedure in SQL Server Database and test the same by executing it.
Posted to: Microsoft SQL Server - Quick Learn ("How to"s for Developers)
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Attached video demonstrates the following:
  • Create a stored procedure (with a single parameter) from the scratch using SSMS.  You can also have stored procedures with no/multiple parameters
  • Execute the newly created stored procedure using SSMS (this generates code to execute a stored procedure automatically)
  • Modify the generated code (to execute stored procedure) and re-execute the same (multiple number of times).
Following is the sample stored procedure developed for this demo:

01.CREATE PROCEDURE [dbo].[uspEmpSalesByCountry]
02.(
03.    @EmployeeID int
04.)
05.AS
06.BEGIN
07.    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmpSales]') AND type in (N'U'))
08.    BEGIN
09.        DROP TABLE EmpSales;
10.    END
11.     
12.    SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
13.    INTO EmpSales
14.    FROM Employees INNER JOIN
15.        (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
16.        ON Employees.EmployeeID = Orders.EmployeeID
17.    WHERE Employees.EmployeeID = @EmployeeID
18.END

Following is the script used to test the above:

01.USE [Northwind]
02.GO
03. 
04.DECLARE @return_value int
05. 
06.EXEC    @return_value = [dbo].[uspEmpSalesByCountry]
07.        @EmployeeID = 3
08. 
09.SELECT  'Return Value' = @return_value



Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout


Video/Screen Recording (may not have audio narration/annotations)
You need to Login or Join for FREE to download the following