Syntaxproblem: concatenating Strings and Variables

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

Transact-SQL (2005)

Syntaxproblem: concatenating Strings and Variables

Author

Topic

DMarz
Starting Member

4 Posts

Posted - 2010-09-09 : 08:04:31

Hi,

could someone please help me find the wrong Syntax here and correct it?

I get following Error: "Incorrect syntax near '@null'."

declare @sql varchar(max)

declare @RowCnt int
declare @MaxRows int

DECLARE @column AS varchar(max)
DECLARE @type AS varchar(max)
DECLARE @null AS varchar(max)
DECLARE @row AS int

select @RowCnt = 1

select @MaxRows=count(*)
from dbo.TABX

set @sql = 'CREATE TABLE [schema].[tab]('

while @RowCnt <= @MaxRows
begin

select

@column = columnx,
@type = type,
@null = nullable,
@row = row

from schema.TABX

where @row = @RowCnt

set @sql = coalesce(@sql + '','') + '[' + @column + '] [' + @type +']' @null ','

Select @RowCnt = @RowCnt + 1

end

set @sql = coalesce(@sql + '','')+ ') ON PART1;'

EXECUTE (@SQL)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-09 : 08:11:07

PUT + on the both the side of @null

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-09-09 : 08:15:41

Try this,

declare @sql varchar(max)

declare @RowCnt int
declare @MaxRows int

DECLARE @column AS varchar(max)
DECLARE @type AS varchar(max)
DECLARE @null AS varchar(max)
DECLARE @row AS int

select @RowCnt = 1

select @MaxRows=count(*)
from dbo.TABX

set @sql = 'CREATE TABLE [schema].[tab]('

while @RowCnt <= @MaxRows
begin

select

@column = columnx,
@type = type,
@null = nullable,
@row = row

from schema.TABX

where @row = @RowCnt

set @sql = coalesce(@sql + '','') + '[' + @column + '] [' + @type +']' + @null + ','

Select @RowCnt = @RowCnt + 1

end

set @sql = coalesce(@sql + '','')+ ') ON PART1;'

EXECUTE (@SQL)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

DMarz
Starting Member

4 Posts

Posted - 2010-09-09 : 08:29:40

thanks a lot!

that was fast =)

unfortunately I got another one now:
Incorrect syntax near ')'.

and it's not concatenating anything...

it sets @sql
first time to "'CREATE TABLE [schema].[tab]('"
within the loop it seems to be null
and after the loop it's "') ON PART1;'"

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 08:45:52

quote:
Originally posted by DMarz

Hi,

could someone please help me find the wrong Syntax here and correct it?

I get following Error: "Incorrect syntax near '@null'."

declare @sql varchar(max)

declare @RowCnt int
declare @MaxRows int

DECLARE @column AS varchar(max)= ' '
DECLARE @type AS varchar(max)= ' '
DECLARE @null AS varchar(max)= ' '
DECLARE @row AS int

select @RowCnt = 1

select @MaxRows=count(*)
from dbo.TABX

set @sql = 'CREATE TABLE [schema].[tab]('

while @RowCnt <= @MaxRows
begin

select

@column = columnx,
@type = type,
@null = nullable,
@row = row

from schema.TABX

where @row = @RowCnt

set @sql = coalesce(@sql + '','') + '[' + @column + '] [' + @type +']' @null ','

Select @RowCnt = @RowCnt + 1

end

set @sql = coalesce(@sql + '','')+ ') ON PART1;'

EXECUTE (@SQL)

try with the above changes marked in green near the declare statement.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

DMarz
Starting Member

4 Posts

Posted - 2010-09-09 : 09:04:02

Cannot assign a default value to a local variable.

no output

tried

set @column = ''

also... didn't change anything

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 09:29:12

What does Print(@SQL) give?
Remove EXEC(@SQL) for that.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

DMarz
Starting Member

4 Posts

Posted - 2010-09-09 : 09:33:16

CREATE TABLE [schema].[tab]([ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,[ ] [ ] ,) ON PART1;

Sachin.Nand

2937 Posts

Posted - 2010-09-27 : 06:12:54

What is the value of @MaxRows?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources