| Author |
 Topic  |
|
|
vijai_sarath
Starting Member
India
4 Posts |
 Posted - 04/15/2008 : 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
United Kingdom
3560 Posts |
Posted - 04/15/2008 : 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
India
4 Posts |
Posted - 04/15/2008 : 07:49:18
|
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 ','.
I altered the code, but while executing the proc in got the above error. Please correct.
Vijai |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3560 Posts |
Posted - 04/15/2008 : 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. |
Edited by - RickD on 04/15/2008 08:32:26 |
|
|
|
vijai_sarath
Starting Member
India
4 Posts |
Posted - 04/15/2008 : 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
United Kingdom
3560 Posts |
Posted - 04/15/2008 : 09:39:06
|
| Take the @de off the execute inside the SP. |
|
|
|
vijai_sarath
Starting Member
India
4 Posts |
Posted - 04/16/2008 : 00:04:25
|
quote:
Originally posted by RickD
Take the @de off the execute inside the SP.
Thanks Rick. It's working fine 
Vijai |
|
|
| |
Topic |
|
Variable in DDL statement in Procedure
