I created a simple stored procedure to update my table, but currently what I need is
Delete * from [ABCSystem].[dbo].[NEW_TEST_NUMBER]
WHERE sONbr = @sONbr AND SOLine = @SOLine
If @Statuscode
is = "N001" by using case. Anyone can show me a simple sample or something?
Thank you.
ALTER PROCEDURE [dbo].[usp_Testing]
-- Add the parameters for the stored procedure here
@sONbr nvarchar(50) = NULL,
@SOLine nvarchar(50) = NULL,
@SerialNbr nvarchar(50) = NULL,
@StatusCode nvarchar(50) = NULL,
@PackType nvarchar(50) = NULL,
@PalletID nvarchar(50) = NULL,
@PackingListNo nvarchar(50) = NULL,
@CrDateTime nvarchar(50) = NULL,
@CrUserID nvarchar(50) = NULL,
@return nvarchar(50) = NULL OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(
SELECT sONbr , SOLine
FROM [ABCSystem].[dbo].[NEW_TEST_NUMBER]
WHERE sONbr = @sONbr AND SOLine = @SOLine
)
BEGIN
UPDATE [ABCSystem].[dbo].[NEW_TEST_NUMBER]
SET StatusCode = @StatusCode
,LastUpdDateTime = GETDATE()
,LastUpdUserID = @CrUserID
,StatusDesc =
CASE @StatusCode WHEN 'N001' THEN 'New'
WHEN 'PR001' THEN 'Prepack In Progress'
WHEN 'PR002' THEN 'PrePacking Completed'
WHEN 'WE002' THEN 'Weight Complete'
END
,PalletID =
CASE @StatusCode WHEN 'N001' THEN cast(null as nvarchar(50))
ELSE PalletID
END
,PackType =
CASE @StatusCode WHEN 'N001' THEN cast(null as nvarchar(50))
ELSE PackType
END
,JobID =
CASE @StatusCode WHEN 'N001' THEN cast(null as nvarchar(50))
ELSE JobID
END
,JobCrDateTime =
CASE @StatusCode WHEN 'N001' THEN cast(null as nvarchar(50))
ELSE JobCrDateTime
END
,PackingListNo =
CASE @StatusCode WHEN 'N001' THEN cast(null as nvarchar(50))
ELSE PackingListNo
END
WHERE sONbr = @sONbr AND SOLine = @SOLine
IF @@ERROR <> 0
Set @Return = 'UPDATE FAILED!'
ELSE
Set @Return = 'UPDATE SUCCESSFULLY.'
END
ELSE
BEGIN
Set @Return = 'NO DATA EXIST!'
END
As far as I am seing your problem, you have to create a simple table that will tell you INSERT, UPDATE, DELETE. Link the table inside your stored proc. This will help you to sort things down.
OR
Simply sort everything in loop inside stored proc telling that
WHEN @StatusCode = 'N001'
DO update
ELSE
DO something else
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments