Dynamic Stored Procedure in SQL 2000

[SQLDTS2000]

Hi,
I'm using dynamic SP which looks something like this
create sp_select @var as
declare @sqlstatement
select @sqlstatement = 'select '
+ @var
+ 'convert(char(10), coalesce c.city, '') + coalesce(c.address, ''))'
+ 'from table1 c'
execute(@sqlstatement)
I have a problem with the concatenate part. I concatenate the select statement while i also want to use concatenate in the inner portion which i use in the convert statement. Do you know how to resolve this problem? I need that concatenate inside this select statement.
I use sql 2000 analyzer.

Thanks,
Charmine

 

[SQLSister]

My advice to you is write the select statement and get it to work with actual values instead of dynamic SQL and concatenation
Then write the dynamic SQl and instead of executing it put a Select @sqlstatement line of code in. Then when you run it you will see the sql it is creating. Compare it to the SQL you know works for the actual values and you will see where you need to adjust the dynamic slq. If you can't see it then, post both the workinbg statement and the dynamic SQL and we'll take a shot at it.

 

[cheyney]

try this:


create sp_select @var as
declare @sqlstatement
select @sqlstatement = 'select '
+ @var
+ 'convert(char(10), coalesce c.city, '''') + coalesce(c.address, ''''))'
+ 'from table1 c'
execute(@sqlstatement)


if you're printing out single quote marks in a string, you have to escape them with single quote marks beforehand.

cheyney