CODEWITHSUNDEEP
c#sql-server
nichu09
nichu09

Reputation: 892

return exception when run one stored procedure

I have this stored procedure

create PROCEDURE spgetoutpatientidbyinpatientid
@pinpatientid int
AS

BEGIN

select  Patient_ID 
    from tblPatientAdmissionDetails 
    where ID=@pinpatientid and Status=1
END
GO

if this condition is wrong i got null exception.I want to return at least null value or anything.how can i avoid this error. In my code I use 

<pre lang="c#">string  ds = UserDataBase.ExecuteScalar(Command).ToString();</pre>

How can I change my sql query..? or how can I avoid this exception

Upvotes: 2

Views: 236

Answers (5)

Pranay Rana
Pranay Rana

Reputation: 176896

update your procedure like this might help you , return -1 if there is no record for you condition..

create PROCEDURE spgetoutpatientidbyinpatientid
@pinpatientid int
AS

BEGIN

  if exists( select  Patient_ID from tblPatientAdmissionDetails 
       where ID=@pinpatientid and Status=1) 
  begin
      select  Patient_ID from tblPatientAdmissionDetails 
       where ID=@pinpatientid and Status=1)
  end
  else 
  begin
      select -1 
  end
END
GO

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062770

Returning no rows is not really an error at the SP level; I would advise against changing the SP here. Really, then, it comes down to how you call it, and check for no-rows vs null vs data.

You can do this via the command, but it is a bit messy. My preference here would be to simplify; for example, to call with dapper with your existing stored procedure:

int id = 123;
var activePatient = connection.Query<int>(
    "spgetoutpatientidbyinpatientid", // command
    new { pinpatientid = id }, // parameters
    commandType: CommandType.StoredProcedure).Single();

where connection is any regular ADO.NET connection (SqlConnection etc). The use of LINQ's Single() will automatically throw an exception if there are zero or multiple matches. Otherwise, activePatient will contain the column returned. Note that you could replace <int> here with <Patient> to have it populate an entire object-model.

Upvotes: 0

Polly Shaw
Polly Shaw

Reputation: 3237

string  ds = String.Format("{0}", UserDataBase.ExecuteScalar(Command));

Upvotes: -1

codingbiz
codingbiz

Reputation: 26386

From C#

string ds = "";
object val = UserDataBase.ExecuteScalar(Command);
if(val != null)
   ds = val.ToString();

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

One approach:

declare @bogus_int int, @result int
set @bogus_int = -1000

select @result = Patient_ID 
from tblPatientAdmissionDetails 
where ID=@pinpatientid and Status=1

select coalesce( @result, @bogus_int )

Upvotes: 1

Related Questions