Concatinating the strings - 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

SQL Server Development (2000)

Concatinating the strings

Author

Topic

vin
Starting Member

26 Posts

Posted - 2003-03-13 : 05:29:04

hi folks,

i am trying to concatenate the values into a string,
by reading the ename column from emp table.

The sp gets created, but not able to print the o/p.
How can i do this ?

(This is a example, to simulate the actual requirement)

alter procedure emp_sp
as
declare @a varchar(500)
declare @b varchar(700)
declare emp_cus cursor for
select distinct(ename) from emp
open emp_cus
while @@FETCH_STATUS=0
begin
fetch next from emp_cus into @a
select @b=@b + ' ' +@a
print @b
end
close emp_cus
deallocate emp_cus
go

exec emp_sp

This displays no output.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-13 : 06:10:33

alter procedure emp_sp
as
declare @b varchar(700)
select @b = coalesce(@b + ' ','') + ename
from
(select ename = distinct(ename) from emp) as a
select @b
go

exec emp_sp

you might want instead

alter procedure emp_sp
@b varchar(700) output
as
select @b = coalesce(@b + ' ','') + ename
from
(select ename = distinct(ename) from emp) as a
go

declare @s varchar(700)
exec emp_sp @s output
select @s

(oops , instead of +)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/13/2003 10:49:52

vin
Starting Member

26 Posts

Posted - 2003-03-13 : 09:33:05

Hi,
Just realted to the above example,

Using the selected values of the ename column, i will concatinte into a string (Which contains value+datataype+ ',') which i should use to create a table dynamically.
The problem i am facing is, the last column value should not have a ',' in it. For eg, john varchar(20), this ',' should not be there for last value, but for all other columns it should be there. How can get this done.
Is there any keyword with cursor, which can be used to findout beforehand how many values are there in the cursor, so that i can just select the last record and trim the comma for that...

or is there any logic to get this done.

could somebody guide me about the same

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-13 : 10:48:27

Why do you want to use a cursor?
for ename values john, jim

declare @b varchar(700)
select @b = coalesce(@b + ', ','') + ename + ' varchar(20)'
from
(select ename = distinct(ename) from emp) as a

should give

'john varchar(20), jim varchar(20)'
in @b.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/13/2003 10:49:00

vin
Starting Member

26 Posts

Posted - 2003-03-14 : 00:59:13

Hey thanx nr,
but i am getting this error

---------------
alter procedure emp_sp
as
declare @b varchar(700)
select @b = coalesce(@b + ' ','') + ename
from
(select ename=distinct(ename) from emp) as a
select @b
go
-------------------
Server: Msg 156, Level 15, State 1, Procedure emp_sp, Line 6
Incorrect syntax near the keyword 'distinct'.
-----------------------------------------

Edited by - vin on 03/14/2003 01:03:59

xpandre
Posting Yak Master

212 Posts

Posted - 2003-03-14 : 04:16:13

alter procedure emp_sp
as
declare @b varchar(700)
set @b=''
select @b = coalesce(@b + ' ','') + ename
from
(select distinct(ename) from emp) as a
select @b
go

vin
Starting Member

26 Posts

Posted - 2003-03-14 : 05:17:03

hey thanx xpandre,

it is working....
could u plese tell me how this is working.
or could u tell me which part of doc i should
refer to understand this.
and let me tell u, that i am very new to T-sql

thanx

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-03-14 : 09:59:13

Hi

Whenever u create a varchar variable, initially the variable should be have NULL value only. so that NULL value with any value merged, that return value also NULL. I think ur doubt clear.Am i right?

IT Knowledge is power

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-14 : 11:08:05

Using COALESCE to Build Comma-Delimited String

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources