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;

• McNets: Have a look at this blog of @AaronBertrand about it.

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.

• Bogdan Bogdanov: Your answer is good, @S4V1N, I will up vote it, but it does not answer my question. I have SP which I execute in EXEC(SQL), not EXEC SP. I prepare SQL string which looks like SET @sSQL = 'EXEC sp_Run 1, 5 ...'. then I run EXEC(@sSQL). I don't have problem with injection because this all is inside my engine and I know what it does. I have the problem with the timing - 90% of the time it is low, but in 10% it is too high for the requirements.
• S4V1N: Are you testing it on a prod or test version, and how busy are the tables you are affecting in that procedure?
• Bogdan Bogdanov: We have issue on Prod. The tables are busy (but far from real load that is expected).
• Robert Carnegie: I am not sure of this, but the string passed to EXEC() or to sp_executesql does have to be "compiled" although it is only calling a stored procedure. If you use sp_executesql then the procedure call itself can be written with parameter placeholders (@parameter1, @parameter2) with values supplied in separate inputs to sp_executesql, and so the procedure call is compiled once and re-usable. SQL Server may or may not compile each call of EXEC() separately for the same procedure being called with different parameters - it is supposed to auto recognise the duplication but I don't trust it.