The
ProgrammingMSAccess.COM Site
A Transact-sql script
to create a stored procedure that uses input and output parameters as well as
return a status value. this t-sql script starts by dropping the stored proc if
it existed previously, then it creates the new stored procedure. A
follow-up script shows how to recover an output parameter value and a return
status value when you invoke the stored proc.
--Create upDis_ContinuedProducts stored proc
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'upDis_ContinuedProducts'
AND type = 'P')
DROP PROCEDURE upDis_ContinuedProducts
GO
CREATE PROC upDis_ContinuedProducts
@Dis_Con bit = 1,
@CategoryID int,
@ProductCount int OUTPUT
AS
SELECT nc.CategoryID, nc.CategoryName,
np.ProductName
FROM Northwind.dbo.Categories AS nc,
Northwind.dbo.Products AS np
WHERE nc.CategoryID = np.CategoryID AND
np.Discontinued = @Dis_Con AND
np.CategoryID = @CategoryID
SET @ProductCount = @@ROWCOUNT
RETURN (SELECT COUNT(*)
FROM Northwind.dbo.Products
WHERE CategoryID = @CategoryID)
GO
--Test upDis_ContinuedProducts stored proc with non-default value
--for @Dis_Con parameter
SET NOCOUNT ON
DECLARE @NotCarried bit
DECLARE @NumberDis_Continued int
DECLARE @NumberOfProducts int
DECLARE @Message nvarchar(60)
SET @NotCarried = 0
IF @NotCarried = 0
SET @Message = ' continued products out of '
ELSE
SET @Message = ' discontinued products out of '
EXEC @NumberOfProducts = upDis_ContinuedProducts @NotCarried,
@CategoryID = 6, @ProductCount = @NumberDis_Continued OUTPUT
PRINT RTRIM(CONVERT(char(2), @NumberDis_Continued)) +
@Message + RTRIM(CONVERT(char(2), @NumberOfProducts)) +
' products.'
SET NOCOUNT OFF
Want to understand SQL Server so that you can program it to
do more tasks like this? Get Programming Microsoft SQL Server 2000
with Microsoft Visual Basic .NET by Rick Dobson from Microsoft Press. Learn more about the book by clicking
here.
Copyright 2004 CAB, Inc. All rights reserved. Republication or redistribution
of CAB, Inc. content, including by framing or similar means, is expressly
prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be
liable for any errors in the content, or for any actions taken in reliance
thereon.