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;
Best Answer
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:
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 isSo in the example:
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:
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.