How properly call stored procedure from EXEC(SQL)?

Question

I have engine is SQL which uses EXEC(SQLString) to run stored procedures. I do that because I need dynamically determine which stored procedure to call and to prepare parameters for that procedure. And I need that logic to be present.

So is this a good way to call stored procedure or I have to use sp_executesql or there is another way?

I ask this question because I have two timings - 1st is to prepare SQLString and to call procedure and call another small procedure and 2nd is the execution of the called procedure itself. It works fine almost all the time - the timings are very close. But from time to time I got huge difference between them - the 2nd timing is the same, but 1st is several times bigger (for example 3ms vs 90ms). I suppose some compilation takes place, but this stored procedure is executed very often.

REMARK:

I call sp_ from EXEC(SQL), not from EXEC sp_.

Here is the code how inner SP is called:

BEGIN
    -- DECLARE results table (results from SP are get via select and insert into from execution)
    DECLARE @table AS TABLE (
        ErrorTxt NVARCHAR(MAX) NOT NULL,
        XMLOut XML NULL);

    -- Prepare SQL for calling SP
    SET @sSQL = 'EXEC dbo.sproc_Method_0000129' +
        CASE
            WHEN (@iRes = 0) THEN '9'
            WHEN (@IsOut = 1) THEN '8'
        END + '_' +
        RIGHT('000' + CAST(@IDParam3 AS VARCHAR(3)), 3) + ' ' +
        '''' + REPLACE(CAST(@XMLIn AS VARCHAR(MAX)), '''', '''''') + ''', ' +
        ISNULL(CAST(@IDSrv AS VARCHAR(10)), 'NULL') + ', 0';

    -- EXEC SP and get results
    INSERT @table EXEC(@sSQL);
    SELECT TOP 1 @sErrorText = ErrorTxt, @XMLOutput = XMLOut FROM @table;

    -- Add log for timing
    EXEC dbo.sproc_AddTiming @XMLExtraParam OUT, 'SlowOperation', @dtOperation OUT;
END;

Answer 1

You can use it either way, however there is a one thing you should keep in mind EXEC() function is SQL injection prone

Take a look at this:

DECLARE @Data varchar(50)
SET @Data= ''''';TRUNCATE TABLE corruptTable'
execute (N'select * from corruptTable where Data = ' + @Data)

Executing this command, will give you no results (since we added that our data equals empty string) however, if you query the table ,table will be empty.

Where as in sp_executesql you are explicitly declaring parameters and

@Param = ''''';TRUNCATE TABLE corruptTable' will be compared as it is

So in the example:

DECLARE @Param varchar(50)
SET @Param= ''''';TRUNCATE TABLE corruptTable'
execute sp_executesql N'select * from corruptTable where Data = @Data',N'@Data varchar(50)',@Data = @Param

It wont yield any results, but it wont execute truncate table, because like i said, it is only treated as a parameters. Other than that, performance vise, there is no difference.

Note that you should call procedure with schema name included, and should be aware that dynamic SQL executions will create a new plan individually from stored procedure each time you call it.

Update

---

Since you are testing it on production,and the tables are busy, there are many reasons why "sometimes" its slow. I cannot give you exact details because i dont know logic and environment in the first procedure, but here are some things to watch for:

• Statistics Update in process: before plan is created, query optimizer checks whether statistics are up to date - since table is busy, statistics will be updated automatically at certain insert/update threshold and it can happen that it waits for statistics to be updated first before it can proceed, OR it can update statistics by itself so again you have to wait
• Locks : I assume your first procedure is retrieving records from a certain table. It can happen that while it tried to get certain records, these records/table are already locked by some update/insert/delete statement. So again you had that slight delay before it actually executed.

Also if you are getting a lot of records from first procedure, consider using temp table instead, table variable is not the best choice.

And lastly you can always diagnose procedure execution using Extended Events, and log it to a table/file if it exceeds certain threshold, along with execution plan,and waits so you can compare it with the regular executions.