CODEWITHSUNDEEP
sql-server-2008t-sqlsql-server-2008-r2
Michael Goldshteyn
Michael Goldshteyn

Reputation: 74470

Is there a way to make SELECT that fails not throw inside a TRY/CATCH?

For example:

CREATE PROCEDURE dbo.MyProc
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    ...
    DECLARE @Id INT;

    -- I don't want the following line to throw and abort processing. 
    -- I just want @Id to remain NULL. I can add a nested TRY/CATCH, but is there
    -- a better way?
    SET @Id=(SELECT Id FROM MyTable WHERE ...); 
    ...
  END TRY
  BEGIN CATCH
    ...
  END CATCH;
END;

Update: Just to clarify, if multiple rows are returned, I want @Id to stay NULL.

Upvotes: 3

Views: 91

Answers (3)

Ben
Ben

Reputation: 35663

Try this:

select @id = (select max(id) from MyTable Where <YOUR CONDITION> Having count(1) = 1)

This will return a single row, or no rows if there are multiple matching.

Upvotes: 2

Jose Chama
Jose Chama

Reputation: 2988

I will do something like this to avoid multiple rows errors:

SELECT top 1 @Id = Id FROM MyTable WHERE ...

or this

SELECT @Id = Id FROM MyTable WHERE ...
if @@rowcount > 1
   set @id = null

This last piece will set the @Id to null when there were multiple rows.

Upvotes: 3

EkoostikMartin
EkoostikMartin

Reputation: 6921

Pretty sure this would not throw:

SELECT @Id = Id FROM MyTable WHERE ...

Upvotes: 1

Related Questions