CTE inside a cursor instead of a temp table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

CTE inside a cursor instead of a temp table?

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2013-01-10 : 15:16:21

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

52326 Posts

Posted - 2013-01-10 : 23:27:40

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/

ssunny
Posting Yak Master

133 Posts

Posted - 2013-01-11 : 10:49:26

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.

ssunny
Posting Yak Master

133 Posts

Posted - 2013-01-11 : 11:06:57

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

.......................

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 11:20:47

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/

ssunny
Posting Yak Master

133 Posts

Posted - 2013-01-11 : 11:27:46

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources