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
Kathi Kellenberger
Kathi Kellenberger
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1451 Visits: 343
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/usingparameterswithstoredprocedures.asp

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Capt Calamity
Capt Calamity
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 43

Nice, except starting off with an example using the RETURN to return the answer - something I've seen too many programmers use because they don't understand its reason for existence

Maybe you can add a paragraph on what it is (INT only), why it exists (return error / success information, similar to C - not VB where you can return any data type) and how to catch it in ADO (and maybe what to do with it!)?

And maybe link to some nice articles about error handling (why and best practice - or worst :-) )...




Regards
Andy Davies


Dewes van Lohuizen
Dewes van Lohuizen
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 472

It sounds clear to me.

But... I am looking for how to get the output from SP_spaceused in variables. Now I can use the output in a text file and I have seen on the internet how to catch it in a temptable. But what I really want is to get the output in variables with my own defined length. Is there anyone who can explain that to me?

Regards,

Dewes van Lohuizen



Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
Lee Dise
Lee Dise
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 21

From what I've been seeing in recent years, the industry trend is moving away from using stored procedures. This is not necessarily due to simple ignorance on how stored procedures work, but rather due to conditions in the business environment.

Software vendors have an incentive to appeal to as many prospective customers as they can, and many of them have made the decision to try to support multiple database platforms. One of our vendors, a well-known one, supports Oracle, SQL Server and (if I'm not mistaken) Informix, and they're hardly alone. This means they have an incentive to write their SQL as generically as they possibly can so that it will run effectively on whatever database platform the customer prefers. This means, among other things, eschewing stored procedures -- Oracle PL/SQL procedures are simply not interchangeble with T-SQL procedures, architecturally. For one thing, Oracle procedures don't return results sets. (At least they didn't do so four years ago, my last direct exposure to Oracle.)

Our experience with vendors that support multiple databases is that they like to stick to straight SQL, and to keep that very simple -- preferring to do any extensive data manipulation in another tier.





noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12316 Visits: 2048

Contrary to what you said I have worked with companies in the past that learned the HARD way that when the amount of non trivial queries or users become part on a DB systems if you don't use Stored procedures your system will CRAWL. When performance is necesary and the amount of data is "non trivial" stored procedures are the only way




* Noel
Lee Dise
Lee Dise
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 21
Maybe we've been dealing with the wrong vendors.



Brett Slaski
Brett Slaski
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 4
Plus if your stored procedures are ANSI compliant then your application will port to other databases quicker.

I always wondered why anyone would want to keep the integrety of a database in software code. Especially in this day and age of multiple apps using the same data. 4GL apps did this and look how locked in you are to them. To write anything out side of them is a fustrating adventure.

With environments like C#, which allow oop, general methods can be written which can call any stored prodecudure by just passing a parameter list. Why give up perfomance and integrety for code control?

Brett
Jim Collet
Jim Collet
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 203
good article but i think there is a typo in one example (it returns @sum , but should return @answer) i rarely use return except for maybe a 0 or 1: i would select instead.

Dewes, I don’t think anybody addressed your question and I didn’t see anything about 'post your question in the XXXXX forum', so here goes (simply).

SP_spaceused is a system stored procedure that returns 2 recordsets: you can access these as recordsets in VB Script just like you would any other recordset, but there are 2 so you will need to access the 2nd one with rs.nextrecordset:

got this from VB Help: paste it in a vbs file, point it to a server and db by changing the connection dtring and save and run:
set strCnn = createobject("adodb.connection")
strCnn.connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XX_DBNAME_XX;Data Source=XX_ServerName_XX"
strCnn.open

Set rstCompound = createobject("adodb.Recordset")
rstCompound.Open "exec SP_spaceused", strCnn, , , adCmdText

' Display results from each SELECT statement.
intcount = 1
Do Until rstCompound Is Nothing
msgbox "Contents of recordset #" & intCount
Do While Not rstCompound.EOF
msgbox rstCompound.Fields(0) & ", " & rstCompound.Fields(1)
rstCompound.MoveNext
Loop

Set rstCompound = rstCompound.NextRecordset
intCount = intCount + 1
Loop

--
you are going to have to modify the code to get the data how you want it.

another option might even be easier:
point to master in QA and paste 'sp_helptext SP_spaceused'. copy it then modify it to return 5 or 6 output params as describe in the article.

the 1st option you can do without modifying the database. you can create your own proc with the new sql and decide where to create and how to name it
Mike C
Mike C
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3633 Visits: 1168
Nice article, as always. I would just point out that when you use a default value for an input parameter, you can actually leave the parameter out of the call if you want.
Kathi Kellenberger
Kathi Kellenberger
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1451 Visits: 343

Thank you for all of your comments. With third-party apps, you usually don't have a choice about whether or not stored procs are used. My real intention for the article wasn't really to start a discussion about the pros and cons of their use, just to show how to use them. Anyway, my opinion is probably pretty obvious from the article.

Several of you made a good point about the return value. I was just really trying to show how to use it, but looking back I see that my example was poor. I'm not sure if it is possible to edit an article after it has been already posted. I'll shoot Steve an email about it and see what he says.

Thanks again for reading the article and your constructive comments. I just started writing this past winter and know I have a lot to learn!

Kathi



Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
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