If Else Problem

[togatown]

I am rather new to stored procedures, can someone explain why when this executes and the conditions are met, I cannot return the results of the select statement to my code (ordinal not found error) but when the conditions aren't true it runs fine.

Thanks

CREATE PROCEDURE sp_selfenroll
AS

Declare @acCheck nvarchar(20)
Declare @Exists nvarchar(10)
Declare @Identity Int

Select @acCheck = AccountNumber From Customer Where AccountNumber = @accountnumber

If (@acCheck IS NULL) OR (@acCheck = '')
Begin
Insert Into table
Update another table
Set @Exists = 'False'
End
Else
Begin
Set @Exists = 'True'
End

SELECT @Exists as 'duplicate'

GO

 

[dbomrrsm]

I did

create table Customer (AccountNumber int)

insert into Customer
values (1325)

then amended your code slightly

CREATE PROCEDURE sp_selfenroll @accountnumber int
AS

Declare @acCheck nvarchar(20)
Declare @Exists nvarchar(10)
Declare @Identity    Int

Select @acCheck = AccountNumber From Customer Where AccountNumber = @accountnumber

If (@acCheck IS NULL) OR (@acCheck = '')
  Begin  
    Set @Exists = 'False'
  End
Else
  Begin
    Set @Exists =  'True'
  End

SELECT @Exists as 'duplicate'

GO

and ran

exec sp_selfenroll 1325 --true
exec sp_selfenroll 1452 --false

seemed to work ok ?

[blue]DBomrrsm[/blue]

 

[donutman]

I think he's having an application language problem not an SQL problem. SPs can return more than one recordset to a calling program at least AFAIK to VB. When you use a IF THEN sequence you may be returning a different number of recordsets depending upon which path is taken.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

I think Karl is correct. When the conditions are met, you are executing an insert and update which causes the "1 row(s) affected" messages to be returned as the first recordsets.

Add SET NOCOUNT ON at the top of your SP. This will suppress these info messages and mean the only resultset returned is your true/false value.

--James

 

[togatown]

Sorry so long in replying, it's been a week...

That's what I thought according to what I read. Following this if I did:

CREATE PROCEDURE sp_testprocedure
AS
SET NOCOUNT ON
Select 'firststatement' as returnval1
select 'secondstatement' as returnval
GO

The recordset returned should be the second one correct? But even this doesn't work...

Do I need soemthing else?

Mat

 

[nigelrivett]

no. That will return two recordsets.
Try it in query analyser and you will see.

I suspect the problem with your origonal code was not having the
set nocount on
at the top.

When it did the insert it was returning the rowcount from the insert as the first recordset.

Also a quote is a string delimitter not an identifier delimitter so one of

SELECT @Exists as duplicate
SELECT @Exists as [duplicate]

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.