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.