How to Create a Stored Procedure in VB 1

[RikHess]

I have an app that I want to send to some other users. All users have MS SQL Server.

The app uses a custom Stored Procedure. I want the app to be able to check if the SP is present on the user's system. If it is not there, I'd like the app to be able to build it.

Is this possible? Any hints, tips, or other helpful ideas are welcome.

Thanks in advance.

 

[gmmastros]

With SQL Server, there are system views that you can use to determine if the stored procedure exists.

In Query Analyzer, run this...

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] Information_Schema.Routines

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

Thanks George, but what I want to do is automate the process so the user doesn't have to know anything about SQL Server.

I want it to be self contained in the VB app.

Can I do this?

 

[gmmastros]

Yes. My earlier advice is part of that solution.

Suppose you have a procedure that looks like this...

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] GetCustomerOrderCount
[COLOR=blue]As[/color]
[COLOR=blue]Select[/color] CustomerName, [COLOR=#FF00FF]Coalesce[/color]([COLOR=#FF00FF]Count[/color](*), 0) [COLOR=blue]As[/color] OrderCount
[COLOR=blue]From[/color]   Customer
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] Orders
         [COLOR=blue]On[/color] Customer.CustomerId = Orders.CustomerId
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] CustomerName

In VB, you can first execute this query.

[COLOR=blue]If[/color] Exists([COLOR=blue]Select[/color] * [COLOR=blue]From[/color] Information_Schema.Routines [COLOR=blue]Where[/color] Specific_Name = [COLOR=red]'GetCustomerOrderCount'[/color])
  [COLOR=blue]Drop[/color] [COLOR=blue]Procedure[/color] GetCustomerOrderCount

And then execute the Create Procedure query. Essentially, if the procedure exists, you can drop it. Then, simply create it.

Usually what I do is... I have a table in the database called CONFIG. This table looks like...

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] Config(ConfigName [COLOR=blue]VarChar[/color](50) [COLOR=blue]Primary[/color] [COLOR=blue]Key[/color], ConfigValue [COLOR=blue]VarChar[/color](200))

In this table, I will have a record that looks like...

[tt][blue]
ConfigName ConfigValue
--------------- -----------
DatabaseVersion 23
[/blue][/tt]

When my app starts up, it compares the value in the table with the value hard coded in the app. If the version (in the database) is less, then I run the scripts (which may take a couple minutes). If the version is the same, I don't do anything. You see, it's VERY quick to see if the database version is the same (or not), so it doesn't slow down my load process. Only if the versions are different will I actually update the procedures (and tables, views, functions, etc...).

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

I'm still missing something.

As a test, I tried creating a new VB project with:

Option Explicit

Private Sub Form_Load()
    If Exists(Select * From Information_Schema.Routines Where Specific_Name = 'sp_myproc') then
        MsgBox "OK"
    Else
        MsgBox "Fail"
    End If
End Sub

The system reports an error on "Select".

Can you point me to a reference where I can read more about this?

Thanks for your time,
Rik

 

[gmmastros]

OK. I see the confusion. You need to run this as a query that you pass to the database.

Option Explicit

Private Sub Form_Load()
    
    Call globalConnection.Execute("If Exists(Select * From Information_Schema.Routines Where Specific_Name = 'sp_myproc') Drop Procedure sp_myproc")

    Call globalConnection.Execute("Create Procdure sp_myproc As etc.....")

End Sub

In this example, globalConnection is an ADODB.Connection object that is already connected to the proper database. Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

George, you have given me a good boost down this path but I'm still having a problem. Here is what I am trying:

    s_Text = "If Not Exists(Select * " & _
            "From Information_Schema.Routines " & _
            "Where Specific_Name = 'sp_myproc') "
    s_Text = s_Text & "CREATE PROCEDURE sp_myproc as ... "

    Call globalconnection.Execute(s_Text)

Upon execution, the system is giving me an error:
Run-time error '-2147217900 (8004e14)':
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'PROCEDURE'.

Can you see my error?

Thanks again for all your help!

 

[gmmastros]

Yes. I see the error.

Follow my original advice, which was...

1. If the procedure exists, drop it.
2. Create the procedure.

There are 2 problems with your code.

First, CREATE PROCEDURE must be the first statement in a batch. This means that you cannot wrap an IF check around it.

Second, this is just bad logic. You see, even if this did work, and you succesfully create the procedure, it's possible that a year from now, you may want to change it. So, subsequent versions would not update the procedure with a newer one.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

I had left out my test logic for dropping the procedure from my code example.

But your note that "CREATE PROCEDURE must be the first statement in a batch" is helpful.

Is there a way for the SQL Server test for an object's existance to return a status to the VB program? I can see where I may have several tests to determine when to drop an object before I am ready to "create" it again. So I really need to know if it was dropped earlier.

This was the reason for my code snipit -- which was the final step in this process, "if the process does not exist when I get to this point, create it."

Again, if this is documented elsewhere, please point me to it. I don't want to be a bother to you.

I really do appreciate your time and tips.

Rik

 

[gmmastros]

Well, you could have a query that looks like this...

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Count[/color](*) As ProcedureExists
[COLOR=blue]From[/color]   Information_Schema.Routines
[COLOR=blue]Where[/color]  Specific_Name = [COLOR=red]'YourProcName'[/color]

You'll need call this from the database in the same way that you would return any other data from the DB. This will return 1 row with 1 column (column name is ProcedureExists). If the procedure exists, the value will be 1. If it doesn't exist, the value will be 0.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

Thanks for the help! This was exactly what I needed.

Rik