CODEWITHSUNDEEP
c#sql-server
rdm
rdm

Reputation: 1

Stored procedure returning incorrect ID guid

I need a little help with this. The stored procedure below doesn't seem to ever match exiting unique identifier

ALTER PROCEDURE [dbo].[spInsertUpdateThisStuff]
    @Id uniqueidentifier OUTPUT,
    @Content nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tAudit table (id uniqueidentifier)

    IF EXISTS(SELECT * FROM [dbo].[myData] WHERE [ID] = @Id)
    --  UPDATE
    BEGIN
        UPDATE [dbo].[myData] 
        SET [ID] = @ID,
            [Content] = @Content
        OUTPUT inserted.[ID] INTO @tAudit
        WHERE [ID] = @Id

        SELECT id FROM @tAudit
    END
    ELSE
    BEGIN
        --  INSERT
        SET @ID = NEWID()

        INSERT INTO [dbo].CBData ([ID], [Content])
        OUTPUT inserted.[ID] INTO @tAudit
        VALUES(@Id, @Content)

        SELECT id FROM @tAudit
    END;

    SET @ID = (SELECT id FROM @tAudit);
END

the C#

cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = (currentRecord.ID == null) ? Guid.Empty : currentRecord.ID;
cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
currentRecord.ID = Guid.Parse(cmd.Parameters["@ID"].Value.ToString());

It seems the first IF statement does not ever become true, but if test (SELECT * FROM [dbo].[myData] WHERE [ID] = @Id) with a the matching UID it comes back with data.

Upvotes: 0

Views: 134

Answers (3)

rdm
rdm

Reputation: 1

This is my fix. This is my new stored procedure

ALTER PROCEDURE [dbo].[spInsertUpdatemyData]
    @ID uniqueidentifier,
    @IDOut uniqueidentifier OUTPUT,
    @CONTENT nvarchar(255)
AS

BEGIN
    --SET NOCOUNT ON;
    DECLARE @tAudit table (outputID uniqueidentifier)
    IF EXISTS(SELECT * FROM [dbo].[myData] WHERE [ID] = @ID)
    --  UPDATE
        BEGIN
            UPDATE [dbo].[CBData] 
            SET     [ID] = @ID,
                    [Content] = @Content
            OUTPUT inserted.[ID] INTO @tAudit
            WHERE [ID] = @ID
        SELECT outputID FROM @tAudit;
          END
    ELSE
        BEGIN
    --  INSERT
        INSERT INTO [dbo].myData
               ([ID],[Content])
            OUTPUT  inserted.[ID] INTO @tAudit
            VALUES(NEWID(),@Content);
            SELECT outputID FROM @tAudit
        END;
        set @IDOut = (SELECT outputID FROM @tAudit);
END

and the relative C#

 //Add Parameter for output to sql command then Change Direction of parameter 
 cmd.Parameters.Add("@IDOut", SqlDbType.UniqueIdentifier).Value = Guid.Empty ;
 cmd.Parameters["@IDOut"].Direction = ParameterDirection.InputOutput;
 cmd.ExecuteNonQuery();
 currentRecord.ID = Guid.Parse(cmd.Parameters["@IDOut"].Value.ToString());
 cmd.Transaction.Commit();

Upvotes: 0

Scott Hannen
Scott Hannen

Reputation: 29207

How are you declaring the parameter in your code? Is it ParameterDirection.Output?

Try changing it to ParameterDirection.InputOutput. Your SQL looks okay. And you can pass an input value to a parameter declared as OUTPUT in your stored procedure. So maybe it's just that ParameterDirection.

You can also change this

IF EXISTS(SELECT * FROM [dbo].[myData] WHERE [ID] = @Id)

to

IF @Id IS NOT NULL AND EXISTS(SELECT * FROM [dbo].[myData] WHERE [ID] = @Id)

If your ID column isn't nullable then it's going to work the same either way. But this is a little more explicit, showing that it's recognized that @Id might be null because it's an OUTPUT parameter.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

This statement is problematic,,

 DECLARE @tAudit table (id uniqueidentifier)
    IF EXISTS(SELECT * FROM [dbo].[myData] WHERE [ID] = @Id)
    --  UPDATE
         BEGIN

@id is an Output Parameter and is null by default and you are trying to check that..

basically NEWID() won't be duplicated,so update part is redundant

Upvotes: 1

Related Questions