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 Monday, July 25, 2005 6:53 PM


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: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/usingparameterswithstoredprocedures.asp

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #204273
Posted Sunday, August 7, 2005 10:50 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 21, 2010 11:12 PM
Points: 13, 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

Post #208229
Posted Monday, August 8, 2005 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 1:44 AM
Points: 15, Visits: 464

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
Post #208253
Posted Monday, August 8, 2005 8:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 7:18 AM
Points: 284, Visits: 19

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.




Post #208364
Posted Monday, August 8, 2005 8:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031

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
Post #208368
Posted Monday, August 8, 2005 8:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 7:18 AM
Points: 284, Visits: 19
Maybe we've been dealing with the wrong vendors.


Post #208369
Posted Monday, August 8, 2005 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 7, 2009 4:50 PM
Points: 23, 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
Post #208373
Posted Monday, August 8, 2005 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 4:30 PM
Points: 13, Visits: 198
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
Post #208387
Posted Tuesday, August 9, 2005 9:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #208768
Posted Wednesday, August 10, 2005 8:17 PM


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: Thursday, November 13, 2014 9:28 AM
Points: 769, Visits: 257

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
Microsoft
(Former SQL Server MVP)
Post #209373
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse