converting another SP to use output params

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

Transact-SQL (2000)

converting another SP to use output params

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-05 : 16:07:18

I have a stored procedure that returns 1 row. I am converting it to use output parameters since I am running it on asp.net, and am under the impression I will achive better performance. The lines I am confused are in bold, can anybody give me a hand here?

Thanks alot!

Mike123

CREATE Procedure select_Profile2_OUTPUT

(
@userID_IN int,
@active tinyint OUTPUT,
@age tinyint OUTPUT,
@city varchar(25) OUTPUT,
@countryID tinyint OUTPUT,
@date datetime OUTPUT,
@lastLoggedIn datetime OUTPUT,
@drinkID tinyint OUTPUT,
@genderID tinyint OUTPUT,
@heightFeet tinyint OUTPUT,
@heightInches tinyint OUTPUT,
@nameOnline varchar(15) OUTPUT,
@sexualityID tinyint OUTPUT,
@smokeID tinyint OUTPUT,
@stateProvID tinyint OUTPUT,
@statusID tinyint OUTPUT,
@userID int OUTPUT,
@votes int OUTPUT,
@points int OUTPUT,
@getRated tinyint OUTPUT,
@zeoExist tinyint OUTPUT,
@online tinyint OUTPUT

)
AS SET NOCOUNT ON

SELECT @active = active, @age = Age, @city = City, @countryID = CountryID, @date = Date, @lastLoggedIn = lastLoggedIn, @DrinkID = DrinkID, @genderID = GenderID, @heightFeet = HeightFeet, @heightInches = HeightInches, @nameOnline = NameOnline, @sexualityID = SexualityID, @smokeID = SmokeID, @stateProvID = StateProvID, @statusID = StatusID, @userID = UserID, @votes = Votes, @points = Points, @getRated = getRated,
case when exists (select userID from tblZeoCast where tblUserDetails.userid = tblZeoCast.userid AND active ='1' ) then
'Y'
else
'N'
end as ZeoExist,
case when exists (select userID from tblActive_Users WHERE tblUserDetails.userid = tblActive_Users.userID ) then
'Y'
else
'N'
end as online

FROM tblUserDetails WHERE userID = @userID_IN

GO

ojn.
Starting Member

10 Posts

Posted - 2003-03-05 : 16:40:46

@ZeoExist = case when exists (select userID from tblZeoCast where tblUserDetails.userid = tblZeoCast.userid AND active ='1' ) then
'Y'
else
'N'
end ,
@online = case when exists (select userID from tblActive_Users WHERE tblUserDetails.userid = tblActive_Users.userID ) then
1
else
0
end

--
-oj
www.rac4sql.net

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-03-05 : 16:47:38

perfect thansk!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources