Using Parameters with Stored Procedures
-
The problem I find with developers creating stored procedures is the need to have database owner authority. I promote the stored procedures to production but they have full control in development.
I wish SQL Server had an authority just for Stored Procedure development.
David Bird
-
The article is basic and good.
Regarding the VB.NET code posted, I would recommend opening the connection as late as possible and close as early as you can, than have the open statement at the beginning.
and you also forgot to close the connection.
so it would be:
conn.Open()
'execute the query
myCommand.ExecuteNonQuery()
conn.close()
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
I think that two aspect of stored procedure are very important:
Optimization, after you run your stored procedure for the first time it is optimized and next time it works faster because server (MS SQL) store execution plan and use it at next attempt to run procedure. So if you have a very complicated sql query which access multiple tables when if you put your query in stored procedure it will return data faster.
Security if you get data from Web page and for example you create sql query based on them if you pass data from customer as parameter to stored procedure it will automatically prevent some code injection attack because SQL server will test data against expected format.
For this .Net code example I would even put this part with connection in try catch finally block or only try finally to be sure that connection is closed and disposed after we done with our code like:
Try
conn.open()
........'your data processing
Finally
conn.close()
conn.dispose
End Try
-
Good advice!
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
... I love stuff like this. Maybe you should submit it to the script library! ...
Done
VB.Net support from your SQLServer DBA
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the article
-
This is a very good article for starters like me.
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks, guys! This has been my most popular article. Glad that I have been able to help lots of people.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
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.
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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
-
Great idea!
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
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
[font="Arial Black"]
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-2 int, @C varchar(30)
Exec usp_AddTwoIntegers @a, @b-2, @C OUTPUT
Select @C[/font]
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply