SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Variable in DDL statement in Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijai_sarath
Starting Member

India
4 Posts

Posted - 04/15/2008 :  07:23:55  Show Profile  Reply with Quote
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
3608 Posts

Posted - 04/15/2008 :  07:27:20  Show Profile  Reply with Quote
You need more quotes, something like:

Set @sstr = N'Alter Table tbl Add Constraint df_title Default ''' + @de + ''' For title'
Go to Top of Page

vijai_sarath
Starting Member

India
4 Posts

Posted - 04/15/2008 :  07:49:18  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 04/15/2008 :  08:30:46  Show Profile  Reply with Quote
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
Go to Top of Page

vijai_sarath
Starting Member

India
4 Posts

Posted - 04/15/2008 :  09:18:45  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 04/15/2008 :  09:39:06  Show Profile  Reply with Quote
Take the @de off the execute inside the SP.
Go to Top of Page

vijai_sarath
Starting Member

India
4 Posts

Posted - 04/16/2008 :  00:04:25  Show Profile  Reply with Quote
quote:
Originally posted by RickD

Take the @de off the execute inside the SP.



Thanks Rick. It's working fine

Vijai
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000