STORED PROCEDURE

[crazydeveloper]

HI FRIENDS,

I am new to SQL server.

pl. help to finding the solution, i am trying to declare a variable at execution of the Store procedure i.e.
ex: i want to declare variables depends on the no. of records fetched from the recordset.

if no. of records in recordset 10 then i want to declare 10 variables.


thanks in advance.

bye

 

[anitas]

Hello friend hope U find this example helpful as U 're new to sql server. Based
on this U can find a solution to U'r problem.

USE Northwind
GO
DROP PROCEDURE OrderSummary
GO

CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS

-- SELECT to return a result set summarizing
-- employee sales.

SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.

SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]

-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO

-- Test the stored procedure.
-- DECLARE variables to hold the return code
-- and output parameter.
DECLARE @OrderSum INT
DECLARE @LargestOrder INT

-- Execute the procedure, which returns
-- the result set from the first SELECT.

EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT

-- Use the return code and output parameter.
PRINT 'The size of the largest single order was: ' +
CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
CONVERT(CHAR(6), @OrderSum)
GO

The output from running this sample is:
EmployeeID SummSales
----------- --------------------------

1 202,143.71

2 177,749.26

3 213,051.30

4 250,187.45

5 75,567.75

6 78,198.10

7 141,295.99

8 133,301.03

9 82,964.00

The size of the largest single order was: 130

The sum of the quantities ordered was: 51317

anitas