remove comma from dynamic sql - 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)

remove comma from dynamic sql

Author

Topic

jn-at-uk
Starting Member

20 Posts

Posted - 2005-04-30 : 04:53:30

Hi,

When i build my sql string, i need to remove my last , as i get the Incorrect syntax near ',' message. how do i remove the last comma?

DECLARE @sql1 nvarchar(4000), @Group nvarchar(4000), @Order nvarchar(4000)
select @sql1 = 'Select '
select @Group = 'Group by '
select @Order = 'Order by '
select @Val1 = 1, @Val2 =0, @Val3=1, @OrdVal1 = 1, @OrdVal2=1, @OrdVal3 = 1

If @Val1 = 1
Select @sql1 = @sql1 + 'pageref2,'
Select @Group = @Group + 'pageref2,'

If @Val2 = 1
Select @sql1 = @sql1 + 'originalref,'
Select @Group = @Group + 'originalref,'

If @Val3 = 1
Select @sql1 = @sql1 + 'ref,'
Select @Group = @Group + 'ref,'

If @ordVal1 = 1
Select @Order = @Order + 'kvalue,'

If @ordVal2 = 1
Select @Order = @Order + 'Time,'

select @sql1 = @sql1 + ' from #Temp ' + @Group + @Order
EXEC('EXEC sp_executesql N''' + @sql1+ '''')

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-04-30 : 05:50:52

Dynamic SQL is generally a bad from the security and performance perspective. Anyway, stripping out the last character from your statement:

select left(@foo, len(@foo)-1)

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources