If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql