Variable in DDL statement in Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Variable in DDL statement in Procedure

Author

Topic

vijai_sarath
Starting Member

4 Posts

Posted - 2008-04-15 : 07:23:55

Create table tbl(title nvarchar(40))

Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End

Execute df_bppr @de = 'NoTitle'

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

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.
Msg 128, Level 15, State 1, Line 1
The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

--------------------

I created the procedure without any error. But when i execute the procedure it shows the above error

I want to create a proc which dynamically change the default value for more than one column with same default value.

Vijai

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-15 : 07:27:20

You need more quotes, something like:

Set @sstr = N'Alter Table tbl Add Constraint df_title Default ''' + @de + ''' For title'

vijai_sarath
Starting Member

4 Posts

Posted - 2008-04-15 : 07:49:18

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-15 : 08:30:46

Why did you add the , to the execute???

Surely you can spot syntax errors on your own? all you have to do is click on the error in MS.

vijai_sarath
Starting Member

4 Posts

Posted - 2008-04-15 : 09:18:45

Execute df_bppr @de='NoTitle';
Execute df_bppr @de 'NoTitle';
Execute df_bppr 'NoTitle';

I tried everything but still got the same error. Please run the code and check.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.

Vijai

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-15 : 09:39:06

Take the @de off the execute inside the SP.

vijai_sarath
Starting Member

4 Posts

Posted - 2008-04-16 : 00:04:25