Using Parameters with Stored Procedures

  • Capt Calamity

    SSC-Addicted

    Points: 449

    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

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    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

  • Dewes van Lohuizen

    SSC Veteran

    Points: 281

    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

    SSCarpal Tunnel

    Points: 4384

    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

    SSC Guru

    Points: 96590

    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

    SSCarpal Tunnel

    Points: 4384

    Maybe we've been dealing with the wrong vendors.

  • Brett Slaski

    SSC Veteran

    Points: 255

    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

    Old Hand

    Points: 345

    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

    SSC-Insane

    Points: 23224

    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

    SSChampion

    Points: 11811

    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

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4582

    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

  • James Shipley

    Valued Member

    Points: 70

    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

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    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

  • Mike C

    SSC-Insane

    Points: 23224

    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 29 total)

You must be logged in to reply to this topic. Login to reply