Creating a default constraint

Question

Creating a default constraint

kalikal

Old Hand

Points: 303
More actions
February 6, 2002 at 6:39 am

#78203

Hi folks
I am trying to create a proc that will create default constraints for all the fields in the database.
I can do this for numerics and logicals but can't get the syntax right for char ...
I am using something like..
IF @columntype = 175 SET @defaulttype = '(' + SPACE(1) + ')'
exec ('ALTER TABLE ' + @tablename + ' ADD CONSTRAINT [DF_' + @tablename + '_' + @columnname + '] ' + 'DEFAULT ' + @defaulttype + ' FOR [' + @columnname + ']')
but I get an error saying
Incorrect syntax near ')'.
I only started working with T-SQL yesterday, so I guess I must be doing something pretty silly ...
Any ideas ?
Thanks
Kal
PS I just previewed this msg and noticed the 'plus' symbol is missing from the syntax .. it is there and the code works if I do SET @defaulttype = '(0)' ...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Off hand I don't see any issues, but I generally try to build the dynamic SQL string outside of the EXEC statement. Try declaring an nvarchar variable like:

DECLARE @sql nvarchar(1000)

and then setting the @sql variable to your concatenated string:

SET @sql = 'ALTER TABLE ' + @tablename + ' ADD CONSTRAINT [DF_' + @tablename + '_' + @columnname + '] ' + 'DEFAULT ' + @defaulttype + ' FOR [' + @columnname + ']'

Then output the @sql to make sure it's building the statement you want using a SELECT @sql or a PRINT @sql. If it is, then remove the output and simply go to EXEC(@SQL). See if that works for you.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

I think you "have" to build the string outside don't you? Shows how often I used exec()!

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

The following works:



DECLARE @Table sysname

SET @Table = 'Customers'
EXEC('Use Northwind; SELECT * FROM ' + @Table)
But I admit I've had problems at times concatenating strings for dynamic SQL queries.
K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 4

What line was it barking about? In QueryAnalyzer you can double click the error message and it put you on the line it is barking about (sometimes not right).

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 5

quote:
IF @columntype = 175 SET @defaulttype = '(' + SPACE(1) + ')'

How about this:

IF @columntype = 175 SET @defaulttype = '(''' + SPACE(1) + ''')'

The extra quotes will get stripped later on your processes leaving you with a your desired space.