May 12, 2010 at 10:49 am
Need a bit of help on the error/warning I get when running this bit of SQL against a database
that does not contain the COLUMN_NAME 'SupplyClass'.
if exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Unit_SRC_missionSupplies' AND
COLUMN_NAME = 'SupplyClass')
BEGIN
DECALRE @supClass as VARCHAR(100)
DECLARE @supName as VARCHAR(100)
DECLARE @supKey as int
--Grab the log keys
DECLARE logSupply_cursor CURSOR FOR
SELECT Supply_Class, Supply_Type, Supply_Type_Key
FROM Log_Supply_Types
OPEN logSupply_cursor
FETCH NEXT FROM logSupply_cursor into @supClass, @supType, supKey
WHILE (@@fetch_status <> -1)
BEGIN
UPDATE Unit_SRC_MissionSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName
UPDATE Unit_SRC_PopSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName
FETCH NEXT FROM logSupply_cursor into @supClass, @supType, supKey
END
CLOSE logSupply_cursor
DEALLOCATE logSupply_cursor
END
GO
If I run the following:
if exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Unit_SRC_missionSupplies' AND
COLUMN_NAME = 'SupplyClass')
print 'Found it'
ELSE
print 'not found'
GO
I get "not found" as expected.
But I think, during the parse/compile it checks/runs this section of SQL:
UPDATE Unit_SRC_MissionSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName
UPDATE Unit_SRC_PopSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName
And gives me error/warning messages that "SupplyClass" and "SupplyName" don't exist.
And.. in this case, against our development database, they don't.
So..... How do I prevent the error/warning message and/or correct the SQL so:
UPDATE Unit_SRC_MissionSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName
UPDATE Unit_SRC_PopSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName
is parsed but not executed unless "SupplyClass" exists?
Many thanks for any help provided.
Mark.....
May 12, 2010 at 11:08 am
You'll have to do it with dynamic SQL, I don't believe there is deferred named resolution for scripts.
CEWII
May 12, 2010 at 11:30 am
So.... something like this?
declare @updatestatment =
N'UPDATE Unit_SRC_PopSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName'
Execute sp_executesql @updatestatement
Mark....
May 12, 2010 at 11:38 am
streinm2 (5/12/2010)
So.... something like this?
declare @updatestatment =
N'UPDATE Unit_SRC_PopSupplies SET Supply_Type_Key = @supKey
WHERE SupplyClass = @supClass and
SupplyName = @supName'
Execute sp_executesql @updatestatement
Mark....
Closer to:
DECLARE @updatestatment nvarchar(4000)
SELECT @updatestatment = N'UPDATE Unit_SRC_PopSupplies SET Supply_Type_Key = '''
+ @supKey
+ ''' WHERE SupplyClass = '''
+ @supClass
+ ''' and SupplyName = '''
+ @supName
+ ''' '
EXECUTE ( @updatestatement )
CEWII
May 12, 2010 at 1:20 pm
Yes... "like" that:)
I did try and get the dynamic sql to work with a Cursor.... no luck there.
It was my last warning, and I wanted to put this pig to bed, so I just output a message for
the user to ignore the 207 message that is generated by the missing column.
Thanks for all your help.
Mark.....
May 12, 2010 at 1:27 pm
Do the variable declare outside the cursor..
CEWII
May 12, 2010 at 1:37 pm
I will re-look at what I tried to get working.
Thanks again.
Mark....
May 13, 2010 at 8:05 am
Ok.... got the cursor working.
Thought I would share an example of one way to do it.
if exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Unit_SRC_missionSupplies' AND
COLUMN_NAME = 'SupplyClass')
BEGIN
DECALRE @supClass as VARCHAR(100)
DECLARE @supName as VARCHAR(100)
DECLARE @logSupply_cursor CURSOR
EXEC sp_executesql
N'SET @logSupply_cursor = CURSOR FOR
SELECT DISTINCT SupplyClass, SupplyName
FROM Unit_SRC_MissionSupplies
UNION
SELECT DISTINCT SupplyClass, SupplyName
FROM Unit_SRC_PopSupplies;
OPEN @logSupply_cursor',
N'@logSupply_cursor cursor OUTPUT',
@logSupply_cursor OUTPUT
FETCH NEXT FROM @logSupply_cursor into @supClass, @supName
WHILE (@@fetch_status <> -1)
BEGIN
If NOT EXISTS(SELECT * FROM Log_Supply_Types
WHERE supply_class = @supClass and
supply_name = @supName)
BEGIN
INSERT into Log_Supply_Types
(Supply_Class,
Supply_Name,
Unit_Of_Measure,
Weight,
Changed_by,
Changed_time)
VALUES(@supClass,
@supName,
'kilo',
'1',
'DEV_3',
'GETDATE())
END
FETCH NEXT FROM @logSupply_cursor into @supClass, @supName
END
CLOSE @logSupply_cursor
DEALLOCATE @logSupply_cursor
END
GO
Hope it is helpful.
And thanks for all the help.
Mark....
May 13, 2010 at 8:14 am
You are welcome.
CEWII
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply