1

Im using following procedure in one of my applications

ALTER procedure [dbo].[FeeRecordSelect]
@GRNo varchar(4),
@PaymentModeId numeric(2) output
as
begin
SELECT @PaymentModeId = Students.PaymentModeId FROM Students where Students.GRNo = @GRNo
end 
GO

my question is: i want to select 1 in @PaymentModeId if I get no result for @GRNo

I Already have tried this

create procedure [dbo].[FeeRecordSelect10]
@GRNo varchar(4),
@PaymentModeId numeric(2) output
as
begin
SELECT @PaymentModeId = isnull(Students.PaymentModeId, 1) FROM Students where Students.GRNo = @GRNo
end 
GO
Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
  • 1
    Which RDBMS are you using? Please retag with the appropriate one. – Matt Fenwick Apr 22 '12 at 16:46
  • Might want to look [at this SO article](http://stackoverflow.com/questions/4823880/sql-server-select-into-variable) and also consider checking the value of @paymentModeID after the insert for null, then setting it to 1. – xQbert Apr 22 '12 at 16:49

2 Answers2

2

The reason IsNull() doesn't work is because the resultset doesn't contain any rows.

ALTER procedure [dbo].[FeeRecordSelect]  
@GRNo varchar(4),  
@PaymentModeId numeric(2) output
as  
begin  
set @PaymentModeId = isnull((select Students.PaymentModeId FROM Students where Students.GRNo = @GRNo), 1)
end   
GO
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
1

@PaymentModeId will only be set if there is at least one row returned by the select.

Put 

Set @PaymentModeId = 1

before the select to set the default value to 1. Now if there are no rows selected, it will still return 1.

Edit:

ALTER procedure [dbo].[FeeRecordSelect]
@GRNo varchar(4),
@PaymentModeId numeric(2) output
as
begin

Set @PaymentModeId = 1    

SELECT @PaymentModeId = Students.PaymentModeId FROM Students where Students.GRNo = @GRNo
end 
GO
Moose
  • 5,354
  • 3
  • 33
  • 46
  • I request you to please edit your asnwer in my coding.. so that i can understand exactly where you want me to use set @PaymentModeId = 1 –  Apr 22 '12 at 16:54
  • I don't know how to do it more clearly than that. Do you not see the edit at the bottom of my post? – Moose Apr 22 '12 at 16:56
  • Thanks Moose it is working. but I wish there could have been something like isnull. –  Apr 22 '12 at 16:59
  • I nevertheless have to accept your answer because it is anyhow working –  Apr 22 '12 at 17:02
  • If it works, upvoting and accepting the answer would be appreciated. the isnull will work if there is a row to return null, but since there is not, the assignment is never made. – Moose Apr 22 '12 at 17:02