April 21, 2022 at 12:11 pm
Hi,
I need some guidance on how to convert a while loop code to a set-based code . I separate the source data to 2 #temp table based on the @AlertType (email or sms), and inner join the #temptable to the based table (itemheader and itemdetail) to replace the #variable and the while loop .But I am not sure if this is the correct way .
**Old code :**
Where IMD.CustomerAccountNo= @CustAccountNo
AND IMD.StatusCode=@ItemStatus
AND (@Reasoncode = '%' OR IMD.Reasoncode = @Reasoncode)
AND IH.TrackingNo=@TrackingNo
AND IH.ServiceTypeId = @ServiceType
**New code:**
inner join #tempresult_SMS SMS on
IMD.CustomerAccountNo= SMS.CustAccountNo
AND IMD.StatusCode= SMS.ItemStatus
AND (IMD.Reasoncode = SMS.ReasonCode 0R SMS.ReasonCode = '%')
AND IH.TrackingNo=SMS.TrackingNo
AND IH.ServiceTypeId = SMS.ServiceType
Full Stored procedure :
Alter PROCEDURE [dbo].[ezy_UpdateItemHeader](
@SMSNotificationItemDetails ezy_SMSNotificationItemDetailsType READONLY)
AS
BEGIN
SET NOCOUNT ON;
--Declare temp table
DECLARE @SMSDetailTable TABLE
(
[ROWID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[CustAccountNo] [NVARCHAR](MAX) NULL,
[ServiceType] [NVARCHAR](MAX) NULL,
[AlertType] [NVARCHAR](MAX) NULL,
[ItemStatus] [NVARCHAR](MAX) NULL,
[TrackingNo] [VARCHAR](MAX) NULL,
[ReasonCode] [VARCHAR](MAX) NULL
)
--Insert into temp table
INSERT INTO @SMSDetailTable( [CustAccountNo] , [ServiceType] ,[AlertType] ,[ItemStatus],[TrackingNo], [ReasonCode] )
SELECT [CustAccountNo] ,[ServiceType] ,[AlertType] ,[ItemStatus] ,[TrackingNo], [ReasonCode] FROM @SMSNotificationItemDetails
DECLARE @TotalRows INT , @CurrentRow INT
SELECT @TotalRows = COUNT(*) FROM @SMSDetailTable
SET @CurrentRow = 0
--Looping start in the first rows
WHILE(@CurrentRow < @TotalRows)
BEGIN
SET @CurrentRow = @CurrentRow + 1
DECLARE @CustAccountNo VARCHAR(50) ,@ServiceType VARCHAR(50) ,@ItemStatus VARCHAR(50) ,
@AlertType VARCHAR(50) , @TrackingNo VARCHAR(50) , @ReasonCode VARCHAR(50)
--Fetch the data into variable
SELECT @CustAccountNo = CustAccountNo , @ServiceType = ServiceType , @ItemStatus = ItemStatus
, @AlertType = AlertType , @TrackingNo = TrackingNo , @ReasonCode = ReasonCode
FROM @SMSDetailTable WHERE ROWID = @CurrentRow
DECLARE @REQNumber VARCHAR(100) , @EmailREQNumber VARCHAR (100)
---Build the transactionID
SET @REQNumber= 'TESTID'+replace(replace(replace(replace(CONVERT(VARCHAR(40),GETDATE(),121),'-',''),':',''),' ',''),'.','')
SET @EmailREQNumber= 'TESTEMAILID'+replace(replace(replace(replace(CONVERT(VARCHAR(40),GETDATE(),121),'-',''),':',''),' ',''),'.','')
--NOtification Item for SMS
IF (@AlertType = 'SMS' OR @AlertType = 'SMS/Email')
BEGIN
declare @NotificationItemDetail table(
[ID] [int],
[TransactionID] VARCHAR(max),
[ItemNumber] VARCHAR(max),
[AlertType] VARCHAR(max)
)
INSERT INTO @NotificationItemDetail (
[ID],
[TransactionID],
[ItemNumber],
[AlertType]
)
Select
IMD.AutoID
,@REQNumber + CAST(RANK() OVER (ORDER BY IMD.AutoID ASC) AS VARCHAR)
,@TrackingNo
,@AlertType
from ItemDetail IMD
INNER JOIN itemheader IH on
IH.TrackingNo = IMD.TrackingNo
WHERE
IMD.CustomerAccountNo= @CustAccountNo
AND IMD.StatusCode=@ItemStatus
AND (@Reasoncode = '%' OR IMD.Reasoncode = @Reasoncode)
AND IH.TrackingNo=@TrackingNo
AND IH.ServiceTypeId = @ServiceType
AND IMD.LastUpdatedDate = CAST(GETDATE() as DATE)
END
--NOtification Item for Email
IF (@AlertType = 'Email')
BEGIN
declare @NotificationItemSMSEMAILDetail table(
[ID] [int],
[ETransactionID] VARCHAR(max),
[EItemNumber] VARCHAR(max),
[EAlertType] VARCHAR(max))
INSERT INTO @NotificationItemSMSEMAILDetail (
[ID],
[ETransactionID],
[EItemNumber],
[EAlertType]
)
Select
IMD.AutoID
,@EmailREQNumber + CAST(RANK() OVER (ORDER BY IMD.AutoID ASC) AS VARCHAR)
,IMD.TrackingNo
,'Email'
from ItemDetail IMD
INNER JOIN Itemheader IH on
IH.TrackingNo = IMD.TrackingNo
where
IMD.CustomerAccountNo= @CustAccountNo
AND IMD.StatusCode=@ItemStatus
AND IH.ServiceTypeId = @ServiceType
AND IMD.LastUpdatedDate = CAST(GETDATE() as DATE)
END
END
END
April 21, 2022 at 1:15 pm
Couple of initial questions, why do you INSERT the rows from your parameter @ezy_SMSNotificationItemDetailsType into a user defined table variable @SMSDetailTable? Why not just use the contents of @ezy_SMSNotificationItemDetailsType? Why are all the column within @SMSDetailTable an nvarchar(MAX) too? Are all the columns likely to have values longer than 4,000 characters?
Also you have some typographical errors. There's a trailing comma after [ReasonCode] [VARCHAR](MAX) NULL, and you attempt to insert data into a column called [TrackingNo, [ReasonCode].
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 21, 2022 at 2:16 pm
Thanks Thom.
The typo is fixed .
All the (Max) data type will be reviewed with the users once the while loop logic have been converted and tested . I can use the contents from @ezy_SMSNotificationItemDetailsType and I will need to amend the user defined table type to ensure the data type length are correct .
This is the data type length which will be change once user is confirm :
--Declare temp table
DECLARE @SMSDetailTable TABLE
(
[ROWID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
[CustAccountNo] [VARCHAR](50) NULL,
[ServiceType] [VARCHAR](20) NULL,
[AlertType] [VARCHAR](20) NULL,
[ItemStatus] [VARCHAR](10) NULL,
[TrackingNo] [VARCHAR](50) NULL,
[ReasonCode] [VARCHAR](10) NULL
)
April 25, 2022 at 4:59 am
while waiting for user confirmation on the data type length , I has some idea how to convert this while loop, but I need help to confirm on the joining part , or is there other better method to convert the while loop :
First I will separate the source data based on the Alert Type, then if #tempresult_SMS table is not empty , then insert into the table and inner join with the #tempresult_SMS with the @variable used in the while loop :
drop table if exists #tempresult_SMS
SELECT CustAccountNo , ServiceType ,ItemStatus , AlertType , TrackingNo , ReasonCode into #tempresult_SMS FROM @SMSDetailTable WHERE AlertType = 'SMS'
IF EXISTS( SELECT TOP (1) 1 FROM #tempresult_SMS)
Begin
INSERT INTO @NotificationItemDetail ([ID],[TransactionID],[ItemNumber],[AlertType])
Select IMD.AutoID
,select 'TESTID'+replace(replace(replace(replace(CONVERT(VARCHAR(40),DATEADD(millisecond, 5 * RANK () over (order by autoid), GETDATE()),121),'-',''),':',''),' ',''),'.','') + CAST(RANK() OVER (ORDER BY IMD.AutoID ASC) AS VARCHAR)
,@TrackingNo , ,@AlertType
from ItemDetail IMD
INNER JOIN itemheader IH on
IH.TrackingNo = IMD.TrackingNo
INNER JOIN #tempresult_SMS T_SMS on
IMD.CustomerAccountNo= T_SMS.CustAccountNo
AND IMD.StatusCode=T_SMS.ItemStatus
AND (T_SMS.ReasonCode= '%' OR IMD.Reasoncode = T_SMS.ReasonCode)
AND IH.TrackingNo=T_SMS.TrackingNo
AND IH.ServiceTypeId = T_SMS.ServiceType
WHERE
IMD.LastUpdatedDate = CAST(GETDATE() as DATE)
End
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply