While insert loop HELP - 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 2012 Forums

Transact-SQL (2012)

While insert loop HELP

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-06-26 : 11:18:45

Hi All,

I got this SP, but this keep in a loop.
but of course this is a continuous loop
i just want it to loop thru 1 time thru all the customers.

CREATE PROCEDURE [dbo].[SP_BACK_mail_Send_Massmail]

@subject as nvarchar(50),
@body as nvarchar(max),
@shopid as nvarchar(50)
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin
WHILE EXISTS (SELECT dbo.T_Customer.CustomerID
FROM dbo.T_Customer INNER JOIN
dbo.T_SYS_ShopSystem ON dbo.T_Customer.site = dbo.T_SYS_ShopSystem.shopid
WHERE (dbo.T_SYS_ShopSystem.shopid = @shopid))

INSERT INTO T_Customer_Mailbox
(userid, useremail, Subject, ordernr, mailbody, replytoemail,answerdby,[status],lang)
SELECT dbo.T_Customer.CustomerID,dbo.T_SYS_ShopSystem.managername, @subject, 0, @body, 0, 'system',2,
dbo.T_SYS_ShopSystem.shoplang
FROM dbo.T_Customer INNER JOIN
dbo.T_SYS_ShopSystem ON dbo.T_Customer.site = dbo.T_SYS_ShopSystem.shopid
WHERE (dbo.T_SYS_ShopSystem.shopid = @shopid)
end

tnx a lot,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 11:23:35

why do you need a loop?
the below code


...
INSERT INTO T_Customer_Mailbox
(userid, useremail, Subject, ordernr, mailbody, replytoemail,answerdby,[status],lang)
SELECT dbo.T_Customer.CustomerID,dbo.T_SYS_ShopSystem.managername, @subject, 0, @body, 0, 'system',2, 
dbo.T_SYS_ShopSystem.shoplang
FROM dbo.T_Customer INNER JOIN
dbo.T_SYS_ShopSystem ON dbo.T_Customer.site = dbo.T_SYS_ShopSystem.shopid
WHERE (dbo.T_SYS_ShopSystem.shopid = @shopid)
....

itself will insert all customer records belong to shopid which is passed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mike13
Posting Yak Master

219 Posts

Posted - 2013-06-26 : 11:29:28

Time outs 250.000 records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 12:53:01

have a look at execution plan and see what are the costly steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mike13
Posting Yak Master

219 Posts

Posted - 2013-06-26 : 12:58:06

mmm can you just answer my question?
how can i do a loop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 13:16:38

why do you need a loop here? as i see there's nothing that calls for a loop. You just need a set based insert.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-26 : 14:58:32

quote:
Originally posted by mike13

mmm can you just answer my question?
how can i do a loop

Your loop is an infinite one .. and that okay, if you're that much interested in a looping method, instead of using a better approach as suggested, then here's an example of the loop

CREATE PROCEDURE [dbo].[SP_BACK_mail_Send_Massmail]

@subject as nvarchar(50),
@body as nvarchar(max),
@shopid as nvarchar(50)
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin
WHILE EXISTS (SELECT dbo.T_Customer.CustomerID
FROM dbo.T_Customer INNER JOIN
dbo.T_SYS_ShopSystem ON dbo.T_Customer.site = dbo.T_SYS_ShopSystem.shopid
WHERE (dbo.T_SYS_ShopSystem.shopid = @shopid))

INSERT INTO T_Customer_Mailbox
(userid, useremail, Subject, ordernr, mailbody, replytoemail,answerdby,[status],lang)
SELECT dbo.T_Customer.CustomerID,dbo.T_SYS_ShopSystem.managername, @subject, 0, @body, 0, 'system',2,
dbo.T_SYS_ShopSystem.shoplang
FROM dbo.T_Customer INNER JOIN
dbo.T_SYS_ShopSystem ON dbo.T_Customer.site = dbo.T_SYS_ShopSystem.shopid
WHERE (dbo.T_SYS_ShopSystem.shopid = @shopid)

Return;

end

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources