replacement for a function on SQL Server 7

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)

replacement for a function on SQL Server 7

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-29 : 21:36:47

Bruno writes "Hi

I have the need of something to replace the FUNCTION existing in Oracle and SQLServer 2000. I'm running SQLServer 7 and cannot have functions (which would solve my problem very easily).
Is there any way of using procedures, cursors or subqueries to perform this simple task below???? Please help me out.

drop table tbl1
drop table tbl2

create table tbl1
(
id smallint,
name varchar(20),
address varchar(30)
)

create table tbl2
(
id smallint,
text varchar(20)
)

insert into tbl1 values(1,'name1', 'address1')
insert into tbl1 values(2,'name2', 'address2')
insert into tbl1 values(3,'name3', 'address3')

insert into tbl2 values(1,'text1a')
insert into tbl2 values(1,'text1b')
insert into tbl2 values(1,'text1c')
insert into tbl2 values(2,'text2a')
insert into tbl2 values(2,'text2b')

***** this is the result I want

name    address                        text                 
------- ------------------------------ --------------------------
name1   address1                       text1a, text1b, text1c
name2   address2                       text2a, text2a

(2 row(s) affected)

***** this is the result I can get

select tbl1.name,tbl1.address,tbl2.text
from tbl1, tbl2
where tbl1.id=tbl2.id

name    address                        text                 
------- ------------------------------ -------------------- 
name1   address1                       text1a
name1   address1                       text1b
name1   address1                       text1c
name2   address2                       text2a
name2   address2                       text2b

(5 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources