Post reply

Dynamic Store Procedure

  • September 23, 2021 at 11:43 am

    #3932153

    hello,

    I want to create a dynamic StoreProcedure that would do some simple checks and that is setting these variable

    SET @valuesInput = (select count(distinct COLUMN_1) from [TABLE_1] where [COLUMN_1] is not null)

    SET @valuesInserted = (select count(distinct COLUMN_2) from [TABLE_2])

    SET @countDuplicates = (select count(*) from [TABLE_2] group by COLUMN_2 having count(*) > 1)

    SET @error_message = 'FAILED: xxxxxxxx'

    what I need help for - is how I make it dynamic? I mean, I would reuse this SP in other SP that are doing the same checks,

    but using different table names and columns

    for example - I want to call this SP in another SP where TABLE_1: employee , COLUMN_1: name

    then in another SP where TABLE_1: orders, COLUMN_1 : product

    thank you very much

  • September 23, 2021 at 2:39 pm

    #3932237

    You cannot do this directly. There isn't a capability for T-SQL to do something like "select x from @mytable".

    If you want something like:

    declare @Product2Count int
    exec @Product2Count = CheckTableCountSP @Table = 'SalesOrderDetail', @column = 'ProductID', @val = 2
    select @Product2Count

    You could do this:

    CREATE OR ALTER PROCEDURE CheckTableCountSP
        @Table NVARCHAR(100),
        @column NVARCHAR(200),
        @val INT
    AS
    BEGIN
        DECLARE @i INT,
                @cmd NVARCHAR(4000),
                @cmd1 NVARCHAR(4000);

        SELECT @cmd1 = N'select count(*) from ' + @Table + N' where ' + @column + N' = ' + CAST(@val AS NVARCHAR(10));
        SELECT @cmd = CAST(@cmd1 AS NVARCHAR(4000));
        EXEC sp_executesql @cmd, N'@i int output', @i OUTPUT;
        RETURN @i;
    END;
    GO

    We are creating a dynamic string here, which I try to avoid, but this works. The key here, is are you looking for specific types of checks that make sense in a proc? Also, how often does this run?

    The reason is that this dynamic string needs to be compiled each time, which can be expensive relative to the cost of running the query. In many cases, I might write a proc generator that would generate a series of procs to do this for specific tables/columns, as I doubt you need this for every table/column combination. That way if requirements changed, I could also the generator rather than each proc.

    This also has the potential of SQL injection when this proc is used, as I'm not sanitizing the params. You could add some checks, strip out things like semicolons, but there is a danger someone will use this in an unintended way if you are not careful.

     

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

You must be logged in to reply to this topic. Login to reply