|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why Output"Output" parameters? In the example from the book: *************************************************************** Create Procedure GetLastName ( @firstname varchar(20), @lastname varchar(20) Output ) As Select @lastname = au_lname from Authors Where au_fname = @firstname ******************************************************************* Wouldn't this work just as well? *************************************************************** Create Procedure GetLastName ( @firstname varchar(20)) As Select au_lname from Authors Where au_fname = @firstname ******************************************************************* This is the way I usually do it. Just curious. Thanks, Tom Consider the following scenario:
CREATE PROCEDURE GetLastName ( @firstname VARCHAR(20), @lastname VARCHAR(20), @time_elapsed INTEGER OUTPUT ) AS DECLARE @starttime DATETIME SET @starttime = CURRENT_TIMESTAMP SELECT au_lname FROM Authors WHERE au_fname = @firstname SET @time_elapsed = DATEDIFF(ms, @starttime, CURRENT_TIMESTAMP) In this scenario it doesn't make sense to return the scalar value (total time elapsed) with each row of the result set. We return it using an OUTPUT parameter instead of a result set, since there is only one possible return value for each invocation of the SP. Show quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23d%23QF5wYFHA.612@TK2MSFTNGP12.phx.gbl... >I as looking at a book about asp.net and was curious, why do we need >"Output" parameters? > > In the example from the book: > *************************************************************** > Create Procedure GetLastName > ( > @firstname varchar(20), > @lastname varchar(20) Output > ) > As > > Select @lastname = au_lname from Authors Where au_fname = @firstname > ******************************************************************* > > Wouldn't this work just as well? > > *************************************************************** > Create Procedure GetLastName > ( > @firstname varchar(20)) > As > > Select au_lname from Authors Where au_fname = @firstname > ******************************************************************* > > This is the way I usually do it. > > Just curious. > > Thanks, > > Tom >
Show quote
Hide quote
"Michael C#" <x**@abcdef.com> wrote in message But you could use the Return Value for that, couldn't you?news:hbOle.26482$NZ1.814@fe09.lga... > Consider the following scenario: > > CREATE PROCEDURE GetLastName > ( > @firstname VARCHAR(20), > @lastname VARCHAR(20), > @time_elapsed INTEGER OUTPUT > ) > AS > > DECLARE @starttime DATETIME > SET @starttime = CURRENT_TIMESTAMP > SELECT au_lname FROM Authors WHERE au_fname = @firstname > SET @time_elapsed = DATEDIFF(ms, @starttime, CURRENT_TIMESTAMP) > > In this scenario it doesn't make sense to return the scalar value (total > time elapsed) with each row of the result set. We return it using an > OUTPUT parameter instead of a result set, since there is only one possible > return value for each invocation of the SP. But I can see your point. But in my case, where you are returning only one value - either way would be the same? Thanks, Tom Show quoteHide quote > > "tshad" <tscheider***@ftsolutions.com> wrote in message > news:%23d%23QF5wYFHA.612@TK2MSFTNGP12.phx.gbl... >>I as looking at a book about asp.net and was curious, why do we need >>"Output" parameters? >> >> In the example from the book: >> *************************************************************** >> Create Procedure GetLastName >> ( >> @firstname varchar(20), >> @lastname varchar(20) Output >> ) >> As >> >> Select @lastname = au_lname from Authors Where au_fname = @firstname >> ******************************************************************* >> >> Wouldn't this work just as well? >> >> *************************************************************** >> Create Procedure GetLastName >> ( >> @firstname varchar(20)) >> As >> >> Select au_lname from Authors Where au_fname = @firstname >> ******************************************************************* >> >> This is the way I usually do it. >> >> Just curious. >> >> Thanks, >> >> Tom >> > > Standard practice is to return ERROR codes as the return value. I.e.,
CREATE PROCEDURE GetLastName ( @firstname VARCHAR(20), @lastname VARCHAR(20), @time_elapsed INTEGER OUTPUT ) AS DECLARE @starttime DATETIME SET @starttime = CURRENT_TIMESTAMP SELECT au_lname FROM Authors WHERE au_fname = @firstname SET @time_elapsed = DATEDIFF(ms, @starttime, CURRENT_TIMESTAMP) RETURN @@error In your case, returning only one value, either would work. However, there might be additional overhead - from a programming standpoint, and from a SQL standpoint - involved with looping through a result set as opposed to just getting an OUTPUT PARAMETER value. Show quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:Of8zCXxYFHA.4036@tk2msftngp13.phx.gbl... > "Michael C#" <x**@abcdef.com> wrote in message > news:hbOle.26482$NZ1.814@fe09.lga... >> Consider the following scenario: >> >> CREATE PROCEDURE GetLastName >> ( >> @firstname VARCHAR(20), >> @lastname VARCHAR(20), >> @time_elapsed INTEGER OUTPUT >> ) >> AS >> >> DECLARE @starttime DATETIME >> SET @starttime = CURRENT_TIMESTAMP >> SELECT au_lname FROM Authors WHERE au_fname = @firstname >> SET @time_elapsed = DATEDIFF(ms, @starttime, CURRENT_TIMESTAMP) >> >> In this scenario it doesn't make sense to return the scalar value (total >> time elapsed) with each row of the result set. We return it using an >> OUTPUT parameter instead of a result set, since there is only one >> possible return value for each invocation of the SP. > > But you could use the Return Value for that, couldn't you? > > But I can see your point. > > But in my case, where you are returning only one value - either way would > be the same? > > Thanks, > > Tom >> >> "tshad" <tscheider***@ftsolutions.com> wrote in message >> news:%23d%23QF5wYFHA.612@TK2MSFTNGP12.phx.gbl... >>>I as looking at a book about asp.net and was curious, why do we need >>>"Output" parameters? >>> >>> In the example from the book: >>> *************************************************************** >>> Create Procedure GetLastName >>> ( >>> @firstname varchar(20), >>> @lastname varchar(20) Output >>> ) >>> As >>> >>> Select @lastname = au_lname from Authors Where au_fname = @firstname >>> ******************************************************************* >>> >>> Wouldn't this work just as well? >>> >>> *************************************************************** >>> Create Procedure GetLastName >>> ( >>> @firstname varchar(20)) >>> As >>> >>> Select au_lname from Authors Where au_fname = @firstname >>> ******************************************************************* >>> >>> This is the way I usually do it. >>> >>> Just curious. >>> >>> Thanks, >>> >>> Tom >>> >> >> > > There are a handful of reasons.
1. It is the only way to pass data directly back to a calling stored proc (meaning not using tables as an intermediary) 2. It allows for multiple return values. 3. It allows for return values that are not integers 4. It is faster than returning an entire recordset (but you can only return singleton values). Thomas Show quoteHide quote "tshad" <tscheider***@ftsolutions.com> wrote in message news:%23d%23QF5wYFHA.612@TK2MSFTNGP12.phx.gbl... >I as looking at a book about asp.net and was curious, why do we need "Output" >parameters? > > In the example from the book: > *************************************************************** > Create Procedure GetLastName > ( > @firstname varchar(20), > @lastname varchar(20) Output > ) > As > > Select @lastname = au_lname from Authors Where au_fname = @firstname > ******************************************************************* > > Wouldn't this work just as well? > > *************************************************************** > Create Procedure GetLastName > ( > @firstname varchar(20)) > As > > Select au_lname from Authors Where au_fname = @firstname > ******************************************************************* > > This is the way I usually do it. > > Just curious. > > Thanks, > > Tom >  
Other interesting topics
Return always 0.
Hoping not everyone is gone the Friday before Memorial Day! Implementing natural keys Recursive rows in table and deletion problem Centralizing Two Stored Procedure Queries (one using Cursor Output Newbie: UPDATE a Table Drop all indexes partitioned view goofiness how to aduit database access? "Insufficient key column information for updating and refreshing" |
|||||||||||||||||||||||
