Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CTE inside a cursor instead of a temp table?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/10/2013 :  15:16:21  Show Profile  Reply with Quote
Hello,

I am trying to avoid using temp table and if possible cursor also in following "test_data" store procedure. Here's my table structure and sample data.
table1
------
aid aname adescription ametaid
1 'sachin' 'god' 100
2 'rahul' 'gentlemen' 99
3 'saurav' 'aggressor' 98
4 'anil' 'jumbo' 97
5 'laxman' 'special' 96
6 'dhobi' 'lallu' 1
...............

table2
--------
bid bname btitle
1 'greatest' 'greatest'
2 'respect' 'respect'
3 'bakwas' 'bakwas'
4 'worst' 'worst'
...........

table3
-------
aid bid
1 1
1 2
2 1
2 2
4 2
6 4
6 3
.............


table4
--------
aid sid

1 1
2 1
3 1
3 2
5 1
............


create proc test_data
(
@sid int
)
as
declare @aid int, @bid int

create table #tt (tname varchar (500)), tvalue varchar (500))

delcare tcursor cursor for select ta.aid,tb.bid
from table1 ta
inner join table3 tab on ta.aid = tab.aid
inner join table2 tb on tab.bid = tb.bid
inner join table4 tas on ta.aid = tas.aid
where tas.sid = @sid
open tcursor
fetch next from tcursor into @aid, @bid
while @@FETCH_STATUS = 0
begin
insert into #tt
select distinct d.id,e.name
from table 5 d
.....................
where table1.aid = @aid

union

select g.id,j.name
from table 10 g
.....................
where table2.bid = @bid

union

select 'description', ta.adescription
from table1 ta
where ta.aid = @aid

union

select 'metaid', cast(ta.ametaid as varchar (50))
from table1 ta
where ta.aid = @aid

union

select 'title', tb.btitle
from table2 tb
where tb.bid = @bid

fetch next from tcursor into @aid, @bid
end
select * from #tt
drop table #tt
close tcursor
deallocate tcursor
go

I think I can do this:

delcare tcursor cursor for with cte as (
select ta.aid,tb.bid
from table1 ta
inner join table3 tab on ta.aid = tab.aid
inner join table2 tb on tab.bid = tb.bid
inner join table4 tas on ta.aid = tas.aid
where tas.sid = @sid
)

But I really want to get rid of temp table here and I tried doing

with cte1 as (
insert into #tt
select distinct d.id,e.name
from table 5 d
.....................
where table1.aid = @aid

union

select g.id,j.name
from table 10 g
.....................
where table2.bid = @bid

union

select 'description', ta.adescription
from table1 ta
where ta.aid = @aid

union

select 'metaid', cast(ta.ametaid as varchar (50))
from table1 ta
where ta.aid = @aid

union

select 'title', tb.btitle
from table2 tb
where tb.bid = @bid
)

but I got an error. Can I do somthing like this to avoid using temp table? Sorry for the long post.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/10/2013 :  23:27:40  Show Profile  Reply with Quote
you've not explained us what you're trying to achieve with code above. can you post your expected output please?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/11/2013 :  10:49:26  Show Profile  Reply with Quote
Sorry for not being clear. I think this is easy to understand and make sense.

-- sample data and table structure

table1 (aid is the PK)
----------
aid aname adescription avalueid
1 'test' 'testtest' 50
2 'test1' 'testtest1' 56
3 'test2' 'testtest2' 45
4 'test3' 'testtest3' 1
5 'test4' 'testtest4' 78
.......

table2 (bid is the PK)
----------
bid bname btitle bvalueid
1 'testing' 'testtesingt' 6
2 'testing1' 'testtesting1' 7
3 'testing2' 'testtesting2' 8
4 'testing3' 'testtesting3' 9
................

table3 (aid,bid is a composite primary key with fk refrences to table1 and table2. one to one)
---------------------
aid bid
1 1
2 3
3 4
4 2
6 50
7 89
............
................

table4 (aid,sid is a composite primary key with fk refrences to table1. many to many )
----------------------
aid sid
1 100
2 100
3 200
3 100
4 100
1 200
...............

-- code within the store proc

declare @aid int,@bid int
create table #temp (attribute_name varchar (2000),attribute_value varchar (2000))
declate testcursor cursor for select t1.a.id,t2.bid
from table1 t1
inner join table3 t3 on t1.aid = t3.aid
inner join table2 t2 on t3.bid = t2.bid
inner join table4 t4 on t1.aid = t4.aid
where t4.sid = 100
/* so cursor will ahve these values.

@aid @bid
1 1
2 3
3 4
4 2
*/

open testcursor
fetch next from testcursor into @aid,@bid
while @@FETCH_STATUS = 0
begin
insert into #temp

-- let's just concentrate on first pair of records for now. @aid = 1,@bid = 1
-- now this is important. I want results in below format only

select 'Aid',cast (t1.aid as varchar (50))
from table1 t1 where t1.aid = @aid

union

select 'Bid',cast (t2.bid as varchar (50))
from table2 t2 where t2.bid = @bid

union

select 'Aname',t1.aname
from table1 t1 where t1.aid = @aid

union

select 'Adescription',t1.adescription
from table1 t1 where t1.aid = @aid

union

select 'Bvalueid',cast (t2.bvalueid as varchar (50))
from table2 t2 where t2.bid = @bid

fetch next from testcursor into @aid,@bid
end
select * from #temp
drop table #temp
close testcursor
deallocate testcursor
go

So for first row in the cursor (@aid = 1, @bid = 1) , my results would look like this:

attribute_name attribute_value
Aid 1
Bid 1
Aname test
Adescription testtest
Bvalueid 6

As I said before, I need result data in above format only. Is there a way I can eliminate cursor or temp table from above query?

Thanks.
Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/11/2013 :  11:06:57  Show Profile  Reply with Quote
I forgot to mention that I have done following and query is much master now but I still want to improve query because it will called very frequently.

declate testcursor cursor for
with cte (counter,aid,bid)
as
(
select ROW_NUMBER () over (order by t1.aid) as counter,t1.a.id,t2.bid
from table1 t1
inner join table3 t3 on t1.aid = t3.aid
inner join table2 t2 on t3.bid = t2.bid
inner join table4 t4 on t1.aid = t4.aid
where t4.sid = 100

.......................
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/11/2013 :  11:20:47  Show Profile  Reply with Quote
I understood your output but didnt understand purpose behind doing this. Once you get it to current o/p you wont be able to distinguish which all values belonged to which aid,bid groups

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 01/11/2013 :  11:27:46  Show Profile  Reply with Quote
Hi Visakh,
Thanks for the reply.You are correct about not able to distinguish but I am fine with that. I want to feed whole data set into a search engine.

Thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.45 seconds. Powered By: Snitz Forums 2000