Try/catch help - Page 2

MCrowley · #16 (**permalink**) 11-13-12, 09:30

Oddly enough, I just looked up the steps to execution of a query in the SQL Server 2008 Internals book (highly recommended, by the way). The first step is Parsing both checks syntax, and builds a query tree for the optimizer to use later on. The second step is binding, and this is where you usually get the error message about a column name not being able to be bound. I am pretty sure parsing affects a whole batch, but binding appears to be statement specific, with the exception that the first failure to bind appears to fail the rest of the batch. I only did a little experimentation with it, so I can not call myself an authority.

Pat Phelan · #17 (**permalink**) 11-13-12, 12:19

Just for the jolly factor, let me know if this works for you:

Code:

CREATE PROCEDURE d43_FirstName
AS SELECT FirstName FROM PREmployees
GO
CREATE PROCEDURE d43_fName
AS SELECT fName FROM PREmployees
GO
CREATE TABLE PREmployees (
   fName NVARCHAR(99) NOT NULL
)
GO

INSERT INTO PREmployees (fName)
   VALUES ('Pat')
GO 5

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE  'fName' = COLUMN_NAME
      AND 'PREmployees' = TABLE_NAME)
   EXECUTE d43_fName
ELSE
   EXECUTE d43_FirstName
GO

DROP PROCEDURE d43_FirstName
GO
DROP PROCEDURE d43_fName
GO
DROP TABLE PREmployees
GO

-PatP

PracticalProgram · #18 (**permalink**) 11-13-12, 14:55

Your code worked for me on both 2008 and 2008R2.

I also ran the following test successfully:

Code:

CREATE TABLE PREmployees (
   fName NVARCHAR(99) NOT NULL
)
GO

INSERT INTO PREmployees (fName)
   VALUES ('Pat')
GO 5

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE  'fName' = COLUMN_NAME
      AND 'PREmployees' = TABLE_NAME)
   EXECUTE ('SELECT fName FROM PREmployees')
ELSE
   EXECUTE ('SELECT FirstName FROM PREmployees')
GO

DROP TABLE PREmployees
GO

Internet Services

Web Development

Digital Marketing

Consumer Tech