I am trying to loop through customers to send their reports. The temp table loads in about 14 seconds but the process to convert each customer's information to an email string takes forever. is there a better way to do this? DECLARE @TO VARCHAR(2000) DECLARE @SPLR_ID DECIMAL(5,0) CREATE TABLE #TMP (SPLR_ID DECIMAL(5,0), CNTCT_EMAIL_ADDR VARCHAR(100)) BEGIN --ONLY LOADS DATA WITH REPORTS AND RECIPIENTS TO BE SENT INSERT INTO #TMP SELECT splr.SPLR, c.CNTCT_EMAIL_ADDR FROM dbo.T_SPLR_ACCT splr JOIN GX_RPT.dbo.T_SPLR_DSTRB_LIST dl ON splr.SPLR = dl.SPLR JOIN dbo.T_RPT_DSTRB rd ON dl.DISTRIB_LIST_ID = rd.RPT_ID JOIN GX_RPT.dbo.TEMP_CONTACT c ON rd.CNTCT_ID = c.CNTCT_ID WHERE splr.ACCT_NUM IN ( --Suppliers that need report sent SELECT SPLR_ACCT FROM REFERENCE.dbo.T_ITEM WHERE ITEM_NUM IN ( --Items with price change of interest SELECT ITEM_NUM FROM dbo.TBL_DAILY_CHG WHERE PROC_DT = dbo.UDF_LAST_BUS_DAY(GETDATE()) AND ( RMV_IND = 1 OR ADD_IND = 1 OR RMV_IND = 1 OR ADD_IND = 1) AND PROC_CD = 'C')) WHILE EXISTS (SELECT * FROM #TMP) SET @SPLR = (SELECT MIN(SPLR) FROM #TMP) BEGIN SET @TO ='' SELECT @TO = CNTCT_EMAIL_ADDR + ';' FROM #TMP WHERE SPLR = @SPLR EXEC ReportServer.dbo.USP_DATA_DRIV_SUB @ID = 199 ,@emailTO = @TO ,@param1 = @SPLR END DELETE FROM #TMP WHERE SPLR = @SPLR END DROP TABLE #TMP
Ah.. now I noticed your problem.. You have a never ending loop in your statements. ... WHILE EXISTS (SELECT * FROM #TMP) SET @SPLR = (SELECT MIN(SPLR) FROM #TMP) BEGIN ... As you can take a look.. the `SET` command will be executed in the loop only. You have to put the SET command after the `BEGIN` keyword. Also you have to put the DELETE FROM #TMP WHERE SPLR = @SPLR42 inside the `BEGIN... END` of the `WHILE` cycle.
If you say, that the list loads in 14 secs, then depends what you are doing in the `ReportServer.dbo.USP_DATA_DRIV_SUB` procedure you are executing. You didn't provide any info about its content. If you provide details about it, maybe we will be able to help you.
First, I want to very strongly agree with Grant. This is not an area where SQL shines at all and I would do this in another language like SSIS or Python or C#. But, if you really want to do this in T-SQL, then instead of looping, you can create one enormous dynamic sql String that contains numerous calls to your ReportServer.dbo.USP_DATA_DRIVEN_SUBSCR with all the values for the paramaters filled in and then just execute that string. This would get somewhat complicated in your particular case since you would need to concatenate the TO section each time in a subquery, but Jeff Moden has some good techniques for that at [