none
Help with Stored Procedure RRS feed

  • Question

  • We have customer support that sends us DB queries to run, some of them are one liners, so the SP can have one line of code with say two variables, updated for one client that will work. But, some customers would have to update say 10k records, that would be difficult for that person to sit and update one at a time.We want to create a SP's so they can run these things without us having to do it manually one at a time to save time.Please help on how to create a SP for such situations. Thank you

    NJPA

    Thursday, January 23, 2020 7:53 PM

Answers

  • Hi ,

    Would you like this one ?

    create procedure test_procedure 
    @Rid   nvarchar(20)
    as
    begin
    	update ACC
    	set COL1 = '1', COL2 = '2', timestamp = GETDATE()
    	FROM acc A
    	JOIN ENROLL B
    	ON A.id = B.id
    	WHERE b.Rid = @Rid
    	and A.id in ('ABC','XYZ','QWE')
    end
    
    go
    execute test_procedure '005'
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by NJPA1234 Friday, January 24, 2020 2:28 PM
    Friday, January 24, 2020 3:28 AM

All replies

  • Hi,

    Can you mention any example data with sample table and expected output for that. 

    Regards,

    Arul

    Thursday, January 23, 2020 8:00 PM
  • We have customer support that sends us DB queries to run, some of them are one liners, so the SP can have one line of code with say two variables, updated for one client that will work. But, some customers would have to update say 10k records, that would be difficult for that person to sit and update one at a time.We want to create a SP's so they can run these things without us having to do it manually one at a time to save time.Please help on how to create a SP for such situations. Thank you

    And we are expected to do this without any examples at all to work from?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 23, 2020 10:56 PM
  • update ACC
    set COL1 = '1', COL2 = '2', timestamp = GETDATE()
    FROM acc A
    JOIN ENROLL B
      ON A.id = B.id
    WHERE b.Rid = 'XXXXX'
    and A.id in ('ABC','XYZ','QWE')

    NJPA

    Friday, January 24, 2020 1:53 AM
  • Hi ,

    Would you like this one ?

    create procedure test_procedure 
    @Rid   nvarchar(20)
    as
    begin
    	update ACC
    	set COL1 = '1', COL2 = '2', timestamp = GETDATE()
    	FROM acc A
    	JOIN ENROLL B
    	ON A.id = B.id
    	WHERE b.Rid = @Rid
    	and A.id in ('ABC','XYZ','QWE')
    end
    
    go
    execute test_procedure '005'
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by NJPA1234 Friday, January 24, 2020 2:28 PM
    Friday, January 24, 2020 3:28 AM
  • Thank you Rachel!!!

    NJPA

    Friday, January 24, 2020 2:28 PM