Using Parameters with Stored Procedures
-
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 -
Comments posted to this topic are about the item Using Parameters with Stored Procedures
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
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 -
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.
-
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 -
Maybe we've been dealing with the wrong vendors.
-
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
-
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
-
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.
-
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 Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Once devs are pushed toward using parameters, they get confused as to why their sp is not reused and has many compilations when in use.
The reason is most of the time, that the parameters are defined with a wrong datatypemapping in the application.
I use this proc the generate the .Net-code to define and use the parameters for a commandobject named sqlcmd. A simple copy/paste helps out in this case.
It is primitive, and it has some flaws, but it helps out in 90 % ...
CREATE proc spc_ALZ_CreateSqlParameter
@ProcedureName sysname
as
begin
set nocount on
Select cast(' wrkCmdParam = New SqlClient.SqlParameter' as varchar(4000))
+ char(13) + ' With wrkCmdParam'
+ char(13) + ' .ParameterName = "' + Parameter_Name + '"'
+ char(13) + ' .SqlDbType = SqlDbType.' + Data_Type
+ char(13) + ' ''.DbType = DbType.String'
+ char(13) + ' ' + isnull('.Size = ' + cast(Character_Maximum_Length as varchar(15)) , '''.Size = ') + ''
+ char(13) + ' .Direction = ParameterDirection.' + case Parameter_Mode when 'IN' then 'Input' else '???' end
+ char(13) + ' End With'
+ char(13) + ' sqlcmd.Parameters.Add(wrkCmdParam) '
+ char(13) + ' '
--select *
FROM INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @ProcedureName
order by Ordinal_Position
print '---'
select ' sqlcmd.parameters("' + Parameter_Name + '").value = rij.Item("' + replace(Parameter_Name,'@','') + '")'
FROM INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @ProcedureName
order by Ordinal_Position
end
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
-
It's quite easy to build true, reusable command objects in VB. We do it this way (this is an update only command, so no return parameters:
Static cmdUpdate as adodb.command
If cmdUpdate is Nothing Then
Set cmdUpdate = New ADODB.Command
With cmdUpdate
.ActiveConnection = GL_Conn
.CommandText = "UpdateOrderItemOptions"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@ID", adGUID)
.Parameters.Append .CreateParameter("@CUpdated", adBoolean)
.Parameters.Append .CreateParameter("@VUpdated", adBoolean)
.Parameters.Append .CreateParameter("@Checked", adBoolean)
.Parameters.Append .CreateParameter("@UseDirect", adBoolean)
.Parameters.Append .CreateParameter("@DirectCost", adCurrency)
.Parameters.Append .CreateParameter("@VendorCost", adCurrency)
End With
End If
cmdUpdate.Parameters("@ID").Value = !ID
cmdUpdate.Parameters("@CUpdated").Value = !CUpdated
cmdUpdate.Parameters("@VUpdated").Value = !VUpdated
cmdUpdate.Parameters("@Checked").Value = !Checked
cmdUpdate.Parameters("@UseDirect").Value = !UseDirect
cmdUpdate.Parameters("@DirectCost").Value = !DirectCost
cmdUpdate.Parameters("@VendorCost").Value = !VendorCost
cmdUpdate.Execute , , adExecuteNoRecords
With this method, you only "create" the command once, then you fill it with variables as often as you like. I greatly prefer to create each parameter specifically, it's really not that much trouble.
Student of SQL and Golf, Master of Neither -
Thanks for the article. I just upgraded to VB.net 2005 from VB6 and started using TableAdapter objects to connect to SQL Server and stored procedures. It seems to work OK. Is this a good way to go? You don't seem to use these.
Thanks,
Jim Shipley
-
Since becoming a DBA 4 years ago, I haven't really kept up with the VB side of things. I have written only a handful of .Net apps with VB.Net 2003. I wonder if TableAdapter objects are new with .Net 2005, I'm not familiar with them.
Anyway, the real point of the article was supposed to be focused on using stored procedures with parameters. There are many ways of calling them from the program you are writing and many different languages, so I just showed a very simple example.
Alzdba -- thanks for sharing your code generating proc. I love stuff like this. Maybe you should submit it to the script library!
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Or, if you wanted to do it the .NET way (as opposed to VB6 style), Fill a DataSet and access the two tables individually. That, or open a SqlDataReader and use the .NextResult method to hit up the next table.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply