Friday, October 17, 2014

Use Go inside the Begin end in sqlserver

While i am working on one day having task of add column if not exists in the table, then add it and update it with some values

i used below scripts:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'EMP' AND COLUMN_NAME = 'SAL_COMM')
BEGIN

ALTER TABLE EMP ADD SAL_COMM int;
UPDATE EMP SET SAL_COMM = coalesce(SAL+COMM,sal,0);
END


Msg 207, Level 16, State 1, Line 6
Invalid column name 'SAL_COMM'.

But error is populated.
WHY:
i above query set we are executing alter and update command in one set. Now what happen at query parse, where it will check for the syntax and other parameters like column table existence.
When parser reach at UPDATE query it fail because "SAL_COMM" not found in the database.

SOLUTION:
Now what we can do,
we now the reason query fail because not existence of column. In that case we need to execute ALTER command before execution of UPDATE command.
This approach we get with using GO (batch seprator, where sql engine execute all query where GO placed), But issue is We can't use GO in BEGIN..END block.

SOOOO WE NEED TO DYNAMIC SQL, where parser get by-pass the UPDATE query.

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EMP' AND COLUMN_NAME = 'SAL_COMM')
BEGIN
ALTER TABLE EMP ADD SAL_COMM int;
exec sp_executesql N'UPDATE EMP SET SAL_COMM = coalesce(SAL+COMM,sal,0)';
END



No comments:

Post a Comment

web stats