Question of the month April 2017 – Why does GO throw error in Dynamic SQL and not in static SQL?

Execute the following code

select 10 as number1
GO
select 11 as number2

It produces following two result sets


number1
-----------
10

(1 row(s) affected)

number2
-----------
11

(1 row(s) affected)

Now the same code executed as Dynamic SQL,

EXEC
(
'
select 10 as number1
GO
select 11 as number2
'
)

throws the following error.

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'GO'.

The Question is “Why does GO throw error in Dynamic SQL but not in static SQL?”

Advertisements

Column alias in SQL Server – Always precede with keyword AS

In SQL Server, a column alias can be specified in several ways. Look at the following example

select
	100number,
	100.number,
	100 number,
	100'number',
	100"number",
	100 [number]
	100              number,
	100 as number

The result is

number   number   number    number   number   number   number   number
-------- -------- --------  -------- -------  -------  -------  ------- 
100      100      100       100       100     100      100      100

Now as you see the column alias is number for all the expressions although they are all different expressions.

However you need to use AS before the alias name for better reading and standard

Now execute the following and see the result

drop table if exists #employee_master;
create table #employee_master
(
emp_id int, emp_name varchar(100), dob datetime
)
insert into #employee_master
select 1,'Sankar','19760910' union all
select 2,'Kamal','19680707' union all
select 3,'Rajesh','19661222'
Select emp_id, emp_name dob from #employee_master

The result is


emp_id      dob
----------- ------------
1           Sankar
2           Kamal
3           Rajesh

The SELECT statement lists out three columns but the output has only two columns. The data of emp_name are listed under the column name dob. It is because the missing comma between column names makes that dob becomes alias name for emp_name which is confusing and hard to detect if there are hundreds of lines in the procedure.

In my opinion, SQL Server should force preceding AS before column alias