April 14, 2008 at 11:13 pm
I tried to use DDL statement in procedure. But it show error for using var in alter statement. I want to assign a same default value for nearly 10 columns in a table. So I used procedure for reusablity. Please clarify if its possible or ?.
Create Procedure tblbppr_default @def nvarchar(30)
As
Begin
Alter Table tblbppr Add Constraint df_title Default @def For title
End
Error:
Msg 112, Level 15, State 4, Procedure tblbppr_default, Line 4
Variables are not allowed in the ALTER TABLE statement.
April 14, 2008 at 11:52 pm
April 15, 2008 at 12:56 am
Thanks for reply. I tried the following but got error. Please rectify.
Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tblbppr Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End
execute df_bppr @de = N'YES'
Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'YES'.
Msg 128, Level 15, State 1, Line 1
The name "YES" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Thanks
Vijai
April 15, 2008 at 1:37 am
Vijai (4/15/2008)
Thanks for reply. I tried the following but got error. Please rectify.Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tblbppr Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End
execute df_bppr @de = N'YES'
Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'YES'.
Msg 128, Level 15, State 1, Line 1
The name "YES" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Thanks
Vijai
vijai,
After seeing your code, i think you did a mistake in the following code
Set @sstr = 'Alter Table tblbppr Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
you should type
Set @sstr = 'Alter Table tblbppr Add Constraint df_title Default '+ @de
sp_executesql (@sstr)
April 15, 2008 at 2:38 am
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'
******************
I created the procedure without any error. But when i execute the procedure it shows the following error:
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.
April 15, 2008 at 3:45 am
Vijai (4/15/2008)
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'
******************
I created the procedure without any error. But when i execute the procedure it shows the following error:
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.
see vijay
in your procedure the following is wrong
Exec sp_executesql @sstr, @de
you have to remove @de that is it should be the following
Exec sp_executesql (@sstr)
change your code and check
hopes it will work
April 15, 2008 at 5:08 am
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 + N' For title'
Exec sp_executesql (@sstr)
End
Msg 102, Level 15, State 1, Procedure df_bppr, Line 6
Incorrect syntax near '@sstr'.
--------
I tried, In this case it shows the above error while creating the procedure itself. Please run and check the code.
April 15, 2008 at 9:57 pm
Shall any one check and give the correct code????
April 15, 2008 at 10:25 pm
Vijai (4/15/2008)
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 + N' For title'
Exec sp_executesql (@sstr)
End
Msg 102, Level 15, State 1, Procedure df_bppr, Line 6
Incorrect syntax near '@sstr'.
--------
I tried, In this case it shows the above error while creating the procedure itself. Please run and check the code.
Yes vijai you are right, i also got same error. after running the query i found problem that you are query in @sstr is like this
alter table tbl add constraint df_little default YES FOR title
but it should be like this
alter table tbl add constraint df_little default 'YES' FOR title
you can see the difference second example is 'YES' including quotation. you have to try generate similar string (including quotation mark). i also dont know how to generate
April 15, 2008 at 10:34 pm
vijai
try this
Create table tbl(title nvarchar(40))
declare @sstr nvarchar(500)
set @sstr = 'alter table tbl add constraint df_little default ''' + 'yes' + ''' for title'
exec sp_executesql @sstr
hope it will work
April 16, 2008 at 1:18 am
I got it the below code dynamically assigns DEFAULT to any column:
Create table tbl(title nvarchar(40))
Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
If Not exists(select * from sys.objects where name = 'df_title')
begin
Set @sstr = 'Alter Table emp Add Constraint df_title Default '''+ @de + ''' For ename'
Exec sp_executesql @sstr
ok:
print 'ok'
end
else
begin
print 'Constraint df_title already exists'
set @sstr = 'Alter Table emp drop Constraint df_title'
exec sp_executesql @sstr
goto ok;
end
End
Execute df_bppr 'NoTitle';
:w00t: Thanks for all your efforts
April 16, 2008 at 2:13 am
Vijai (4/16/2008)
I got it the below code dynamically assigns DEFAULT to any column:Create table tbl(title nvarchar(40))
Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
If Not exists(select * from sys.objects where name = 'df_title')
begin
Set @sstr = 'Alter Table emp Add Constraint df_title Default '''+ @de + ''' For ename'
Exec sp_executesql @sstr
ok:
print 'ok'
end
else
begin
print 'Constraint df_title already exists'
set @sstr = 'Alter Table emp drop Constraint df_title'
exec sp_executesql @sstr
goto ok;
end
End
Execute df_bppr 'NoTitle';
:w00t: Thanks for all your efforts
viyay,
don't miss understand!!!!!!!
the sp will create constraint only for ename at first time and if you run the same sp it will delete the df_title ( which is default constraint for ename ). the sp create and delete the constaints when consequence execution.
April 16, 2008 at 2:42 am
Yes, I understand.
I just testing it. I will modify this and use it to change the default value of column by deleting the previous constraint and recreating it again with a new value.
April 16, 2008 at 3:53 am
Vijai (4/16/2008)
Yes, I understand.I just testing it. I will modify this and use it to change the default value of column by deleting the previous constraint and recreating it again with a new value.
ya well, i think you got the solution for the question you posted..
now you enhance your query fine
May 22, 2008 at 11:11 am
Thanks for the solution guys. I have wanted to write DDL scripts that use variables on several occasions but ended up finding another solution. I will use sp_executesql on the job this week!
Regards,
David:)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply