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.
EXEC(SQL), not from
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;