Saturday, July 30, 2016

Dynamic SQL and error messages

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.


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:


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.


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: