CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Antonio
Antonio

Reputation: 602

Returns of an Update Stored Procedure

I have this stored procedure on my SQL SERVER 2008:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SAVE1]
    @IDKEY float,
    @CPF nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;
BEGIN TRY
    UPDATE people SET NINUMBER = @CPF WHERE IDKEY = (SELECT IDKEY from contact where IDKEY= @IDKEY)
    SELECT 1 as retorno
END TRY
BEGIN CATCH
    SELECT 0 as retorno
END CATCH


END

But if I execute with a invalid IDKEY, the return is "1", but I thought would enter in the "CATCH" and return "0". What I'm doing wrong?

Upvotes: 1

Views: 30

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

Your query is not failing instead it is returning no rows and hence it is returning 1. Try like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SAVE1]
    @IDKEY float,
    @CPF nvarchar(20)
AS

BEGIN
    SET NOCOUNT ON;

    UPDATE people SET NINUMBER = @CPF WHERE IDKEY = (SELECT IDKEY from contact where IDKEY= @IDKEY)
END

IF (@@ROWCOUNT > 0)
BEGIN
     SELECT 1 as retorno
END
ELSE 
BEGIN
    SELECT 0 as retorno
END

Upvotes: 1

mservidio
mservidio

Reputation: 13057

It won't throw an exception in this case. Your subquery simply would return no records, and therefore your update statement would execute against zero records. It would not fail though.

Upvotes: 2

Related Questions