SQL Server Performance Forum – Threads Archive

sp_executesql v. EXECUTE

Which is better sp_executesql or EXECUTE, and why? …And how much better? How do you use each? Thanks, v/r Gooser

---

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql. Changes in database context last only to the end of the sp_executesql statement.
Being able to substitute parameters in sp_executesql offers these advantages to using the EXECUTE statement to execute a string: Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.
The Transact-SQL string is built only once.
The integer parameter is specified in its native format. Casting to Unicode is not required. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

should I use sp_executesql to call a stored procedure from within a stored procedure, or is there a better way? Or, maybe what I am asking, or should be asking, is what’s the best way to call a stored procedure from a stored procedure? Thanks, v/r Gooser

---

To call one store procedure from other there is no problem. Just write the name, pass parametres and that all. Of course if sp are stored in other place or database you have to precede with that.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

---

but the limitation with using sp_executesql is you need to pass nvarchar type which can be only upto 4000 characters.

---

Shouldn’t be a problem with most statements. And if your query spells out the criteria, then you can also feed those through parameters: EXEC dbo.sp_ExecuteSQL
N’SELECT * FROM MyTable WHERE Col1 = ”abcd123456789” AND Col2 = 13′ DECLARE @V VARCHAR(13), @I INT
SET @V = ‘abcd123456789’
SET @I = 13 EXEC dbo.sp_ExecuteSQL
N’SELECT * FROM MyTable WHERE Col1 = @P1 AND Col2 = @P2′,
N’@P1 VARCHAR(13), @P2 INT’,
@V, @I Main advantage of sp_ExecuteSQL is that you can have any number of output parameters, whereas with EXEC you have no way of returning information – only when calling a stored procedure, and then you have only one return value … DECLARE @R INT, @Z INT EXEC dbo.sp_ExecuteSQL
N’SELECT @P3 = COUNT(*), @P4 = SUM(Col2) FROM MyTable WHERE Col1 = @P1 AND Col2 = @P2′,
N’@P1 VARCHAR(13), @P2 INT, @P3 INT OUTPUT, @P4 INT OUTPUT’,
@V, @I, @R OUTPUT, @Z OUTPUT

---

]]>