T-SQL – Dynamic SQL

You can execute a string as SQL command.

DECLARE @myQuery AS NVARCHAR(MAX) = N'SELECT * FROM HR.Employees WHERE firstname = N''Sara''';
EXECUTE(@myQuery); -- 1st example
EXEC(@myQuery); -- 2nd example
EXEC sp_executesql @myQuery; -- 3rd example

You can use more secure way – pass parameter to avoid SQL injection.

DECLARE @name AS NVARCHAR(20) = N'Sara';
DECLARE @myQuery AS NVARCHAR(MAX) = N'SELECT * FROM HR.Employees WHERE firstname = @name';
EXEC sp_executesql @statement= @myQuery, -- set statement
                   @params=N'@name NVARCHAR(20)', -- declare parameter
                   @name= @name; -- set parameter
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s