• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 883
  • Last Modified:

Drop and Create Default Constraint using Dynamic SQL

Hello Experts,

I am trying to drop a constraint on an existing table in order to add a new default value on a column.

The following code works fine but will not accept a parameter.  I must be able to pass a parameter to the SQL statement.


--drop constraint
ALTER TABLE dbo.table1 DROP CONSTRAINT def_column1

--add constraint
ALTER TABLE dbo.table1
 ADD CONSTRAINT def_column1
 DEFAULT 'test'
 FOR colum1

I think I need to use dynamic SQL in order to pass params here. I am now trying to accomplish this (drop and create a default constraint) in the SPROC below but I cannot get it to work.

CREATE PROCEDURE AddDefaultColumnSP
 @Column1 varchar(10),
@TableName nvarchar(64)= 'dbo.table1',
 @SQL VARCHAR(1000)= 'dynamicSQLString '
 AS


--Dynamic SQL
SET @TableName  = 'dbo.table1'
      
--Drop constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'DROP CONSTRAINT' + 'def_Column1'      
      
--add constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'

EXEC(@SQL)      

GO

Can anyone see what I am doing wrong here??

Thanks
0
Saxitalis
Asked:
Saxitalis
  • 6
  • 4
  • 2
  • +1
1 Solution
 
dbbishopCommented:
If this is the actual code, for one thing, you are never dropping the constraint. You use
SET @SQL = :ALTER TABLE... drop contraint,
then turn around and
SET @SQL = 'ALTER TABLE... add constraint.
I think you want your second SET to be SET @SQL = @SQL + 'ALTER TABLE... add constrint.

Secondly, there are no spaces between parts of the SET, so the first one would translate to:
ALTER TABLEdbo.table1DROP CONSTRAINTdef_column1
same for the second.
0
 
Jai STech ArchCommented:
there seems to be a typo in your SP code...
you are not leaving any SPACE before your single quotes when createing the SQL
like
SET @SQL = 'ALTER TABLE' + @TableName + 'DROP CONSTRAINT' + 'def_Column1'      
it has to be
SET @SQL = 'ALTER TABLE '  + @TableName + ' DROP CONSTRAINT ' + 'def_Column1'  
0
 
dbbishopCommented:
jaiganeshsrinivasan: I thought that is what I said?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SaxitalisAuthor Commented:
Sorry for my "greeness" here..

OK:
I am able to pass in a param for @Column1 to set the default value for column1 like this:
--this works
SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT '''+  @Column1 + ''' FOR column1' -

 But If I try to add a Constraint first like this, passing in a value for the param:
SET @SQL = @SQL + 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'
--this does not work with these error msgs :
--Line 1: Incorrect syntax near 'TABLEdbo'.
--Unclosed quotation mark before the character string ' FOR Column1'.

Thanks for your patience :)




0
 
frankyteeCommented:
i think you would have to execute them seperately
--Drop constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'DROP CONSTRAINT' + 'def_Column1'      
EXEC(@SQL)      

--now add constraint      
SET @SQL = 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'

EXEC(@SQL)

-------------------------------------------------------------
building a string with several ALTER table statements may not be valid (for constraints)
you can try seperating them with commas (like when you are adding fields etc) but i dont think it would work for constraints.
but you can try below:

SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT '''+  @Column1 + ''' FOR column1' -
--note remove ALTER TABLE for the second constraings
SET @SQL = @SQL + ',  ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT'
EXEC(@SQL)

let me know how it goes
0
 
frankyteeCommented:
typo carried over from your original post, there should be spaces between the arguments etc



--Drop constraint
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + ' def_Column1 '      
EXEC(@SQL)      

--now add constraint      
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT ' + @Column1 + ' FOR Column1 '

etc
...

or if attempting to exec in one statement

SET @SQL = 'ALTER TABLE  ' + @TableName + ' WITH NOCHECK ADD DEFAULT '+  @Column1 + '  FOR column1 '
--note remove ALTER TABLE for the second constraints
SET @SQL = @SQL + ',  ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT '
EXEC(@SQL)

0
 
dbbishopCommented:
What are the values you are passing for
@TableName and @Column1

After the statement:
  SET @SQL = @SQL + 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'

add:
  PRINT @SQL

and copy and paste the result back into a reply.
0
 
SaxitalisAuthor Commented:
Hi dbbishop - Thanks for the replies.

1. First I ran SPROC below to Add a constraint only

CREATE PROCEDURE AddDefaultColumn1SP
@Column1 varchar(10)  = 'Test',
@TableName nvarchar(64)= 'dbo.table1',
 @SQL VARCHAR(1000)= 'dynamicSQLString '
 AS

-- this runs without error but it does not add a default value to Column1 in table1
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT ' + @Column1 + ' FOR Column1 '

PRINT @SQL
GO  

Result:

Print @SQL output:
ALTER TABLE dbo.table1 ADD CONSTRAINT  def_Column1  DEFAULT Test FOR Column1  

SPROC runs without error but doesnt appear to alter Column1 in table1. Test was not added as a default value but <database default> was added for Collation (Not sure what this means).

2. When I run the Drop Constraint statement, it fails with the message along the lines of No Constraint to Drop

It seems like step one is not adding the constraint def_Column1 and is not adding the default value of Test.

Thanks!
0
 
dbbishopCommented:
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT (''' + @Column1 + ''') FOR Column1'
0
 
SaxitalisAuthor Commented:
OK - I ran this line:
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT (''' + @Column1 + ''') FOR Column1'

Output from PRINT looks like this:
ALTER TABLE dbo.table1 ADD CONSTRAINT  def_Column1  DEFAULT ('Test') FOR Column1

Default value is not added to Column1. Also, I can run the SPROC multiple times without "Already a CONSTRAINT bound" error.

Thanks
0
 
dbbishopCommented:
If it is not working, I have no idea what is working, because I set @TableName to the name of a valid table in my database, changed the column name (Column1) to a column in that table, and ran it exactly how I present it above, and the constraint was added. What is the datatype of Column1 in your table?
0
 
SaxitalisAuthor Commented:
I have it working now. I was missing EXEC(@SQL) after the ADD CONSTRAINT string.

Thanks for spelling it out for me!!

 
0
 
dbbishopCommented:
Glad to help. Good luck!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now