SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Parameters with Stored Procedures


Using Parameters with Stored Procedures

Author
Message
Patrick-304111
Patrick-304111
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 81
Thanks for the article
Bidyut Bhattacharjee
Bidyut Bhattacharjee
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
This is a very good article for starters like me.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504557 Visits: 44233
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kathi Kellenberger
Kathi Kellenberger
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5444 Visits: 358
Thanks, guys! This has been my most popular article. Glad that I have been able to help lots of people.

Aunt KathiLinchpin People TeammateSQL Server MVPAuthor of Expert T-SQL Window Functions
George H.
George H.
Right there with Babe
Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)Right there with Babe (764 reputation)

Group: General Forum Members
Points: 764 Visits: 118
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220468 Visits: 33546
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GSquared
GSquared
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138295 Visits: 9731
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
Kathi Kellenberger
Kathi Kellenberger
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5444 Visits: 358
Great idea!

Aunt KathiLinchpin People TeammateSQL Server MVPAuthor of Expert T-SQL Window Functions
praveen-435766
praveen-435766
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search