Better Practice, Return Value or Selected value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Better Practice, Return Value or Selected value?

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-09-28 : 19:16:45

What is a better practice when you are returning the Scope_Identity() from a procedure?

should you use

return Scope_Identity()

or

Select Scope_Identity()

?

The reason I do not want to use return Scope_Identity(), is due to .net makes it more and more difficult to retrieve when working with datasets. The downside of using a select insetead of a return is if you decide to use an executescalar against a procedure, because it will return the wrong results when there are multiple sub procedures that ran.

If mystoredproc1 returns a integer using

return scope_Identity()

Then a .net execute scalar will return the correct results, but if mystoredproc1 returns a integer using

select Scope_Identity() as MyLookupVal

Then it will not (See code below) Assume mystoredproc1 @MyVarID returns the scope_Identity() using Select Scope_Identity instead of Return Scope_Identity()



create proc test
@MyvarID int
as
declare @Mylookupval int 
exec @MyLookupval = mystoredproc1 @MyVarID
exec MystoredProc2 @MyLookupval
go

To fix I need to supress the results of the first procedure so .net does not return it when execute scalar is ran.



create proc test
@MyvarID int
as
declare @Mylookupval int 
Declare @TmpTable(Mylookupval int)

insert into @TmpTable
exec  mystoredproc1 @MyVarID

set @Mylookupval = (Select * mylookupval from @TmpTable)


exec MystoredProc2 @MyLookupval

What's a better practice for sql programming when returning scope_Identity from a procedure? Should I use a select and deal with it the way I am, or should I use a return and just do the grunt work in dot net so it can accomidate?

Or possible any other sugestions?

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 00:20:09

nope. its better to use a output parameter in your procedure and then return value of scope_identity through that. Only if you want the return value you can make an output parameter in .net and return the value through it. see second method in below link:

http://www.sqlteam.com/article/stored-procedures-returning-data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources