Skip to main content
Adding sample code
Source Link
Bogdan Bogdanov
  • 1.2k
  • 2
  • 18
  • 38

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;

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_.

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;
added 67 characters in body
Source Link
Bogdan Bogdanov
  • 1.2k
  • 2
  • 18
  • 38

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_.

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.

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_.

deleted 3 characters in body
Source Link
Bogdan Bogdanov
  • 1.2k
  • 2
  • 18
  • 38

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 second2nd 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.

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 second 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.

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.

Source Link
Bogdan Bogdanov
  • 1.2k
  • 2
  • 18
  • 38
Loading