locked
Updating Stored Procedure with constant value RRS feed

  • Question

  • Hi - I have below sproc. I need to update the same column with a static value. How can I achieve this ?

    I tried below, but its not getting updated. 


    ALTER PROCEDURE [dbo].[spUpdate_SENT] ( @Status VARCHAR(50) ) AS BEGIN SET NOCOUNT ON; UPDATE [GetPaid_IntraPayments] SET [Status] = 'SENT' WHERE Status ='INTRANSIT'; END



    MBH

    Tuesday, January 12, 2016 6:23 PM

Answers

  • So, first try

    select * from [GetPaid_IntraPayments]  WHERE Status ='INTRANSIT';
    

    How many rows this query will return?

    Then, if you're sure you want to update all these statuses, run your query

    UPDATe [GetPaid_IntraPayments] SET [Status] = 'SENT' WHERE Status ='INTRANSIT';
    

    It should show the same number of rows updated as your original query returned.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 12, 2016 7:18 PM
    Answerer
  • If you have some date column in your table and you want to delete old data based on that date, you may use

    select * from myTable where DateCol <= dateadd(month, -2, CAST(CURRENT_TIMESTAMP as date)) -- find all rows which are older than 2 months from the current date

    If this select returns rows you want to delete and there are not that many rows, then

    delete from myTable where DateCol <=dateadd(month, -2, cast(CURRENT_TIMESTAMP as date))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Tuesday, January 12, 2016 8:44 PM
    Answerer

All replies

  • Hi,

    This is probably because you don't have any values 'INTRANSIT' in your STATUS column.

    SELECT TOP 10 status FROM[GetPaid_IntraPayments] WHERE status = 'INTRANSIT'

    Run the above command and see if these values(INTRANSIT) exist in your table.

    Also your proc is little confusing. Why do you want to pass  @Status pram when you are not using it ?

    Hope this helps

    Thanks

    Bhanu

    Tuesday, January 12, 2016 6:37 PM
  • below is my table...I have to update the Status colomn as Status=SENT, when Status = INTRANSIT


    MBH

    Tuesday, January 12, 2016 7:09 PM
  • So, first try

    select * from [GetPaid_IntraPayments]  WHERE Status ='INTRANSIT';
    

    How many rows this query will return?

    Then, if you're sure you want to update all these statuses, run your query

    UPDATe [GetPaid_IntraPayments] SET [Status] = 'SENT' WHERE Status ='INTRANSIT';
    

    It should show the same number of rows updated as your original query returned.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 12, 2016 7:18 PM
    Answerer
  • What are you getting when you run the proc? Do you see any errors or any output in the results section?

    Copy the screenshot with the exec proc command if possible.

    Thanks

    Bhanu


    • Edited by bhanu_nz Tuesday, January 12, 2016 7:27 PM
    Tuesday, January 12, 2016 7:25 PM
  • is there any command to delete the rows after 2 months of time ?

    MBH

    Tuesday, January 12, 2016 7:37 PM
  • I guess, their might be extra white space in Status value. Select the 'Status' column value and press Ctrl +C  (Copy) and past the value in notepad.In short copy and past value in notepad. See the exact value.If you see any extra space in value then use that value or remove the space from value.

    If column datatype is 'char', then defiantly there is extra space.

    Tuesday, January 12, 2016 7:48 PM
  • If you have some date column in your table and you want to delete old data based on that date, you may use

    select * from myTable where DateCol <= dateadd(month, -2, CAST(CURRENT_TIMESTAMP as date)) -- find all rows which are older than 2 months from the current date

    If this select returns rows you want to delete and there are not that many rows, then

    delete from myTable where DateCol <=dateadd(month, -2, cast(CURRENT_TIMESTAMP as date))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Tuesday, January 12, 2016 8:44 PM
    Answerer