April 1, 2010 at 1:08 am
I want to add a column to a table in My SQL CE database by using the following command:
ALTER TABLE tblProducts ADD isDeleted bit
However I want to prevent the alter table command to be executed in case the column already exists.
I could not use IF Exist in SQL CE.
Anyone has an idea or solution for this?
Regards,
Vimal Panchal
April 14, 2010 at 2:42 pm
Try this, Do a select count(*) from the system tables looking for the column name in the table you want, if the count > 0 then the column exist
DECLARE @Counter as INT
SELECT @Counter = COUNT(*)
FROM sysColumns
WHERE Blahhhhhh
IF @Counter > 0 THEN
BEGIN
Alter Table
END
April 22, 2010 at 7:20 am
vimal_panchal (4/1/2010)
I want to add a column to a table in My SQL CE database by using the following command:ALTER TABLE tblProducts ADD isDeleted bit
However I want to prevent the alter table command to be executed in case the column already exists.
I could not use IF Exist in SQL CE.
Anyone has an idea or solution for this?
Regards,
Vimal Panchal
IF NOT EXISTS (SELECT *
FROM information_schema.columns
WHERE table_name = 'tblProducts'
AND column_name = 'isDeleted')
BEGIN
ALTER TABLE tblproducts ADD isDelete bit
END
April 22, 2010 at 7:32 am
Thanks to both of you for your reply but i want to do this in SQL Compact Edition database..which is not supporting DECLARE keyword as well as i can not start my query with IF......
Thanks
Vimal
April 23, 2010 at 7:59 am
Can you use TRY CATCH?
BEGIN TRY
ALTER TABLE tblproducts ADD isDelete bit
END TRY
BEGIN CATCH
END CATCH
June 14, 2010 at 4:34 am
Check this...
http://msdn.microsoft.com/en-us/library/ms174123(SQL.90).aspx
And you can use if condition in your application code. Generate data set from the query to check the column using Information_Schema and take decission at application level code.
Stored Procedures are NOT supported in SQL Server CE.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply