Exec within an sqlstament for Excecutenonquery

Transact-SQL
1

Hello,
I want to create a table, grant previlegs, create a stored procedure and finally use this stored procedure.
And I want to use all of them with 'exec'.
A good explication to use exec within an sqlstament ist here,
"https://stackoverflow.com/questions/3523989/how-to-use-sqlcommand-to-create-database-with-parameterized-db-name"
(for whatever reason it says this links is presented like 2 links, so I can put it here just like that)

But no matter what I'm doing I cant run finally the stored-procedure.
It says allways, it couldnt find the store procedure.
Or, if I try it without exec, than it says it has to much arguments.

It works only if I use all the other staments without exec.
This is how it looks, for grant and create stp as well.

[code]public void CreateTable(string strtblName)
{
strSQL = "EXEC ('CREATE TABLE ' + @tblName + ([EmployeeID] [int] NOT NULL PRIMARY KEY CLUSTERED," +
"[VorName] nvarchar NULL, [NachName] nvarchar NULL," +
" [Titel] nvarchar NULL," + " [City] nvarchar NULL," +
" [BirthDate] [date] NULL) ON [PRIMARY]')";

       try
       {

           myCnn.Open();
           myCmd.Connection = myCnn;
           myCmd.CommandType = CommandType.Text;
           myCmd.CommandText = strSQL;

           myCmd.Parameters.Clear();
           myCmd.Parameters.Add("@tblName", SqlDbType.Text);
           myCmd.Parameters["@tblName"].Value = strtblName;

           myCmd.ExecuteNonQuery();
           myCnn.Close();

       }
       catch (Exception ex)
       {
           myCnn.Close();
           throw new Exception(ex.Message);
       }

   }[/code]

I have something like a solution, but its dirty and not a real answer.
Take this two ado objects
SqlConnection
SqlCommand
make them ready
myCmd.CommandText = create the database (exec within the statement)
myCmd.ExecuteNonQuery();

myCmd.CommandText =create the table (exec within the statement)
myCmd.ExecuteNonQuery();

myCmd.CommandText = grant previligs for the table (exec within the statement)
myCmd.ExecuteNonQuery();

create a stp (exec within the statement)
myCmd.ExecuteNonQuery();

Now, to fill the table with datas, using the stp,but keep exec out of the statment
myCmd.CommandText = InsertIntotblName
myCmd.ExecuteNonQuery();

2

Your problem seems more like a C# problem. But I cannot see any code that actually calls a stored procedure. You need to post all the SQL you are using.

3

Well, I agree to that, but on the other hand 'exec' seems to me more and more question about a special behavior of the mssql-server.
Even if I already have a database, have a table, have a stp and now I want just use the stp, but with exec of course, than it tells me 'could not find a stp calld 'myStpName'.

The reason migh be, that, in case of using a stp, nothing will be created, as it is in case of create table, create stp and so on?

greetings pavel

4

I think we need to see the code for that bit.

Could be that you have a syntax error or somesuch, but because of how you are executing it, as dynamic SQL, you might not easily be able to "see" any error message that SQL raises.

Only other thought is that you cannot do
CREATE DATABASE / CREATE TABLE / CREATE MySproc / EXECUTE MySProc
all in one statement, some (I think all EXCEPT the EXEC MySProc) have to be separate statements. As you have described your code it does look like you are sending them to SQL "separately", so it looks like that is OK, but I raise it because you have described it in pseudo-code and perhaps your actual code is not like that. (SO you could be thinking that you have created Database, Table and Sproc but in fact SQL has raised an error (that you have not seed, for whatever reason)

5

Is there any chance to put a mini-VS-Project beside the answer?
I would place here the whole code, - but maybe its better to place it here within a working project?

greetings pavel

6

don't post a project. Most folks here work in SSMS, many exclusively

7

Well, this code should work. Should create a Database, a table, a STP, and finally use this STP, but will throw an error: "could not find a stp 'mySTPName'"

[code] private void btnTestExecUseStp_Click(object sender, EventArgs e)
{
SqlCommand mySQLCommand = new SqlCommand();
SqlConnection mySQLCnn = new SqlConnection();

        string strDBName = "DBTest";
        string strTblName = "TblTest";
        string strMsSQlServer = "myServer\\SQLEXPRESS";

         mySQLCnn.ConnectionString = "data source='" + strMsSQlServer  + "';Trusted_Connection = true";

         try
         {
             mySQLCnn.Open();
             mySQLCommand.Connection = mySQLCnn;
             mySQLCommand.CommandType = CommandType.Text;
             mySQLCommand.Parameters.Clear();

             //Create DB
             mySQLCommand.Parameters.AddWithValue("@myDBName", SqlDbType.Text).Value = strDBName;                
             mySQLCommand.CommandText = "exec ('CREATE DATABASE ' + @myDBName)";
             mySQLCommand.ExecuteNonQuery();


             mySQLCnn.ChangeDatabase(strDBName);
             mySQLCommand.Parameters.Clear();


             // Create Table
             mySQLCommand.Parameters.AddWithValue("@myTblName", SqlDbType.Text).Value = strTblName;               
             mySQLCommand.CommandText = "EXEC ('CREATE TABLE ' + @myTblName + '([OrderID] [int] NULL,[CustomerID] [smallint] NULL) ON [PRIMARY]')";
             mySQLCommand.ExecuteNonQuery();

             //GRANT TBL
             mySQLCommand.Parameters.Clear();
             mySQLCommand.Parameters.AddWithValue("@myTblName", SqlDbType.Text).Value = strTblName;
           
             mySQLCommand.CommandText = "EXEC ('GRANT SELECT,UPDATE,INSERT,DELETE ON ' + @myTblName + ' TO public;')";
             mySQLCommand.ExecuteNonQuery();

             //Create STP
             mySQLCommand.Parameters.Clear();
             mySQLCommand.Parameters.AddWithValue("@myTblName", SqlDbType.Text).Value = strTblName;
            
             mySQLCommand.CommandText = "EXEC ('CREATE PROCEDURE InsertInto' + @myTblName + ' @OrderID int, " +
                 "@CustomerID smallint  AS  BEGIN " +
                 " INSERT INTO ' + @myTblName + ' (OrderID,CustomerID) " +
                 " VALUES(@OrderID, @CustomerID);" + " END ')";
             mySQLCommand.ExecuteNonQuery();

             // Use STP to Insert
             mySQLCommand.Parameters.Clear();
             mySQLCommand.CommandType = CommandType.StoredProcedure;
             mySQLCommand.Parameters.AddWithValue("@myTblName", SqlDbType.Text).Value = strTblName;

             mySQLCommand.CommandText = "EXEC ('InsertInto' + @myTblName + ');";              
             mySQLCommand.Parameters.AddWithValue("@OrderID", 1);
             mySQLCommand.Parameters.AddWithValue("@CustomerID", 1000);
             mySQLCommand.ExecuteNonQuery();               
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message);
         } 
    }

[/code]

8

Why this:

mySQLCommand.CommandText = "exec ('CREATE DATABASE ' + @myDBName)";

rather than this?

mySQLCommand.CommandText = "exec ('CREATE DATABASE " & strDBName & ")";

I think instead of trying to get the SQL End to do parameter substitution for Database and Table names it would be a lot better if you did it in the APP

9

you wrote a long letter about why it might useful to see the entire code, so now, thats the answer you have?

using paramters means protection, maybe this are news, here, in this forum ?

10

You said originally you had written a stored procedure. If you called a stored procedure with

EXEC MySProc 'MyDatabaseName', 'MyTableName'

then, yes, you could check some things with the parameters, and any SQL code related to HOW you created the database - e.g. the OrderID / Customer ID logic - you could encapsulate IN the Sproc and then change that if/when the need arose (rather than having to change the SQL code in your application)

But in your example code you are just using Dynamic SQL to do this job. You are passing Database / Table name as a parameter and expecting SQL to just substitute it, but SQL won't do that. I've answered a (different) question on this recently, can't remember if who it was from though. SQL doesn't substitute Database, Table or Column names from parameters (unless you use Dynamic SQL, and if you are doing to do that it would almost certainly be easier to do it direct from your Application)

For what you are doing you would be better off using string handling in your APP to combine the Database Name and the CREATE TABLE syntax and execute that. You are at risk of SQL Injection, but you will be if you do it in a stored procedure instead because that will have to do exactly the same string manipulation to create a dynamic SQL command.

The benefit of parameters in SQL is in a query like this:

SELECT Col1, Col2, ...
FROM MyTable
WHERE Col3 = 'ABC123'

You can issue the query just like that - e.g. from your Application. In which case you application probably does some string manipulation

mySQLCommand.CommandText = "SELECT Col1, Col2, ...
FROM MyTable
WHERE Col3 = '" & strCol3Value & "'";

and, again, you have risk of SQL injection in the string manipulation.

The parametrised way would be to have the query

SELECT Col1, Col2, ...
FROM MyTable
WHERE Col3 = @Col3Value

and then run the query using a parameter. You can do that with a Stored Procedure:

CREATE PROCEDURE MySProc
     @Col3Value varchar(10)
AS
    SELECT Col1, Col2, ...
    FROM MyTable
    WHERE Col3 = @Col3Value

or using SQL direct from your application using sp_ExecuteSQL:

EXEC sp_ExecuteSQL 
   'SELECT Col1, Col2, ...
    FROM MyTabl
    WHERE Col3 = @Col3Value'
    , '@Col3Value varchar(10)'
    , @Col3Value = 'ABC123'
AS

but you can only do that for Column Values, you can not substitute Database, Table or Column names using parameters, so you cannot say:

CREATE PROCEDURE MySProc
     @MyTable  sysname,
     @Col3Value varchar(10)
AS
    SELECT Col1, Col2, ...
    FROM @MyTable  --<<< THIS is not allowed
    WHERE Col3 = @Col3Value
11

well, I like this answer.
First place for that question were here
http://www.codeproject.com/Messages/5115684/Exec-within-an-sqlstament-for-Excecutenonquery.aspx

then here
http://forums.codeguru.com/showthread.php?551625-exec-what-is-that-for-within-a-sqlstament-using-with-executenonequery

And I'm plesure to say, that, I never felt closer to a solution.
Or would any of you disagree?
Just a yes or no please, just to keep me on the way

12

Seems to say the same thing I did?

"Mika Wendelius wrote:
The problem with the table creation is that you try to use parameters, don't use them."

Second one asked a question but got no answer, so I'm not sure that adds anything to the discussion.

But maybe I missed the point of your question?

13

It seems to me pretty simple.

The MsSQlserver is made to reject a call like that, from ado.net, with intention.

There is no reason to call a existing stp with a constructet name,
from a application(maybe just about the risc to cause errors.)

And finally, there is no way to call a stp with the keyword ‘EXEC’, with or
without paramters, within the stp name

That’s probably
the answer. I write here probably cause, people with deeper knowledge of
the sever construction, may have a better or finally other explication