Dynamic SQL is a batch of SQL statements that you can run my executing the batch. Basically you declare a command variable and execute the command. The Command variable has the executable T-SQL code. The executable code may also contain the following:
System Stored Procedure
User defined stored procedure
CLR stored procedure
Scalar valued user-defined function
Extended stored procedure
In this post I am not going into any of the above which require more space and time. Rather I try to look at the simplest of the examples where I have observed some problems and seen some error messages.
Let me first give an example which is often used to demonstrate the use of Dynamic SQL.
D_SQL_00
The first line of code declares a vaibale @cmd of data type ccharacters
The second line declares a variable @tbl also of data type ccharacters
The third statement sets a value for the @tbl variable which in this case AdventureWorks2014.Person.Address table. This statement does not require the context of AdventureWorks2014, but can be run from 'master'
The fourth statement declares what the @cmd variable should do when executed. It is just a seelct satement that selects the top 5 rows from the Person.Address table.
You can see that this dynamic SQL returns the results requested.
Basically this is how it works.
Now let me take a look at a little more basic example to see how this execution takes place.
Let us consider this example to see if it works.
Declare @num as int = 12;
Declare @cmd as int;
Set @cmd='Select @num*@num*@num'
Execute @cmd
My expectation is it would return the (column) 12X12X12
However it returns the following error message:
D_SQL_01
Good. It only means that procname which in this case is @cmd can only be of type char, varchar, or nvarchar
The syntax is very critical.
The following statement which has just a parenthesis for the execute statement produces a different error message a shown.
D_SQL_02
Let us consider another example which is same as above but working with strings. Although the @wrd variable has be declared and set , there is still this unexplicable error.
D_SQL_03
This is one of common errors that people encounter dealing with dynamic SQL but the explanation may be different. I am still on the look out for a cool and reasonable explanation. You may observe that the statement 'Select @wrd' which is commented out does produce the correct result using the declared variable @wrd. beats me!
Read the MSDN article here:
https://msdn.microsoft.com/en-us/library/ms188332.aspx?f=255&MSPPError=-2147217396
System Stored Procedure
User defined stored procedure
CLR stored procedure
Scalar valued user-defined function
Extended stored procedure
In this post I am not going into any of the above which require more space and time. Rather I try to look at the simplest of the examples where I have observed some problems and seen some error messages.
Let me first give an example which is often used to demonstrate the use of Dynamic SQL.
D_SQL_00
The first line of code declares a vaibale @cmd of data type ccharacters
The second line declares a variable @tbl also of data type ccharacters
The third statement sets a value for the @tbl variable which in this case AdventureWorks2014.Person.Address table. This statement does not require the context of AdventureWorks2014, but can be run from 'master'
The fourth statement declares what the @cmd variable should do when executed. It is just a seelct satement that selects the top 5 rows from the Person.Address table.
You can see that this dynamic SQL returns the results requested.
Basically this is how it works.
Now let me take a look at a little more basic example to see how this execution takes place.
Let us consider this example to see if it works.
Declare @num as int = 12;
Declare @cmd as int;
Set @cmd='Select @num*@num*@num'
Execute @cmd
My expectation is it would return the (column) 12X12X12
However it returns the following error message:
D_SQL_01
Good. It only means that procname which in this case is @cmd can only be of type char, varchar, or nvarchar
The syntax is very critical.
The following statement which has just a parenthesis for the execute statement produces a different error message a shown.
D_SQL_02
Let us consider another example which is same as above but working with strings. Although the @wrd variable has be declared and set , there is still this unexplicable error.
This is one of common errors that people encounter dealing with dynamic SQL but the explanation may be different. I am still on the look out for a cool and reasonable explanation. You may observe that the statement 'Select @wrd' which is commented out does produce the correct result using the declared variable @wrd. beats me!
Read the MSDN article here:
https://msdn.microsoft.com/en-us/library/ms188332.aspx?f=255&MSPPError=-2147217396
