I have a few years program experience but I still do not understand how to
use "output parameter" in program. For example, in ASP.NET.
Can some experts help me?
1) Why need output parameters?
2) Whe to use it?
3) How to use it?

--
Message posted via http://www.sqlmonster.com

Output parameters can help in a number of ways. For the most part, they're
more efficient than returning a row containing values--the overhead in the
TDS stream is far lower and they give the developer more flexibility when
returning data to the client. Returning a dozen OUTPUT parameters is far
faster than returning a single row via a SELECT. Generally, OUTPUT
parameters are used whenever you want to return one or more values that are
not in a rowset like computed or @@ global values. In your SP you set the
OUTPUT argument on the parameter declaration and use a SELECT or SET
statement to set the value. On the client end, you create a Parameter object
whose direction is set to output. Remember that the OUTPUT parameter packets
are returned after any rowsets are returned.

hth

--
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
__________________________________________________ __________________________________________



"aspfun via SQLMonster.com" <u53138@uwe> wrote

In SQL Server 2000 I used to return the error code from within a stored
procedure or if you have a nested stored procedures wrapped in begin
tran.... commit/rollbak then you can get the status of the error and
process accordingly

CREATE PROCEDURE BigOne
AS
DECLARE @err integer
BEGIN TRANSACTION
EXEC @err = sp1
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp2
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp3
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @err = sp4
SELECT @err = coalesce(nullif(@err, 0, @@error)
IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO

In stored procedure as well as sp_executesql has an output parameter

USE pubs

DECLARE @RowCount int

EXEC sp_executesql
N'SELECT @RowCount = COUNT(*) FROM authors',
N'@RowCount int OUTPUT',
@RowCount OUTPUT

RAISERROR ('Authors rowcount is %d', 0, 1, @RowCount)




See above :-) and also read the BOL (Books On Line)




"aspfun via SQLMonster.com" <u53138@uwe> wrote