C#Scripting [SOLVED]: How to create a stored procedure with dynamically created IN set

C#Scripting [SOLVED]: How to create a stored procedure with dynamically created IN set

Home Forums Scripting C# Tutorials C#Scripting [SOLVED]: How to create a stored procedure with dynamically created IN set

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #132785

    Cloudy Point
    Keymaster

    QuestionQuestion

    I have some SQL that I am converting to stored procedures via blind requirement/request. There is a bit of SQL in the application I’m looking at that builds the where clause with a dynamic IN (set) statement. I have searched for dynamic stored procedure but nothing close to what I’m looking for has come up for me. Here is a sample of the WHERE clause:

    WHERE Var.A = @Param AND Var.Id IN
    

    From here the SQL is built manually using a string builder and then executed. Not sure how I’d convert this into a stored procedure as I’m fairly new to them.

    We are using C# and SQL Server

    #132786

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    You could use an user-defined data type.

    On the C# side it would look like this:

    //Setup a DataTable with the same structure as the SQL Type
    var data = new DataTable();
    data.Columns.Add("value", typeof(string));
    
    //Populate the table
    data.Rows.Add("oneID");
    data.Rows.Add("anotherID");
    
    //You create your sql command
    cmd.Parameters.Add("@listArgument", data);
    //Command execution
    

    On the SQL side you could have a type like this

    CREATE TYPE [dbo].[NVarCharTable] AS TABLE (
        [value] NVARCHAR(MAX) NOT NULL);
    

    And then the Stored procedure:

    CREATE PROCEDURE [dbo].[MyProc] 
        @listArgument NVarCharTable READONLY
    AS
    BEGIN
    
        SELECT *
        FROM FOO
        WHERE Var.Id IN (Select [value] FROM @listArgument)
    
    END
    

    Reference: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

    Source: https://stackoverflow.com/questions/44597671/how-to-create-a-stored-procedure-with-dynamically-created-in-set
    Author: kblok
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.