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.
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 ametaid1 'sachin' 'god' 1002 'rahul' 'gentlemen' 993 'saurav' 'aggressor' 984 'anil' 'jumbo' 975 'laxman' 'special' 966 'dhobi' 'lallu' 1 ............... table2--------bid bname btitle 1 'greatest' 'greatest'2 'respect' 'respect'3 'bakwas' 'bakwas'4 'worst' 'worst' ........... table3-------aid bid1 11 22 12 24 26 46 3.............table4--------aid sid1 12 13 13 25 1 ............ create proc test_data(@sid int)asdeclare @aid int, @bid intcreate 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 = @sidopen tcursor fetch next from tcursor into @aid, @bidwhile @@FETCH_STATUS = 0begin 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, @bidendselect * from #ttdrop table #ttclose tcursordeallocate tcursorgoI 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 MVPhttp://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 structuretable1 (aid is the PK)----------aid aname adescription avalueid1 'test' 'testtest' 502 'test1' 'testtest1' 563 'test2' 'testtest2' 45 4 'test3' 'testtest3' 15 'test4' 'testtest4' 78.......table2 (bid is the PK)----------bid bname btitle bvalueid1 'testing' 'testtesingt' 62 'testing1' 'testtesting1' 73 'testing2' 'testtesting2' 84 'testing3' 'testtesting3' 9................table3 (aid,bid is a composite primary key with fk refrences to table1 and table2. one to one)---------------------aid bid 1 12 33 44 26 507 89............................table4 (aid,sid is a composite primary key with fk refrences to table1. many to many )----------------------aid sid1 1002 1003 2003 1004 1001 200............... -- code within the store procdeclare @aid int,@bid intcreate 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 @bid1 12 33 44 2*/open testcursorfetch next from testcursor into @aid,@bidwhile @@FETCH_STATUS = 0begin 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 = @bidfetch next from testcursor into @aid,@bidendselect * from #tempdrop table #tempclose testcursordeallocate testcursorgoSo for first row in the cursor (@aid = 1, @bid = 1) , my results would look like this:attribute_name attribute_value Aid 1Bid 1Aname testAdescription testtestBvalueid 6As 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.bidfrom table1 t1inner join table3 t3 on t1.aid = t3.aidinner join table2 t2 on t3.bid = t2.bidinner join table4 t4 on t1.aid = t4.aidwhere 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 MVPhttp://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. |
|
|
|
|
|
|
|