| Author |
 Topic  |
|
|
ssunny
Posting Yak Master
USA
130 Posts |
 Posted - 01/10/2013 : 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
India
48094 Posts |
Posted - 01/10/2013 : 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
USA
130 Posts |
Posted - 01/11/2013 : 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
USA
130 Posts |
Posted - 01/11/2013 : 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
India
48094 Posts |
Posted - 01/11/2013 : 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
USA
130 Posts |
Posted - 01/11/2013 : 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. |
|
|
| |
Topic |
|
|
|
CTE inside a cursor instead of a temp table?
