Creating on-the-fly SQL stored procedure in C# then select it and then delete it

Question

Hey all I am trying to figure out how to go about this. I am wanting to send a parameter that is the name of my table to a query in my C# program. I've read that this is not possible and they suggested that you make a stored procedure to do this.

So this is my code so far:

CREATE PROCEDURE _tmpSP 
@TableName NVARCHAR(128) 
AS 
BEGIN 
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);
SET @Sql = N'SELECT TOP 1 HelpMsg FROM ' + QUOTENAME(@TableName)
 EXECUTE sp_executesql @Sql
 DROP PROCEDURE [_tmpSP]
END

When I execute that in Server Management Studio it creates the SP but never executes that store procedure nor deletes it.

When I run that SP in Server Management Studio (right-clicking on it under Programmability>dbo._tmpSP and choosing Execute Stored Procedure) and give it the table name, it populates and then deletes the SP. This is the end result I want without having to make 2 query's.

The SQL query for when the SP runs is this (tHelp being the table name):

USE [TTool]
GO    
DECLARE @return_value int    
EXEC    @return_value = [dbo].[_tmpSP]
        @TableName = N'tHelp'    
SELECT  'Return Value' = @return_value    
GO

I get the returned help message and also returned value 0.

How can I modify this SP in order to do that?

Why are you creating a stored procedure so that you can execute it once and then delete it? That doesn't make any sense at all. Why do you need a stored procedure? And ouf course the script to create your procedure isn't going to run it. — Sean Lange, Mar 17 '17 at 18:36
@SeanLange because I am unable to do this: **SELECT TOP 1 HelpMsg FROM @val1** — StealthRT, Mar 17 '17 at 18:37
what is it? a self-destruct procedure :) I would have expected that the query processor would refuse to drop the procedure it is currently executing. — Cee McSharpface, Mar 17 '17 at 18:38
That is because you can't parameterize object names. But seriously what is the point? One of the main ideas of parameterization is to prevent sql injection and the code you created doesn't even pretend to avoid that. In fact, you have defeated the whole point of the parameter by using it to build a dynamic string to execute. Just create your dynamic string and execute it. But keep quotename in there. You might even do a check against sys.objects to ensure the value is an object name. — Sean Lange, Mar 17 '17 at 18:41
apart from the problem presented here, there may be a design flaw. TOP 1 is not deterministic. so either these tables contain just one row each, then you would be better of merging all those help texts into a single table with some meaningful key, or you run the risk of reading a different row each time the query runs. — Cee McSharpface, Mar 17 '17 at 18:53

score 1 · Accepted Answer · edited Mar 20 '17 at 15:06

1

Just do this, forget stored procedures:

EXECUTE sp_executesql 'SELECT TOP 1 HelpMsg FROM '+QUOTENAME(@TableName)

Dirty C#...

string qry = string.Format("SELECT TOP 1 HelpMSG FROM {0}", myTableName.Replace("'", "''"));
cmd = conn.CreateCommand();
cmd.CommandText = qry;
string helpMsg = conn.ExecuteScalar();

Where conn is an instance of System.Data.SqlClient.SqlConnection

edited Mar 20 '17 at 15:06

marc_s

732,580
175
1,330
1,459

answered Mar 17 '17 at 18:39

SsJVasto

486
2
13

Then it's even easier – SsJVasto Mar 17 '17 at 18:39
You need to wrap that table name in quotename to minimize the risk of sql injection. – Sean Lange Mar 17 '17 at 18:42
And even better would be to wrap the select statement with an exists to look at sys.objects to ensure that myTableName is in fact a table name. – Sean Lange Mar 17 '17 at 18:42
You have ruined your answer with your last update. You can NOT parameterize object names. That is now how sql works. – Sean Lange Mar 17 '17 at 18:45
object identifiers cannot be substituted by varchar parameters, I doubt this would execute – Cee McSharpface Mar 17 '17 at 18:45
@SeanLange yeah, but how far do you want to push validation? – SsJVasto Mar 17 '17 at 18:45
add using() {} around the command, and make CreateCommand a function call (), throw in some ; then it might finally compile and harvest upvotes :) – Cee McSharpface Mar 17 '17 at 18:49
@SeanLange What's your point? There is no "QUOTENAME" un my C# code... – SsJVasto Mar 17 '17 at 18:49
@dlatikay the "using" clause just creates a miniature scope on a single variable. If you put the code in a function and return the result, you have the same effect as a "using" clause – SsJVasto Mar 17 '17 at 18:51
@StealthRT Yeah, I've been doing too much VB recently, forgot the () (which aren't required in VB) – SsJVasto Mar 17 '17 at 18:53
At the very least you should add Quotename in your Dirty C# example. – Sean Lange Mar 17 '17 at 18:54
the using cause calls Dispose(), which is a pattern that should be followed for all classes that implement IDisposable. – Cee McSharpface Mar 17 '17 at 18:54
@dlatikay Which is also what happens at the end of a function call for all variable types that implement IDisposable... Only difference is that a function does it for all the variables, while using targets a single one. – SsJVasto Mar 17 '17 at 18:55
1

not in any known version of C#. the GC calls finalizers, but not dispose. out-of-scope does neither. see also [here](http://stackoverflow.com/a/2926884/1132334) – Cee McSharpface Mar 17 '17 at 18:58
It will mark that object as being ready to garbage collected. This is dangerous with sql connections because you can't control when those objects will be disposed and in the meantime your connection pool is short one connection. – Sean Lange Mar 17 '17 at 18:59
@SeanLange Well, Personally I would have made "conn" static outside the function, but I guess you can re-connect every single query of you want to prove a point... – SsJVasto Mar 17 '17 at 19:00
I would never have a static connection that stays open. That defeats the whole point of connection pooling. But we have strayed very far away from the OP at this point. – Sean Lange Mar 17 '17 at 19:03

LONG · Answer 2 · 2017-03-17T18:53:14.367

You need create another SP to apply your logic. First let's see your SP:

CREATE PROCEDURE [_tmpSP]
@TableName NVARCHAR(128) 
AS 
 BEGIN 
  DECLARE @Sql NVARCHAR(MAX);
  SET @Sql = N'SELECT TOP 1 HelpMsg FROM ' + @TableName
  EXEC(@Sql)
END

Then create another SP only if you need to drop the first one after return the result. The logic will be :

Create procedure auto_delete
@NewTableName
as
begin

EXEC _tmpSP @TableName = @NewTableName

Drop procedure [_tmpSP]

End

In C# (I assume you are using the 2nd SP above):

Your code could be like this:

..
using System.Data.SqlClient;
..
string a = YourTableName;        
using (SqlConnection sqlCon = new SqlConnection(YourDatabaseConnection))
{
 sqlCon.Open()
using (SqlCommand sqlCmd = sqlCon.CreateCommand())
{
sqlCmd.CommandText = "auto_delete";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add(new SqlParameter("NewTableName", a));
sqlCmd.ExecuteNonQuery();
}   
 sqlCon.Close();
 }

score 0 · Answer 3 · answered Mar 17 '17 at 18:43

0

I agree with @SsJVasto. If you still need your query not be hard coded in the C# program you can use an xml and keep the query in it. And fetch the xml and execute the query. I guess you would like to handle some dynamic stuff.

answered Mar 17 '17 at 18:43

Hari Hara Krishnan

68
8

score 0 · Answer 4 · edited May 23 '17 at 12:32

There is no point in doing this because there is quite complicated and also incurs the overhead of creating and dropping of the stored procedure. If you have a dynamic query that deals with some dynamic elements that cannot be pushed as parameters, you can construct the query string:

var query = $"SELECT TOP 1 col FROM {tableName}";

However, you must take care to avoid SQL injection if tableName is constructed based on user input. This question and its answers deal with this problem:

DbConnection connection = GetMyConnection(); 
DbProviderFactory factory = DbProviderFactories.GetFactory(connection);
DbCommandBuilder commandBuilder = factory.CreateCommandBuilder();
var tableName commandBuilder.QuoteIdentifier(rawTableName);

If "normal" (non table name) parameters are needed, pass them as usual within the query. E.g. @param1, @param2

@StealthRT - no, it should be {yourVariableHoldingCurrentTableName}. Don't miss the SQL injection part of the answer, if table name is computed based on user input. — Alexei - check Codidact, Mar 17 '17 at 18:47

Creating on-the-fly SQL stored procedure in C# then select it and then delete it

4 Answers4