August 12, 2008 at 12:20 pm
I have multiple databases on the server that have slightly different structures. I need to create a procedure that will act on each databases appropriately based on its structure.
Here's what I have so far:
--------------DETERMINE THE STRUCTURE AND SET FLAG VALUES APPROPRIATELY------------------
Declare @CallsFlag bit,
@SubjectFlag bit,
@DBName sysname
If OBJECT_ID('Calls','U') is not null --Calls table exists
SET @CallsFlag=1
ELSE --Calls table doesn't exist
SET @CallsFlag=0
IF EXISTS (
SELECT 1 from syscolumns WHERE ID = object_id('Calls') and name = 'Subject') --Subject field exists
Set @SubjectFlag=1
Else --Subject field doesn't exist
Set @SubjectFlag=0
---------------------------------ACTION TO TAKE BASED ON VALUES OF FLAGS--------------------
IF @CallsFlag=1 AND @SubjectFlag=1
Begin
Select Subject from Calls
End
Else If @CallsFlag=1 and @SubjectFlag=0
Print 'Calls=1 and Subject=0'
Else
Print 'Both Flags=0'
-------------------------------------------------------------------------------------------------
If I run this code against a database that has both the Calls table and the Subject field, all is OK. If I run it against a database where both are not present I would expect the IF statement to be ignored, jumping to either the ELSE IF or the ELSE statements. Unfortunately this is not the case...I get an error "Invalid Column Name 'Subject'". What am I doing wrong?
August 12, 2008 at 1:03 pm
If you are running this as a query in SSMS it is trying to create an execution plan and THAT is when it is failing in the database where the column does not exist.
One way to fix that is to use dynamic sql. Build your select and use Exec (@SQL) or sp_execute_sql.
One thing I did notice is that you don't need to check if the column exists if the table does not exist so if OBject_ID('calls', 'U') is null then you should exit out anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2008 at 2:52 pm
Actually this will be a stored procedure (I hope) and will have a lot more going on in the finished product. I just figured why complicate things until I can get the basic code running. There are 3 scenarios I need to deal with: Calls table exists AND Subject field exists, Calls table exists AND Subject field does NOT exist, neither Calls table nor Subject field exist. Here's the procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sample
AS
BEGIN
SET NOCOUNT ON;
Declare @CallsFlag bit,
@SubjectFlag bit
If OBJECT_ID('Calls','U') is not null
SET @CallsFlag=1
ELSE
SET @CallsFlag=0
IF EXISTS (SELECT 1 from syscolumns WHERE ID = object_id('Calls') and name = 'Subject')
Set @SubjectFlag=1
Else
Set @SubjectFlag=0
IF @CallsFlag=1 AND @SubjectFlag=1
Select Subject from Calls
Else If @CallsFlag=1 and @SubjectFlag=0
Print 'Calls table exists, Subject field does not'
Else
Print 'Neither Calls table nor Subject field exists'
END
GO
Running this in a database that contains the Subject field works fine, unfortunately it fails when run on a database without the Subject field. I don't understand why the Select statement would be evaluated at all since the IF statement fails...
August 12, 2008 at 3:27 pm
The error occurs because the table DOES exist and SQL Server is checking dependencies. If you were to create the procedure before the table you would be fine, until you had to change the procedure. Here is a procedure that does what you want and, I think, is simpler and easier to read:
[font="Courier New"]ALTER PROCEDURE Test
AS
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(1000)
IF OBJECT_ID('Calls','U') IS NOT NULL -- table exists
BEGIN -- check for column
IF EXISTS (SELECT 1 FROM syscolumns WHERE ID = OBJECT_ID('Calls') AND name = 'Subject')
BEGIN -- column exists so select column from table
SET @sql = N'Select Subject From Calls'
END
ELSE -- column does not exist so do something different
BEGIN
PRINT 'Calls table exists, Subject field does not'
END
/* using sp_executesql protects against SQL Injection, Allows use of parameters in
in the sql statement, and more likely creates a re-usable plan */
EXEC sp_executesql @sql
END
ELSE -- table does not exist so column can't exist
BEGIN
PRINT 'Neither Calls table nor Subject field exists'
END
[/font]
Notice I use the BEGIN END blocks for all my control of flow statements, even when there is only a single command following. This makes it easier to see what is being executed in the flow and also means that if I go back in and add statements to that section I won't execute them by accident because I did not wrap them in the BEGIN END. I've seen the lack of a BEGIN END block cause bugs many times.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2008 at 7:26 am
Thanks Jack. I'm still not clear on why the code is evaluated...I'm self taught and guess I need to do some more research on this...but I GREATLY appreciate the time you took to rewrite my sample so I'd have the correct foundation for my procedure. I'm so glad there are people like you out there that are willing to help people like me. Have a wonderful day!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply