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();