Skip to main content

Good guide on getting output from a stored procedure

https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

The sp itself..

USE AdventureWorks2008R2;
GO
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
    DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS  

    SET NOCOUNT ON;
    SELECT @SalesYTD = SalesYTD
    FROM Sales.SalesPerson AS sp
    JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
    WHERE LastName = @SalesPerson;
RETURN
GO

the call to get the value 



-- Declare the variable to receive the output value of the procedure.
DECLARE @SalesYTDBySalesPerson money;
-- Execute the procedure specifying a last name for the input parameter
-- and saving the output value in the variable @SalesYTDBySalesPerson
EXECUTE Sales.uspGetEmployeeSalesYTD
    N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
-- Display the value returned by the procedure.
PRINT 'Year-to-date sales for this employee is ' + 
    convert(varchar(10),@SalesYTDBySalesPerson);
GO

Comments

Popular posts from this blog

NMAP

It's been about 15 years since I used NMAP, I forgot how useful the tool is. To find out what services are running on an IP address simply use something like nmap -n -v -p1-10000 x.x.x.x -n tells nmap not to do name resolution (saves about 13 seconds if the IP you're scanning isn't in DNS_ -v verbose -p1-10000 says scan ports 1 to 10,000 (otherwise nmap will only scan the first 1000 ports)