May 20, 2013 at 11:53 am
IF EXISTS(SELECT 1 FROM sys.columns
WHERE name = 'CID AND object_id = Object_ID(Benin))
BEGIN
DELETE dm
FROM [ABC].[Dbo].[Benin] dm
JOIN #TempDim T
ON dm.CID=T.TempDimID
END
The table Benin Does not have the field CID , but it fails in validation/parsing before the query can run. I want the query to run only if the joining columns exists. any work around?
May 20, 2013 at 12:09 pm
May 20, 2013 at 12:12 pm
I don't think you need dynamic sql for this to work. You just need to get the right syntax. If you had posted the error message it would be very helpful.
IF EXISTS(SELECT 1 FROM sys.columns
WHERE name = 'CID' AND object_id = Object_ID('Benin'))
BEGIN
DELETE dm
FROM [ABC].[Dbo].[Benin] dm
JOIN #TempDim T
ON dm.CID=T.TempDimID
END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2013 at 12:56 pm
Sean Lange (5/20/2013)
I don't think you need dynamic sql for this to work. You just need to get the right syntax. If you had posted the error message it would be very helpful.
IF EXISTS(SELECT 1 FROM sys.columns
WHERE name = 'CID' AND object_id = Object_ID('Benin'))
BEGIN
DELETE dm
FROM [ABC].[Dbo].[Benin] dm
JOIN #TempDim T
ON dm.CID=T.TempDimID
END
Sean, just a question here, but how do you get around not using dynamic sql if the problem you are trying to solve includes using a query where a column in a table may not exist. If the column does not exist when the query is parsed, the query fails. The only way I know around this issue is to use dynamic sql to build the sql that is subsequently run using exec or sp_executesql.
May 20, 2013 at 2:03 pm
Lynn Pettis (5/20/2013)
Sean Lange (5/20/2013)
I don't think you need dynamic sql for this to work. You just need to get the right syntax. If you had posted the error message it would be very helpful.
IF EXISTS(SELECT 1 FROM sys.columns
WHERE name = 'CID' AND object_id = Object_ID('Benin'))
BEGIN
DELETE dm
FROM [ABC].[Dbo].[Benin] dm
JOIN #TempDim T
ON dm.CID=T.TempDimID
END
Sean, just a question here, but how do you get around not using dynamic sql if the problem you are trying to solve includes using a query where a column in a table may not exist. If the column does not exist when the query is parsed, the query fails. The only way I know around this issue is to use dynamic sql to build the sql that is subsequently run using exec or sp_executesql.
That runs just fine on my instance right now and I can assure you I don't have the ABC database or the tables referenced. If I were to attempt to run the delete it would throw all sorts of errors.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2013 at 2:07 pm
Sean Lange (5/20/2013)
Lynn Pettis (5/20/2013)
Sean Lange (5/20/2013)
I don't think you need dynamic sql for this to work. You just need to get the right syntax. If you had posted the error message it would be very helpful.
IF EXISTS(SELECT 1 FROM sys.columns
WHERE name = 'CID' AND object_id = Object_ID('Benin'))
BEGIN
DELETE dm
FROM [ABC].[Dbo].[Benin] dm
JOIN #TempDim T
ON dm.CID=T.TempDimID
END
Sean, just a question here, but how do you get around not using dynamic sql if the problem you are trying to solve includes using a query where a column in a table may not exist. If the column does not exist when the query is parsed, the query fails. The only way I know around this issue is to use dynamic sql to build the sql that is subsequently run using exec or sp_executesql.
That runs just fine on my instance right now and I can assure you I don't have the ABC database or the tables referenced. If I were to attempt to run the delete it would throw all sorts of errors.
It runs fine if you don't have the table, because of deferred compile. If you have the table but not the column, it fails with an invalid column error. At parsing time the table is allowed to not exist, but if the table does exist all columns referenced must exist.
CREATE TABLE SomeOldTable ( Col1 INT )
GO
IF EXISTS ( SELECT 1
FROM sys.columns
WHERE name = 'ColumnDoesNotExist'
AND object_id = OBJECT_ID('SomeOldTable') )
DELETE FROM SomeOldTable
WHERE ColumnDoesNotExist = 42
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ColumnDoesNotExist'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2013 at 2:16 pm
GilaMonster (5/20/2013)
Sean Lange (5/20/2013)
Lynn Pettis (5/20/2013)
Sean Lange (5/20/2013)
I don't think you need dynamic sql for this to work. You just need to get the right syntax. If you had posted the error message it would be very helpful.
IF EXISTS(SELECT 1 FROM sys.columns
WHERE name = 'CID' AND object_id = Object_ID('Benin'))
BEGIN
DELETE dm
FROM [ABC].[Dbo].[Benin] dm
JOIN #TempDim T
ON dm.CID=T.TempDimID
END
Sean, just a question here, but how do you get around not using dynamic sql if the problem you are trying to solve includes using a query where a column in a table may not exist. If the column does not exist when the query is parsed, the query fails. The only way I know around this issue is to use dynamic sql to build the sql that is subsequently run using exec or sp_executesql.
That runs just fine on my instance right now and I can assure you I don't have the ABC database or the tables referenced. If I were to attempt to run the delete it would throw all sorts of errors.
It runs fine if you don't have the table, because of deferred compile. If you have the table but not the column, it fails with an invalid column error. At parsing time the table is allowed to not exist, but if the table does exist all columns referenced must exist.
CREATE TABLE SomeOldTable ( Col1 INT )GO
IF EXISTS ( SELECT 1
FROM sys.columns
WHERE name = 'ColumnDoesNotExist'
AND object_id = OBJECT_ID('SomeOldTable') )
DELETE FROM SomeOldTable
WHERE ColumnDoesNotExist = 42
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ColumnDoesNotExist'.
That makes perfect sense. Thanks Gail.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply