Alter Column - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Alter Column

Author

Topic

davidc
Starting Member

26 Posts

Posted - 2007-09-27 : 09:11:52

I want to either drop a column if all values are NULL or reduce column width to max length. I get an error

Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@lcColwidth'.

What am I doing wrong?

IF (SELECT COUNT(BatchNbr) FROM [DBO].[SF_PROCESS]) = 0
BEGIN
ALTER TABLE [DBO].[SF_PROCESS]
DROP COLUMN BatchNbr
END
ELSE
DECLARE @lcColwidth int
SELECT @lcColwidth = (SELECT MAX(LEN(BatchNbr)) FROM [DBO].[SF_PROCESS])
ALTER TABLE [DBO].[SF_PROCESS]
ALTER COLUMN BatchNbr VARCHAR (@lcColwidth) null

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 09:16:02

need to use some dynamic sql to accomplish that.

ALTER COLUMN BatchNbr VARCHAR (@lcColwidth) null

that don't like the variable for the size declaration.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

davidc
Starting Member

26 Posts

Posted - 2007-09-27 : 09:22:05

Not much help.

davidc
Starting Member

26 Posts

Posted - 2007-09-27 : 10:05:21

Surely one of you experts know the answer !!

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:07:58

Why, what's wrong with the answer Don gave you?

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-27 : 10:11:33

He told you the answer...dynamic sql.

Delcare a variable say @sql varchar(1000) and set it = 'all your alter stuff...varchar('+@lcColwidth+')'. Then exec (@sql). What you are currently doing is not possible. It doens't accept the value from your variable as a column width. Instead you have to use dynamic sql. It would be like having a variable @tablename and trying to say - select * from @tablename...not possible.

davidc
Starting Member

26 Posts

Posted - 2007-09-27 : 10:44:49

Because the code is already running in dynamic sql as part of a stored procedure

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 11:32:25

Well then you need to change the

ALTER COLUMN BatchNbr VARCHAR (@lcColwidth) null

snippet to be something like:

...
ALTER COLUMN BatchNbr VARCHAR (' + CONVERT(varchar(20), @lcColwidth) + ') null
...

so that the actual value for @lcColwidth is in the dynamic SQL.

Or am I missing something?

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 12:38:43

quote:
Originally posted by davidc

Because the code is already running in dynamic sql as part of a stored procedure

Then can we see the stored proc in its entirety? We have been given straight SQL, not dynamic. The link in my sig will tell you how to properly ask a question so that our brain trusts can answer it in a very short time.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:56:16

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources