Problem Creating Table? - 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

General SQL Server Forums

New to SQL Server Programming

Problem Creating Table?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-12 : 04:04:38

Hi all

I am trying to create a reference table based upon the MAX(DATALENGTH) of the fields found within another table. (the table created will only be read by users - not written to or amended in any way).

I am testing using the following:-


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CreateTest_a')
BEGIN
	DROP TABLE CreateTest_a
END
--create the table to receive the data
CREATE TABLE	[CreateTest_a]
		(
			PDate VARCHAR(50),
			rrec VARCHAR(30),
			pfix VARCHAR(30),
			num VARCHAR(10)
		)

INSERT CreateTest_a
VALUES ('Test length of first field','length of field 2','length of next 1','last one')

DECLARE @pd int, @rlr int, @pfx int, @nm int

--check to see what the length of each field is

SET @pd = (SELECT MAX(DATALENGTH([PDate])) 
FROM CreateTest_a)
print @pd

SET @rlr = (SELECT MAX(DATALENGTH([rrec]))
FROM CreateTest_a)
print @rlr

SET @pfx = (SELECT MAX(DATALENGTH([pfix]))
FROM CreateTest_a)
print @pfx

SET @nm = (SELECT MAX(DATALENGTH([num]))
FROM CreateTest_a)
print @nm

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CreateTest1')
BEGIN
	DROP TABLE CreateTest1
END
--create the table to receive the data
CREATE TABLE	dbo.[CreateTest1]
		(
			PDate VARCHAR(@pd),
			rrec VARCHAR(@rlr),
			pfix VARCHAR(@pfx),
			num VARCHAR(@nm),
		)

INSERT CreateTest1

SELECT [PDate] ,[rrec] ,[pfix] ,[num] 
FROM CreateTest_a

The error I am getting is:-

Msg 102, Level 15, State 1, Line 42
Incorrect syntax near '@pd'.

Any ideas of where I am going wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 04:08:46

It's your CREATE TABLE statement.

You can't create a table with precision for VARCHAR columns dynamically. You can use the MAX keyword in SQL Server 2005.
If you want to create table with a variable precision of columns, use dynamic sql!

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 04:09:37

[code]--create the table to receive the data

declare @sql varchar(1000)

set @sql = '
CREATE TABLE dbo.[CreateTest1]
(
PDate VARCHAR(' + str(@pd) + '),
rrec VARCHAR(' + str(@rlr) + '),
pfix VARCHAR(' + str(@pfx) + '),
num VARCHAR(' + str(@nm) + ')
)'
exec (@sql)[/code]

E 12°55'05.63"
N 56°04'39.26"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-12 : 04:12:53

You can create Dynamic queries like this

CREATE TABLE dbo.[CreateTest1]
(
PDate VARCHAR(@pd),
rrec VARCHAR(@rlr),
pfix VARCHAR(@pfx),
num VARCHAR(@nm),
)

The Format is as follows

1.Exex("CREATE TABLE dbo.[CreateTest1](PDate VARCHAR("+@pd+")....")

2. Declare @str as varchar(1000)
set @str="CREATE TABLE dbo.[CreateTest1](PDate VARCHAR("+@pd+")...."

Exec(@str)

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-03-12 : 04:29:34

Thanks all, problem solved!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources