I return the value of Stored-procedure in SQL

Question

I return the value of Stored-procedure in SQL

Navigation

#1 by (0 votes)

0

I just made a Store procedure on sql server. This is the structure.

USE [AuditoriasDB]
GO
/****** Object:  StoredProcedure [dbo].[spFindOrInsertSucursalProveedores]    Script Date: 18/08/2018 01:23:58 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER PROCEDURE [dbo].[spFindOrInsertSucursalProveedores] 
    @SucursalID int,
    @ProveedorID int
AS
BEGIN
    DECLARE @Result INT;
    IF EXISTS (SELECT proveedorid FROM SucursalProveedores WHERE sucursalid = @SucursalID and proveedorid = @ProveedorID)
         BEGIN
             RETURN(0)  
         END
    ELSE
        BEGIN
            INSERT INTO SucursalProveedores (SucursalID, ProveedorID) VALUES (@Sucursalid, @ProveedorID)
        END

    SET @Result = SCOPE_IDENTITY();
END

The code as I call it.

  const string sql = @"[dbo].[spFindOrInsertSucursalProveedores]";

                        var result = conn.Execute(sql, new
                        {
                            SucursalID = Convert.ToInt32(cboSuc.SelectedValue.ToString()),
                            ProveedorID = Convert.ToInt32(cboProv.SelectedValue.ToString())
                        }, commandType: CommandType.StoredProcedure);

result siempre es -1

In the first control sequence, add a return of 0 if it exists. otherwise, it adds the value, but when calling it with the orm daper framework, this returns the value of -1. always, that is, I do not duplicate the data, that is the goal of the sp, but it always returns me -1. How could I return 0 if the insertion does not occur and return the value greater than 0 when it does. I hope you help me.

thanks.

sql sql-server stored-procedure

asked by JuanL 18.08.2018 в 21:28

source

1 answer

Log me correctly on asp.net Create a trigger to copy value from one field to another field within the same table in mysql

score 0 · Accepted Answer

Friend try to do it in the following way:

USE [AuditoriasDB]
GO
/****** Object:  StoredProcedure [dbo].[spFindOrInsertSucursalProveedores]    Script Date: 18/08/2018 01:23:58 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER PROCEDURE [dbo].[spFindOrInsertSucursalProveedores] 
    @SucursalID INT,
    @ProveedorID INT,
    @Result INT OUTPUT
AS
BEGIN
    IF EXISTS (SELECT proveedorid FROM SucursalProveedores WHERE sucursalid = @SucursalID and proveedorid = @ProveedorID)
         BEGIN
             SET @Result = 0; 
         END
    ELSE
        BEGIN
            INSERT INTO SucursalProveedores (SucursalID, ProveedorID) VALUES (@Sucursalid, @ProveedorID)
            SET @Result = SCOPE_IDENTITY();
        END
END