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

Transact-SQL (2008)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-30 : 09:52:16

I want to rewrite the below query to avoid temp tables, set based declarative or cursors.



CREATE TABLE #UpdateItems 
(id int identity(1, 1),
 Iname varchar(200),
 Items_id int,
 Finditems_id int)
 
insert into #UpdateItems Iname,Items_id,Finditems_id)  
select itname,
us.mapitems,
uo.misitems 
from store uo
	Left outer join storemap uom on 
		uo.itemname = uom.storeitemname 
inner join processs ub on uo.processid = ub.id
inner join primarytable us on us.primaryid= ub.processmainid
left outer join masterstoreitems mo on uo.itemname = mo.itemname
where us.status ='y' and uo.misitems is null
and uo.processid = @processid
and mo.itemname is null
	
select @rowcount = @@rowcount

set @i = 0

select * from #UpdateItems

while @i <= @rowcount
begin
	select top 1 
	@id = id,
	@ItemsIname = Iname,
	@Items_id = Items_id,
	@Finditems_id = Finditems_id
	from #UpdateItems
	where id > @i
	
	select @row = @@rowcount
	
	if @row = 0 break

 	exec dbo.Itemsstock @Items_id,@ItemsIname,@Finditems_id
	
	set @i = @id
end
	
drop table #UpdateItems

Thanks for you help in advance..

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-30 : 10:52:04

The EXEC of the stored procedure is the problem. Unless you are able to implement the logic of the stored procedure inside your own script, you'll be stymied.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 10:54:14

do you have control over procedure Itemsstock ?

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-30 : 14:57:46

Yes i have control on exec proc

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-30 : 15:16:54

Just for information Itemsstock procedure will insert records from #missingitems table.

The procedure has following code something like below.

INSERT INTO [dbo].[Items]
(@Items_id,@ItemsIname,@Finditems_id)
Values
(@Items_id,@ItemsIname,@Finditems_id)

Please let me know how i can rewrite the code without temp table or cursors..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-11-30 : 15:29:30

Store Proc has to be modified. Instead of inserting one by one...why can't you insert with query you have used to insert in Temp table.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-30 : 16:02:49

Thanks sodeep..

How can be it done..can you provide the sample code..

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-30 : 16:28:43

[CODE]insert into ~~#UpdateItems~~ Items (Iname,Items_id,Finditems_id)
select itname,
us.mapitems,
uo.misitems
from store uo
Left outer join storemap uom on
uo.itemname = uom.storeitemname
inner join processs ub on uo.processid = ub.id
inner join primarytable us on us.primaryid= ub.processmainid
left outer join masterstoreitems mo on uo.itemname = mo.itemname
where us.status ='y' and uo.misitems is null
and uo.processid = @processid
and mo.itemname is null[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:15:26

put the code Bustaz gave directky inside your procedure and you're good to go
And reemember to add @processid as a parameter to proc

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-04 : 10:31:29

Thanks Bustaz Kool & visakh!
It works but How can i rewrite with CTE (common table expression) to accomplish the below..

insert into #UpdateItems Items (Iname,Items_id,Finditems_id) select itname,
us.mapitems,uo.misitems from store uo Left outer join storemap uom on
uo.itemname = uom.storeitemname inner join processs ub on uo.processid = ub.id
inner join primarytable us on us.primaryid= ub.processmainid
left outer join masterstoreitems mo on uo.itemname = mo.itemname
where us.status ='y' and uo.misitems is nulland uo.processid = @processid
and mo.itemname is null

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-04 : 10:33:28

Thanks Bustaz Kool & Visakh!

How can i accomplihs the below with CTE(Common table expression)
..
insert into #UpdateItems Items (Iname,Items_id,Finditems_id)
select itname,
us.mapitems,
uo.misitems
from store uo
Left outer join storemap uom on
uo.itemname = uom.storeitemname
inner join processs ub on uo.processid = ub.id
inner join primarytable us on us.primaryid= ub.processmainid
left outer join masterstoreitems mo on uo.itemname = mo.itemname
where us.status ='y' and uo.misitems is null
and uo.processid = @processid
and mo.itemname is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 11:52:30

hmm? why should you need CTE here? there's no need of using it in your scenario

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources