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.

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