Concatenation Issue

[ChopinFan]

I need to concatenate into a string used as a select statement that creates a view, and I'm finding it doesn't like the syntax I am using. I already use the same syntax in a case when construct with no problem.

Declare @SQL as varchar(2000)
Set @SQL = 'Select FieldName From...' etc
Declare @MyView as varchar (2000)
Set @MyView = 'alter view ViewName as' +@SQL
Exec (@MyView)

Inside the @SQL variable, this syntax works fine:
CASE WHEN Field_A = 1 then '''+'Yes'+''' else '''+'No'+''' end as FieldAlias

When I try to concatenate into a field (trying to change values such as AA1234567 into AA-123-4567), this syntax doesn't work:
LEFT(Field_B,2)'''+'-'+'''Substring(Field_B,3,3)'''+'-'+'''Right(Field_B,4) as Field_B

I get an error saying it's an invalid SQL statement. What am I doing wrong?

 

[gmmastros]

I suspect that if you would change the Exec(@MyView) to Print @MyView, the reason would be obvious.

Running your query results in...

Declare @SQL as varchar(2000)
Set @SQL = 'Select FieldName From...'
Declare @MyView as varchar (2000)
Set @MyView = 'alter view ViewName as' +@SQL
Print (@MyView)

[tt][blue]alter view ViewName asSelect FieldName From...[/blue][/tt]

There is no space between as and select

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChopinFan]

Thanks George, but I actually do have a space there in my actual sproc. It just didn't make it into my example above. Good catch, but without the concatenation, the whole thing runs fine.

 

[ChopinFan]

Well, I got around the problem by splitting the field into three pieces in the query and inserting the dashes in the application layer instead. Nevertheless, if it would be helpful to know how to do this in the query for future reference if anyone knows how. Thanks!

 

[gmmastros]

You want to know how to put dashes in to the data?

Like this...

Declare @Temp VarChar(50)
Set @Temp = 'AA1234567'

Select Stuff(@Temp, 3, 0, '-')

Since you want 2 dashes...

Declare @Temp VarChar(50)
Set @Temp = 'AA1234567'

Select Stuff(Stuff(@Temp, 5, 0, '-'), 3, 0, '-')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChopinFan]

Excellent. I had to add additional single quotes to use this inside the @SQL string, but it works.

Set @SQL = 'SELECT Stuff(Stuff(FieldName, 5, 0, ''-''), 3, 0, ''-'') AliasName' etc

Thanks!