C# running temporary stored procedure

Question

I have a SQL statement that I need to run in C# and would need to get parameters from C# code. I know stored procedures are preferred to avoid SQL injection but I am just looking to do this in C#.

I am translating this SQL to C# but I encountered an error even though the query works in SQL Server Management Studio. It uses temporary stored procedure and temp table below:

-- 1.) Declare a criteria table which can be any number of rows
BEGIN TRY 
    DROP TABLE #CriteriaTable 
END TRY 
BEGIN CATCH 
END CATCH

CREATE TABLE #CriteriaTable (ParameterCode VARCHAR(64), Value VARCHAR(64))

-- 2.) Declare a procedure to add criteria table
BEGIN TRY 
    DROP PROCEDURE #AddCriteriaTable 
END TRY 
BEGIN CATCH 
END CATCH
go

CREATE PROCEDURE #AddCriteriaTable
    (@ParameterCode VARCHAR(64), @Value VARCHAR(64))
AS
    INSERT #CriteriaTable 
    VALUES(@ParameterCode, @Value)
GO

-- 3.) Do a computation which accesses the criteria
BEGIN TRY 
    DROP PROCEDURE #ComputeBasedOnCriteria 
END TRY 
BEGIN CATCH 
END CATCH
go

CREATE PROCEDURE #ComputeBasedOnCriteria
     (@product VARCHAR(36) = 'ABC',
      @currency VARCHAR(3) = 'USD',
      @zScore FLOAT = .845)
AS
    -- Code inside this procedure is largely dynamic sql. 
    -- This is just a quick mock up
    SELECT 
        @Product ProductCode,
        @currency Currency,
        950 ExpectedRevenue,
        *
    FROM 
        #CriteriaTable c
    PIVOT
        (min (Value) FOR ParameterCode IN
             ([MyParam1], MyParam2, MyParam3)
        ) AS pvt
    GO

    --End of code for Configuration table

-- Samples: Execute this to add criteria to the temporary table that will be used by #ComputeBasedOnCriteria
EXEC #AddCriteriaTable 'MyParam1', 'MyValue1'
EXEC #AddCriteriaTable 'MyParam2', 'MyValue3'
EXEC #AddCriteriaTable 'MyParam3', 'MyValue3'

--Execute the procedure that will return the results for the screen
EXEC #ComputeBasedOnCriteria

Now trying this in C# I encounter an error when I try to run the #AddCriteriaTable procedure. When I try to run the ExecuteQuery on the second to the last line it throws:

Exception: System.Data.SqlClient.SqlException, Incorrect syntax near the keyword 'PROC'.

Why does it work in SQL Server but not in C# code? Is there another way to do this in C#? Let me know if there are c# guidelines I should follow as I am still learning this c# - db work.

EDIT: I know I could do this as a normal stored proc and pass in a DataTable however there are team issues I cannot say and it forces me to use the sp as a text.

Answer 1

The reason that it is failing is you are passing parameters to the CREATE PROC section here:

cmd.CommandText = @"CREATE PROC #AddCriteriaTable (@ParameterCode VARCHAR(64), @Value VARCHAR(64)) AS INSERT #CriteriaTable VALUES (@ParameterCode, @Value)";
cmd.Parameters.AddWithValue("@ParameterCode", request.Criteria.First().Key;
cmd.Parameters.AddWithValue("@Value", request.Criteria.First().Value;
var reader2 = cmd.ExecuteReader();

It does not make sense to pass the values here, since you are just creating the procedure, you only need to pass them when executing the procedure. If you run a trace you will see something like this being executed on the server:

EXEC sp_executesql 
        N'CREATE PROC #AddCriteriaTable (@ParameterCode VARCHAR(64), @Value VARCHAR(64)) AS INSERT #CriteriaTable VALUES (@ParameterCode, @Value)',
        N'@ParameterCode VARCHAR(64),@Value VARCHAR(64)',
        @ParameterCode = 'MyParam1',
        @Value = 'MyValue1'

Which will throw the same incorrect syntax error when run in SSMS. All you need is:

EXEC sp_executesql 
    N'CREATE PROC #AddCriteriaTable (@ParameterCode VARCHAR(64), @Value VARCHAR(64)) AS INSERT #CriteriaTable VALUES (@ParameterCode, @Value)';

So in c# you would need:

//First Create the procedure
cmd.CommandText = @"CREATE PROC #AddCriteriaTable (@ParameterCode VARCHAR(64), @Value VARCHAR(64)) AS INSERT #CriteriaTable VALUES (@ParameterCode, @Value)";
cmd.ExecuteNoneQuery();

//Update the command text to execute it, then add parameters
cmd.CommandText = "EXECUTE #AddCriteriaTable @ParameterCode, @Value;";
cmd.Parameters.AddWithValue("@ParameterCode", request.Criteria.First().Key;
cmd.Parameters.AddWithValue("@Value", request.Criteria.First().Value;
var reader2 = cmd.ExecuteReader();

I think you are over complicating everything, a temporary stored procedure to add data to a temporary table seems over kill. If you are executing from code it seems likely that you need to reuse everything, so why not just have a permanent procedure for your computation, and then use a defined type to manage instances of the execution.

So first create your type:

CREATE TYPE dbo.CriteriaTableType AS TABLE (ParameterCode VARCHAR(64), Value VARCHAR(64));

Then create your procdure:

CREATE PROC dbo.ComputeBasedOnCriteria
(
    @product        VARCHAR(36)='ABC',
    @currency       VARCHAR(3)='USD',
    @zScore         FLOAT = .845,
    @CriteriaTable  dbo.CriteriaTableType READONLY
)
AS
--Code inside this proc is largely dynamic sql. This is just a quick mock up
SELECT 
        @Product ProductCode
        ,@currency Currency
        ,950 ExpectedRevenue
        ,*
FROM    @CriteriaTable c
        PIVOT (MIN (Value) FOR ParameterCode IN (MyParam1, MyParam2,MyParam3)) AS pvt;
GO

Then finally to run:

DECLARE @Criteria dbo.CriteriaTableType;
INSERT @Criteria 
VALUES
    ('MyParam1', 'MyValue1'),
    ('MyParam2', 'MyValue2'),
    ('MyParam3', 'MyValue3');

EXECUTE dbo.ComputeBasedOnCriteria @CriteriaTable = @Criteria;

You can even populate the criteria table in c#, and just pass this from c# to the procedure.

    var table = new DataTable();
    table.Columns.Add("ParameterCode", typeof(string)).MaxLength = 64;
    table.Columns.Add("Value", typeof(string)).MaxLength = 64;

    foreach (var criterion in request.Criteria)
    {
        var newRow = table.NewRow();
        newRow[0] = criterion.Key;
        newRow[1] = criterion.Value;
        table.Rows.Add(newRow);
    }
    using (var connection = new SqlConnection("connectionString"))
    using (var command = new SqlCommand("dbo.ComputeBasedOnCriteria", connection))
    {
        var tvp = command.Parameters.Add("@CriteriaTable", SqlDbType.Structured);
        tvp.TypeName = "dbo.CriteriaTableType";
        tvp.Value = table;

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                //Do Something with your results
            }
        }
    }

Answer 2

If you are executing SQL to create a stored procedure via C# then you might as well just execute your SQL via C# and forget about the Procedure.

The point of using a stored procedure to avoid SQL Injection only applies when the stored procedure already exists on the server and you are not creating it via the code.

You can avoid SQL injection here by using a Parameterised query. Parameters prevent sql injection by validating the data type. So if you insert a integer in your code then someone attempting injection cannot supply a string with special characters which changes your expected result.

BUT apart from all that, you're getting an error because you have CREATE PROC in your SQL in C# instead of CREATE PROCEDURE