SQL Server allows to execute SQL code from a variable. The simple way is to use EXEC command.
1 2 3 |
DECLARE @query_text nvarchar(max); SET @query_text = N'SELECT * FROM Clients'; EXEC (@query_text); |
The second way is to use sp_executesql stored procedure. It even allows to run code with parameters.
1. Simple query
1 2 3 4 5 6 |
SET @sql_text = N' SELECT * FROM Clients WHERE CountryCode = ''US'' '; EXEC sp_executesql @sql_text; |
Don’t forget to change single quote (‘) with two single quotes inside a string.
2. Query with a parameter
You need to pass a list of parameters to be used inside a dynamic SQL query and actual values for those parameters.
1 2 3 4 5 6 7 8 9 10 |
SET @sql_text = N' SELECT * FROM Clients WHERE CountryCode = @CountryCode '; SET @param_list = N' @CountryCode char(2) '; EXEC sp_executesql @sql_text, @param_list, @code; |
Note that name of the parameter inside the query and the name of variable with actual value could be different.
3. Query with output parameter
1 2 3 4 5 6 7 8 9 10 11 12 |
SET @sql_text = N' SELECT @ClientCount = COUNT(*) FROM Clients WHERE CountryCode = @CountryCode '; SET @param_list = N' @CountryCode char(2), @ClientCount int OUTPUT '; EXEC sp_executesql @sql_text, @param_list, @code, @TotalClients OUTPUT; |
4. Query with table-valued parameter
The rules of the SQL Server are the same: we need to create table-valued type and use a readonly parameter.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TYPE CodeOfCountry AS TABLE ( Code char(2) not null ); SET @sql_text = N' SELECT * FROM Clients c JOIN @CountryList cl ON c.CountryCode = cl.Code '; SET @param_list = N' @CountryList CodeOfCountry READONLY '; EXEC sp_executesql @sql_text, @param_list, @CodeList; |
5. Temporary tables with dynamic SQL
SQL Server allows only to use temp tables that were created before running dynamic SQL code. If you create a temp table during dynamic query, it would be inaccessible after that query.
Important! You can pass more parameters to sp_executesql procedure than is really used in dynamic query. This can be very helpful when you need to dynamically add joins or where clauses to a query. So you pass all the parameters, but consume just a part of them.
Also you can download full sample codeĀ Dynamic-SQL-queries.zip to play with it.