Output parameters or recordsets to deal with app? - 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 2000 Forums

SQL Server Development (2000)

Output parameters or recordsets to deal with app?

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-04 : 06:41:58

I have an ASP.NET VB.NET web application, and recently some database developers told me that use output parameters in the Stored Procedures inside Sql Server in order to pass and receive data with the application is a bad practice, and it is much more better use simply ‘Select’ statements. But the very best ASP.NET developers says that output parameters is the best efficient way in all cases to pass and receive data with the DB. And also they say that when we use ‘Select’ statements to send data to the application, the result set received by the app has more than the data: all sorts of metadata about what is being returned.

So, my question is: About Database and Stored Procedures performance, which is the problem or effect of use output parameters in the SP inside Sql server to deal with application in the data traffic?

Thank you,
Cesar

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-04 : 06:45:35

it is better to encapsulate the query statements in stored procedures

1. secured - if properly designed, sql injections can be minimized if not eliminated
- users can be restricted as per SP purpose
2. execution plans are taken advantage of
3. debugging becomes simpler compared to native queries passed

--------------------
keeping it simple...

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-04 : 06:56:36

No, I already use SP inside Sql Server. I mean, What is the problem to use ouput parameters instead of recordsets?

Output parameters:

Use market
GO
ALTER PROCEDURE offer_detail
@Offer_num bigint, @Reference varchar(50) output, 
@Company_State varchar(50) output, @Company_City varchar(100) output, 
@Company_name varchar(100) output, @Web_page varchar(100) output,
@Activity_description varchar(500) output, 
...

As

SET NOCOUNT ON 
Select @Reference = Reference, 
@Company_State = State_name,
@Company_City = City_name,
@Company_name = Company_name, @Web_page = Web_page, 
@Activity_description = Activity_description,
...

From Offers
Where Offer_id = @Offer_num

SET NOCOUNT OFF 
GO

Record set:

Use market
GO
ALTER PROCEDURE offer_detail
@Offer_num bigint
As

SET NOCOUNT ON 
Select Reference, State_name, City_name, Company_name, Web_page, 
Activity_description,
...

From Offers
Where Offer_id = @Offer_num

SET NOCOUNT OFF 
GO

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-04 : 07:22:29

The Output parameter method is only valid for single row rowsets.

There is more overhead in returning a "recordset", but Moore's Law continues unabated...

DavidM

A front-end is something that tries to violate a back-end.

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-04 : 10:29:26

Ok, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources