Multiple selects with T-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 2005 Forums

Transact-SQL (2005)

Multiple selects with T-Sql

Author

Topic

debiru
Starting Member

4 Posts

Posted - 2008-04-29 : 06:05:08

Hallo, i need some help

I've done this script
[CODE]
SET NOCOUNT ON

DECLARE @login varchar(50)

DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)

OPEN cur

FETCH NEXT FROM cur
INTO @login

WHILE @@FETCH_STATUS = 0
BEGIN

select last,first from users where login=@login;

FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
[/CODE]

But the result is not what i originally wanted.

It's generates multiple select tables with only one row.

I need only one table with all the rows generated.

I tried this:
[CODE]
SET NOCOUNT ON

DECLARE @login varchar(50)

DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)

OPEN cur

FETCH NEXT FROM cur
INTO @login

IF @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
WHILE @@FETCH_STATUS = 0
BEGIN

union
select last,first from users where login=@login;

FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
[/CODE]

But it ended in an error on the union command.

Can you help me?

Thanks and sorry for my bad english :)

Debiru

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 06:17:40

Why do you want to use cursor for this? I think this is enough
SELECT u.last,u.first
from [users] u
inner join products p
on p.user=u.login

debiru
Starting Member

4 Posts

Posted - 2008-04-29 : 06:28:32

Yes, it's correct, but this script is a "biohazard" :)

I'm working on a select more complex then this and the script i've wrote is the base for my bigger select...

Thanks :)

Debiru

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 06:30:52

biohazard? didnt get that. Can you post the full query then?

debiru
Starting Member

4 Posts

Posted - 2008-04-29 : 06:45:39

I can't post the full query

"Biohazard" intends for an experiment

My intention is to launch a very big query for each login present in a query result, but i can't use the statement "login in (select...)".

I've to make the same query for a single login for time.

The big query generate a single row table, but i need that all the rows generated by the WHILE cicle are present in a sigle result table...

Is that too complex? (sorry for my bad english

)

Debiru

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 07:53:07

quote:
Originally posted by debiru

I can't post the full query

"Biohazard" intends for an experiment

My intention is to launch a very big query for each login present in a query result, but i can't use the statement "login in (select...)".

I've to make the same query for a single login for time.

The big query generate a single row table, but i need that all the rows generated by the WHILE cicle are present in a sigle result table...

Is that too complex? (sorry for my bad english )

Debiru

Sorry i didnt understand what you told here. Are you trying to get values for IN clause from another query?If thats the case you can try putting them in a temoprary table and join onto that. Or even create a UDf if query needs to be reused.

debiru
Starting Member

4 Posts

Posted - 2008-04-29 : 08:45:09

The idea of a temporary table is good, but i don't know if i can create a temporary table or insert in a existing table temporary data...

I've to think a manner to do this job

Debiru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources