Create a Store Proceedure from VB.Net Code

Question

0.00/5 (No votes)

See more:

Is it possible to generate a stored proceedure by running VB.Net code so I can then call that proceedure in future sections of code rather than using inline SQL?

The database that I'll be connected to isn't guaranteed to be under my control or even created by my application so I can't preload the stored proceedure, I need to create it dynamically once my user tells me "Connect to this Server and then this DB".

Of course I also need to know if the process fails so I can return an error saying "Unable to proceed"

Any suggestions, articles, or examples appreciated.

Thanks.

Posted 5-Oct-11 9:36am

BHort

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Accepted Answer · 2011-10-05T09:58:00

Solution 1

There sin't anything special really. If you want to create a stored procedure you just create the command text and call ExecuteNonQuery, just as with say an update command.

The issue may be, however, with the permissions the user who is running the code may have. The DBA may not, and probably will not, give the permissions to create stored procs. If they won't give control to add the stored proc normally, they certainly don't want any type of procedure being created by code.

Posted 5-Oct-11 9:58am

Not Active

Comments

BHort 5-Oct-11 16:00pm

Understood. The DBA isn't the issue in this case ... it's more, I wouldn't know how to write it to be generated externally and I wouldn't know how to trap any errors that might get generated (although I guess a try except would hopefully be sufficient)

[no name] 5-Oct-11 16:04pm

Not very difficult with very little research.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "CREATE PROC...";
cmd.ExecuteNonQuery();

BHort 5-Oct-11 16:02pm

Could you perhaps provide an example of how I would write the code to create a proceedure "CountRecs" with the code "SELECT count(*) As numrecs FROM mytable" (trivial I know but it's simple enough to show me how it would be written which is what I'm looking for).