CODEWITHSUNDEEP
sqlsql-serverssms
Michael Tot Korsgaard
Michael Tot Korsgaard

Reputation: 4014

Stored procedure only updates when running it from Management studio

So yea. I have this really weird problem. I have the following stored procedure

ALTER PROCEDURE [CP24SHOP].[sp_part_set_status_bulk]
    @user                   nvarchar(max),
    @doerTicket             VARCHAR ( 200 ) = null,
    @status                 int,
    @items                  CP24SHOP.udt_parts READONLY
AS
BEGIN
    SET NOCOUNT ON;
        --  Check security
        exec websocket.sp_validate_user
            @user,
            @doerTicket out

        --  foreach row in @items, update the status
        MERGE INTO [file].ItemPart WITH ( XLOCK, ROWLOCK ) AS target 
        USING ( SELECT
                    item.GID        
                FROM @items AS item
             ) AS source
        ON ( target.GID = source.GID )
        WHEN MATCHED THEN
            UPDATE SET
                target.[Status] = @status,
                target.DateTimeModified = GETDATE();

        select 'bob'
    RETURN 0
END

and when I run it from Management Studio with this code

declare @user nvarchar(max) = 'websocket'

        DECLARE @list CP24SHOP.udt_parts
        INSERT INTO @list 
        (
            GID
        )
        VALUES  
        (
            -7228376
        )

        select [Status] from [file].ItemPart
        where GID = -7228376

        exec CP24SHOP.sp_part_set_status_bulk   
            @user = @user,
            @items = @list,
            @status = '155'

        select [Status], DateTimeModified from [file].ItemPart
        where GID = -7228376

it updates the status without problem

but when calling it through our websocket it runs the code and returns "bob" as it should, but when I check the database the status of the item hasn't updated. I'm clueless as to what might be wrong

Upvotes: 1

Views: 46

Answers (1)

john McTighe
john McTighe

Reputation: 1181

err - I think you're missing a commit. Looks to me like you are updating & then rolling back!

Upvotes: 1

Related Questions