Select returns no rows within TRY... END TRY

Prev: dynamic SQL and temp tables
Next: CAST question

From: mscertified on 18 Jul 2008 11:36

---

I have this:

BEGIN TRY
SELECT @BankName = BankName, @BankRouting = BankRouting,
@BankAccount = BankAccount, @BankAccountHolder = BankAccountHolder,
@AccountType = AccountType
FROM dbo.BankAccount
WHERE ParentId = @ParentID
AND (AccountActiveDate <= @RunDate) AND (AccountInactiveDate > @RunDate))
END TRY
BEGIN CATCH
Set @Error = ERROR_NUMBER
If @Error <> 0
Return @Error
END CATCH
....
Return 0

My question how do I deal with the condition where no row is returned by the
select. I understand it does not enter the Catch? What happens to my outoput
parameters? Can I set them to Null?

Thanks.

From: sloan on 18 Jul 2008 12:11

---

Untested, but try the below:




"mscertified" <rupert(a)tigerlily.com> wrote in message
news:882627DD-AA61-4EC6-9427-44338AC71329(a)microsoft.com...
>I have this:
>
> BEGIN TRY

select @BankName = NULL --do not delete this line

> SELECT @BankName = BankName, @BankRouting = BankRouting,
> @BankAccount = BankAccount, @BankAccountHolder = BankAccountHolder,
> @AccountType = AccountType
> FROM dbo.BankAccount
> WHERE ParentId = @ParentID
> AND (AccountActiveDate <= @RunDate) AND (AccountInactiveDate > @RunDate))


if (@BankName IS NULL)
begin
print 'No Bank Found'
end



> END TRY
> BEGIN CATCH
> Set @Error = ERROR_NUMBER
> If @Error <> 0
> Return @Error
> END CATCH
> ...
> Return 0
>
> My question how do I deal with the condition where no row is returned by
> the
> select. I understand it does not enter the Catch? What happens to my
> outoput
> parameters? Can I set them to Null?
>
> Thanks.

From: nlulla on 18 Jul 2008 12:44

---

Try Below...what your query is doing is just assing value to
variable , hence it will not output any rows, to output the row you
will have to write another select statement to display the values in
those variables. Also I have added "TOP 1" in your query as variable
assignment will otherwise not work if the select returns more then one
row.

SET NOCOUNT ON
BEGIN TRY    
        SELECT TOP 1 @BankName = BankName, @BankRouting =
BankRouting,
        @BankAccount = BankAccount, @BankAccountHolder =
BankAccountHolder,
        @AccountType = AccountType
        FROM dbo.BankAccount
        WHERE ParentId = @ParentID
        AND (AccountActiveDate <= @RunDate)  AND (AccountInactiveDate
> @RunDate))  

SELECT @BankName , @BankRouting AS BankRouting, @BankAccount
AS BankAccount, @BankAccountHolder AS BankAccountHolder,
@AccountType AS AccountType
END TRY
BEGIN CATCH
        Set @Error = ERROR_NUMBER
        If @Error <> 0
                Return @Error
END CATCH

HTH

lulla
www.yottaconsulting.com

From: mscertified on 18 Jul 2008 13:52

---

Thanks for the response.
Is there a way to code this so that if more than one row is returned I can
trap this and return an error?

Thanks - David

"nlulla" wrote:

> Try Below...what your query is doing is just assing value to
> variable , hence it will not output any rows, to output the row you
> will have to write another select statement to display the values in
> those variables. Also I have added "TOP 1" in your query as variable
> assignment will otherwise not work if the select returns more then one
> row.
>
> SET NOCOUNT ON
> BEGIN TRY
> SELECT TOP 1 @BankName = BankName, @BankRouting =
> BankRouting,
> @BankAccount = BankAccount, @BankAccountHolder =
> BankAccountHolder,
> @AccountType = AccountType
> FROM dbo.BankAccount
> WHERE ParentId = @ParentID
> AND (AccountActiveDate <= @RunDate) AND (AccountInactiveDate
> > @RunDate))
>
> SELECT @BankName , @BankRouting AS BankRouting, @BankAccount
> AS BankAccount, @BankAccountHolder AS BankAccountHolder,
> @AccountType AS AccountType
> END TRY
> BEGIN CATCH
> Set @Error = ERROR_NUMBER
> If @Error <> 0
> Return @Error
> END CATCH
>
> HTH
>
> lulla
> www.yottaconsulting.com
>

From: Plamen Ratchev on 18 Jul 2008 14:36

---

Immediately after the select statement you can check the row count and raise
an error that will be trapped in the catch section:

IF @@ROWCOUNT > 1
RAISERROR('More than one rows.', 16, 1)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: dynamic SQL and temp tables
Next: CAST question