0 votes

Validate whether a record exists in a SQL SERVER table

Cordial Greetings,

What I want to do is to validate if a record exists in a table with a stored procedure from SQL SERVER.

USE [BDCredito]
GO
/****** Object:  StoredProcedure [dbo].[SP_CONS_SOLCUPO_APRO_EXISTE]    Script Date: 11/15/2018 08:48:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONS_SOLCUPO_APRO_EXISTE]
(
@Id_Solicitud nvarchar (255),
@CC_Asociado nvarchar (255)
)
AS 
SELECT * FROM TBL_DOC_APRO WHERE EXISTS (SELECT * FROM TBL_DOC_APRO WHERE Id_Solicitud = ''+@Id_Solicitud+'' and CC_Asociado = ''+@CC_Asociado+'');

I don't know how to make it work, I hope you can help me.

Thank you

0voto

shop350 Points 633

To begin with, you should not put quotation marks in the variables, since they arrive as text, concatenation is unnecessary.

You can validate if it exists and return a boolean, the cast as bit is to force the return of bit and not an int. (causes conflict in some ORMs if not specified).

You might also consider putting this better as a function since it does not perform any modification procedures to the database.

USE [BDCredito]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONS_SOLCUPO_APRO_EXISTE]
(
@Id_Solicitud nvarchar (255),
@CC_Asociado nvarchar (255)
)
AS 
IF EXISTS (SELECT * FROM TBL_DOC_APRO WHERE Id_Solicitud = @Id_Solicitud and CC_Asociado = @CC_Asociado)
    RETURN CAST(1 AS bit);
ELSE 
    RETURN CAST(0 AS bit);

0voto

Victor Perdomo Points 1776

I would make only one SELECT COUNT(*) of the table if it returns 0 does not exist if it returns something else it means that it does exist. If only unique records are handled under those conditions, then I can use the response from shop350 if what you want is a bool as a response in your application. Or alternatively you can also make a case if you get a lot of records in the query and convert based on that response to a bool . Where @Resultado is an output variable.

First alternative (Returns the number of records that meet the condition):

USE [BDCredito]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONS_SOLCUPO_APRO_EXISTE]
(
@Id_Solicitud nvarchar (255),
@CC_Asociado nvarchar (255),
@Resultado int output
)
AS 
SELECT @Resultado = COUNT(*) FROM TBL_DOC_APRO
WHERE Id_Solicitud = @Id_Solicitud
AND CC_Asociado = @CC_Asociado;

Second alternative (If only 1 record is obtained as a response, it returns a bool ):

USE [BDCredito]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONS_SOLCUPO_APRO_EXISTE]
(
@Id_Solicitud nvarchar (255),
@CC_Asociado nvarchar (255),
@Resultado bit output
)
AS 
SELECT @Resultado = CAST(COUNT(*) AS BIT) FROM TBL_DOC_APRO
WHERE Id_Solicitud = @Id_Solicitud
AND CC_Asociado = @CC_Asociado;

Third alternative (If several records are obtained, the following is carried out case to convert the response to bool ):

USE [BDCredito]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONS_SOLCUPO_APRO_EXISTE]
(
@Id_Solicitud nvarchar (255),
@CC_Asociado nvarchar (255),
@Resultado bit output
)
AS 
SELECT @Resultado = CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) FROM TBL_DOC_APRO
WHERE Id_Solicitud = @Id_Solicitud
AND CC_Asociado = @CC_Asociado;

The single quotation marks do not need to be placed.

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X