Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Using Parameters with Stored Procedures Expand / Collapse
Author
Message
Posted Wednesday, May 24, 2006 2:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:27 AM
Points: 22, Visits: 80
Thanks for the article
Post #282599
Posted Tuesday, January 8, 2008 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2008 2:13 AM
Points: 1, Visits: 11
This is a very good article for starters like me.
Post #440163
Posted Tuesday, January 8, 2008 7:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
I know it's an older article, but I gotta say, nicely done, Kathi! Great examples for folks that don't know.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #440390
Posted Wednesday, January 9, 2008 7:50 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244
Thanks, guys! This has been my most popular article. Glad that I have been able to help lots of people.

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #440668
Posted Friday, February 29, 2008 4:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 4, 2013 5:21 AM
Points: 52, Visits: 117
Kathi,

Great article.

One thing I wanted to add, which threw me for a loop a while back, is that if you use a return value when filling a datareader object that return value isn't available in vb.net code until after the datareader has iterated through all of its rows and closed. Which to me seems backwards. I always wanted to use the return value to make sure the SP didn't error-off and then run through my reader, but the return value isn't available until after I'd run through the reader. Weird.

George H.
Post #462255
Posted Friday, February 29, 2008 7:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 15,527, Visits: 27,908
Nice article Kathi. Good job.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #462357
Posted Friday, February 29, 2008 9:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Good article.

One thing I like to include in parameter names is the direction. For example, "@Date_in" would be an input parameter, "@Date_out" would be an output parameter, "@Date_both" would be both.

Variables declared in the body of the proc don't have either. (e.g.: "@Date")

It makes it easier when I'm debugging or refactoring a proc, to be able to tell at a glance, whether I'm looking at an input parameter, an output parameter, or an internal variable.


- 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
Post #462466
Posted Friday, February 29, 2008 9:02 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244
Great idea!

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #462469
Posted Thursday, August 21, 2008 10:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 22, 2009 3:49 AM
Points: 4, Visits: 23
The Logic given below has really finished my doubt which i was having in retrieving the output parameter in sql queries...
I knew it how to fetch the output from front end and actually itried like this in sql queries but i forgot that at the time of retrieval we give output identifier that's why i was making mistake in the retrieval ....


Thanx,


Regards Praveen




ALTER PROC usp_AddTwoIntegers
@FirstNumber int = 5,
@SecondNumber int,
@Answer varchar(30) OUTPUT
as
Set @Answer = 'The answer is ' + convert(varchar,@FirstNumber + @SecondNumber)




Declare @a int, @b int, @c varchar(30)
Select @a = 1, @b = 3
Exec usp_AddTwoIntegers @a, @b, @c OUTPUT
Select @c
Post #557057
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse