July 24, 2008 at 3:24 am
I would like to know if there is any performance gain b/w following to different style of code:
Sample #1.
========
Create Proc usp_Test1 @Col int, @Col2 varchar (500) OUTPUT, @Col2 varchar(500) OUTPUT
AS
select @Col1 = Col1, @Col2 = Col2 from Table Where Col = @InputParameter
Sample #2.
========
Create Proc usp_Test2 @Col int
AS
BEGIN
SET NOCOUNT ON
select Col1, Col2 from Table Where Col = @InputParameter
SET NOCOUNT OFF
END
1- Which one would be best wrt Performance and how?
2- Incase, if select is returning more than 1 row how we this situation will work, and in this case what you suggest?
Shamshad Ali.
July 24, 2008 at 3:29 pm
The main difference is that the output parameters will only get the last value passed to them, if you have more than one row that fits the Where clause, while the select will get you all rows.
The method for calling the proc changes a bit too, of course.
Performance-wise, they should be just about identical.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 10:44 pm
I haven't run them under load, but I tend to view these as Gsquared has mentioned.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply