January 13, 2011 at 7:50 pm
if
exists (select * from information_schema.columns where
table_name='employee' and column_name='salary')
BEGIN
ALTER TABLE [employee]
ADD DEFAULT -2 FOR [salary]
ALTER TABLE [employee]
ALTER COLUMN [salary] [int] NOT NULL
When i am trying to run this query its showing an error default is already bound.
I should not get an error even if any one try to run this query again and again.
So can you please suggest what i can add in "if exists" statement for adding default value and not null to the column.
Thanks in advance!!!!
January 14, 2011 at 2:14 am
Just switch the statement to create the default with the alter column statement.
ALTER TABLE [employee]
ALTER COLUMN [salary] [int] NOT NULL
ALTER TABLE [employee]
ADD DEFAULT -2 FOR [salary]
If you want to have a more robust solution where you check if there is already a default on the column, try something like this:
use tempdb
go
create table employee (name varchar(100) ,
salary decimal (10,2) default (1000) --I hope for a big pay rise!
)
GO
if exists (select * from information_schema.columns where
table_name='employee' and column_name='salary')
BEGIN
print 'table exists'
--REMOVE DEFAULT
DECLARE @DEFAULTNAME NVARCHAR(200)
SELECT
@DEFAULTNAME = DC.name
FROM
sys.default_constraints DC
INNER JOIN sys.columns COLON DC.parent_column_id = COL.column_id
AND DC.parent_object_id = COL.object_id
WHERE
DC.parent_object_id = OBJECT_ID('employee')
AND COL.Name = 'salary'
IF @DEFAULTNAME IS NOT NULL
BEGIN
PRINT 'DEFAULT NOT NULL'
DECLARE @SQLSTR NVARCHAR(MAX)
SELECT @SQLSTR = N'ALTER TABLE employee DROP CONSTRAINT ' + @DEFAULTNAME
--PRINT @SQLSTR
EXEC sp_executesql @SQLSTR
END
if exists (select * from information_schema.columns where
table_name='employee' and column_name='salary')
BEGIN
ALTER TABLE [employee]
ALTER COLUMN [salary] [int] NOT NULL
ALTER TABLE [employee]
ADD DEFAULT -2 FOR [salary]
END
END
GO
DROP TABLE employee
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply