Using case to delete data row

Alvan

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
Maverick

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Delete data row from database using href

From Dev

How to fetch data in one row using case condition

From Dev

Delete specific row in a data frame?

From Dev

delete row using delete button in ListView

From Dev

delete row id using Contentprovider

From Dev

Delete blank row using For Next

From Dev

Delete Table row using javascript

From Dev

Delete row using where clause

From Dev

delete row in googlesheet using googlesheetapi

From Dev

How to delete row using php?

From Dev

Delete row of the table using javascript

From Dev

Delete a row javascript using row id

From Dev

How to have all data of one field into an array in case of duplicate row while using left join

From Dev

How do I delete a row from Sqlite using ID where my ID is binary data (GUID)

From Dev

SQL row to column using nested Case statement

From Dev

How to delete a row in dojo EnhancedGrid using row index not by using selection

From Dev

how to insert and delete row data same time

From Dev

jqGrid - how to delete row from local data?

From Dev

take value data & Delete row table with javascript

From Dev

Delete row of DT data table in Shiny app

From Dev

How to delete a row in a data frame by name in R

From Dev

delete existing row from data table

From Dev

how to insert and delete row data same time

From Dev

Delete user row in Parse Data browser

From Dev

How to delete a row in a data frame by name in R

From Dev

How to delete every row in a range that contains data?

From Dev

Using CASE statement in a Data type

From Dev

Table row data delete on Action Sheet Delete Button Pressed

From Dev

unable to delete row from table using jquery?

Related Related

HotTag

Archive