June 2, 2011 at 8:49 am
I realize that the subject line for this topic makes me sound like an idiot but please bear with me.
I am writing a utility script that returns information about a table. Both it's structure and it's contents. Early in the script I determine if a certain set of Columns has been added to the table. Later in the script I want to determine if there is any data at all in the columns if they exist.
This issue is when the script runs against a table where the columns do not exist the SQL blows up with an error even though the statement doesn't actually get executed. I check the flag that I set earlier that determines if the columns are there.
Given that I am using SQL Server 2000 (don't ask . . . and changing that is not an option) I can't wrap the section in a TRY CATCH, so I'm trying to figure out how I can still include this information but not cause the error.
Any ideas would be greatly appreciated.
Thanks,
Doug
Here is the Reader's Digest Condensed version of the SQL
(My apologies for the structure. The editor strips out all the white space at the beginning of the lines.)
[font="Courier New"]
DECLARE @TableCatalog varchar(100)
DECLARE @TableSchema varchar(100)
DECLARE @TableName varchar(100)
DECLARE @HasAllCols bit
DECLARE @MissingAcctInfo int
DECLARE @Count int
SET @TableCatalog = '<my table catalog>'
SET @TableSchema = '<my table schema>'
SET @TableName = '<my table name>'
SET @HasAllCols = 1
SET @MissingAcctInfo = 0
SELECT @Count = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @TableCatalog
AND TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName
AND COLUMN_NAME IN ('Org','Fund','Acct','Project','Program','UserDef')
IF @Count = 6 SET @HasAllCols = 1
-- More stuff in here
IF @HasAllCols = 1 BEGIN
-- This is the statement that causes the error
--
SELECT @MissingAcctInfo = COUNT(*)
FROM [<my table catalog>].[<my table schema>].[<my table name>]
WHERE Org IS NULL
OR Fund IS NULL
OR Acct IS NULL
OR Project IS NULL
OR Program IS NULL
OR UserDef IS NULL
END
ELSE BEGIN
SET @MissingAcctInfo = 0
END
SELECT @HasAllCols AS HasAllCols, @MissingAcctInfo AS MissingAcctInfo
[/font]
As you might expect, these are the errors that are raised:[font="Courier New"]
Msg 207, Level 16, State 3, Line 28
Invalid column name 'Org'.
Msg 207, Level 16, State 3, Line 28
Invalid column name 'Fund'.
Msg 207, Level 16, State 3, Line 28
Invalid column name 'Acct'.
Msg 207, Level 16, State 3, Line 28
Invalid column name 'Project'.
Msg 207, Level 16, State 3, Line 28
Invalid column name 'Program'.
Msg 207, Level 16, State 3, Line 28
Invalid column name 'UserDef'.
[/font]
June 2, 2011 at 8:58 am
The problem is that all statements are being checked against the table schema prior to the actual execution of the statements.
Simply put, the above approach will not work
To accomplish your objective, I suggest you read up on "Dynamic SQL." Using this approach you can build code and execute code at runtime based on a given table's actual schema, not it's hypothetical schema.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 2, 2011 at 9:07 am
That's what I figured. I was just hoping that there might be some nifty trick that would get around it. I have done some work with Dynamic SQL in SQL Server so I'll give that a whirl.
Thanks,
Doug
June 2, 2011 at 9:35 am
Thanks to The Dixie Flatline for his/her response. I implemented the solution using Dynamic SQL and it works just fine now.
Here is the solution that I wound up with:
DECLARE @TableCatalog nvarchar(100)
DECLARE @TableSchema nvarchar(100)
DECLARE @TableName nvarchar(100)
DECLARE @HasAllCols bit
DECLARE @MissingAcctInfo int
DECLARE @Count int
DECLARE @SQL nvarchar(4000)
DECLARE @ParameterList nvarchar(500)
SET @TableCatalog = '<my table catalog>'
SET @TableSchema = '<my table schema>'
SET @TableName = '<my table name>'
SET @HasAllCols = 0
SET @MissingAcctInfo = 0
SELECT @Count = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @TableCatalog
AND TABLE_SCHEMA = @TableSchema
AND TABLE_NAME = @TableName
AND COLUMN_NAME IN ('Org','Fund','Acct','Project','Program','UserDef')
IF @Count = 6 SET @HasAllCols = 1
-- More stuff in here
IF @HasAllCols = 1 BEGIN
SET @ParameterList = '@MissingAcctInfo int OUTPUT'
SET @SQL = '
SELECT @MissingAcctInfo = COUNT(*)
FROM [<my table catalog>].[<my table schema>].[<my table name>]
WHERE Org IS NULL
OR Fund IS NULL
OR Acct IS NULL
OR Project IS NULL
OR Program IS NULL
OR UserDef IS NULL'
EXECUTE sp_executesql @SQL, @ParameterList, @MissingAcctInfo = @MissingAcctInfo OUTPUT
END
ELSE BEGIN
SET @MissingAcctInfo = 0
END
SELECT @HasAllCols AS HasAllCols, @MissingAcctInfo AS MissingAcctInfo
Thanks again to The Dixie Flatline,
Doug
June 2, 2011 at 9:41 am
You're welcome, Doug. This may be the first time I've ever been thanked for saying "NO".
Congratulations on making it happen with dynamic SQL. Good job.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply