Stored Procedures - Executing & Dynamic SQL

The permission to execute a stored procedure defaults to its owner. However you can use the GRANT statement to grant the permission to a procedure to other users, as follows:

1.GRANT EXECUTE ON byroyalty TO PUBLIC

Generally, stored procedures are executed with EXECUTE procedure_name syntax. Although, if the stored procedure call is the first step in the batch then the EXECUTE keyword is not required. Alternately, you can use the sp_executesql system stored procedure to call a user-defined (or system) stored procedure. 

Sp_executesql can be used to call any statement or batch, not just a stored procedure. Executing SQL statements that are built "on the fly" is referred to as dynamic SQL. You can use either sp_executesql or the EXECUTE command to execute your dynamic SQL statements. The following paragraphs summarize the usage of each alternative. 

The system stored procedure sp_executesql behaves very similar to the EXECUTE command, however, it offers two advantages:

• Parameters can stay in their native data type - with EXECUTE you will have to pass a string; therefore, everything needs to be converted to string data types with EXEC.
• Query optimizer is more likely to re-use the existing execution plans if you have to run the same query with different parameters. The reason for this is that the text of the query does not change with sp_executesql - with EXEC you pass a different string each time.

The following query shows how to build a query string dynamically with sp_executesql and EXECUTE:

1.DECLARE @string NVARCHAR(100) 

2.SELECT @string = 'select * from authors' 

3.EXEC sp_executesql @string

Or you can use EXEC syntax to accomplish the same. 

1.DECLARE @string VARCHAR(100) 

2.SELECT @string = 'select * from authors' 

3.EXEC (@string)

Either command will return all rows from the authors table. Since there are no numeric values involved, both queries will perform exactly the same. Now let's consider another situation: we'd like to return sales where quantity exceeds a particular value. We could do this with EXEC command as follows:

1.DECLARE @string VARCHAR(100), @qty INT 

2.SELECT @qty = 20 

3.SET @string = 'select * from sales where qty > ' + CONVERT(VARCHAR(2), @qty) 

4.EXEC (@string)

On the other hand, sp_executesql would not require explicit conversion of the @qty variable data type. Instead you'd have to provide the datatype as well as the parameter name within the sp_executesql call, as follows:

1.DECLARE @string NVARCHAR(100), @qty INT 

2.SET @qty = 20 

3.SET @string = 'select * from sales where qty > @qty' 

4.EXEC sp_executesql @string, N'@qty int', @qty

Although both of the above queries return the same results the second one is more efficient. Notice that sp_executesql requires that the statement parameter be of a Unicode data type. 

You can pass system or user-defined functions to the EXEC or sp_executesql. For instance the following statements behave identically:

Functions with sp_executesql:

1.DECLARE @string NVARCHAR(100), @qty INT 

2.SET @qty = 20 

3.SET @string = 'select stor_id, sum(qty) from sales group by stor_id having sum(qty) > @qty' 

4.EXEC sp_executesql @string, N'@qty int', @qty

Same thing with EXEC:

1.DECLARE @string VARCHAR(100), @qty INT 

2.SELECT @qty = 20 

3.SET @string = 'select stor_id, sum(qty)  from sales group by stor_id having sum(qty) > ' + CONVERT(VARCHAR(2), @qty) 

4.EXEC (@string)

Some limitations still apply with EXEC and sp_executesql. For instance, you cannot create and use local variables within the string passed to either command. The following example will fail:

1.DECLARE @string NVARCHAR(100) 

2.SET @string = 'declare @qty int '+ char(10) 

3.SET @string = @string + 'SET @qty = 20 ' + char(10) 

4.SET @string = 'select stor_id, sum(qty) from sales group by stor_id having sum(qty) > @qty' 

5.EXEC sp_executesql @string, N'@qty int', @qty

SQL Server will inform you that you must declare @qty variable before using it. Similarly, any temporary objects created within dynamic SQL exist only for the duration of dynamic SQL execution and are not visible outside of dynamic SQL's scope. 

Sp_executesql behaves identically to EXEC when it comes to changing the database context. If you change the database context within the stored procedure or batch and execute that module with sp_executesql or EXEC, you'll be back to the database where you started as soon as the module is done executing. Similarly, the SET commands issued within the context of EXEC or sp_executesql do not affect the main block of code and are effective only while the dynamic SQL is executing. On the other hand, the SET commands used in a batch prior to calling the dynamic SQL will have effect on how dynamic SQL executes.