asked on

Procedure working fine in query analyzer, but not returning data from .Net application with same parameters.

Hi,
This procedure working fine in sql query analyzer. But not returning data from the .Net webapplication. I passed same parameters from the .Net application also.

Any suggestions please.
Alter PROCEDURE [dbo].[usp_Search_BookDetails]
-- Add the parameters for the stored procedure here
@UserNo varchar(10),
@FirstName varchar(100),
@LastName varchar(100),
@Status varchar(255),
@ID int,
@Author varchar(100),
@Category varchar(50),
@CaseNo char(20),
@Date DateTime,
@Type varchar(50),
@Company varchar(100),
@ISBN varchar(17),
@iBook int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @iBook = 1
SELECT du.user_ID, du.user_no, du.first_name,du.last_name, du.created_by,
CASE
WHEN du.acct_type = 'P' THEN 'Primary User'
Else 'Joint User'
END as acct_type
,du.rec_type, du.created_On
from dbo.t_user_input du, dbo.t_book_info db
WHERE du.user_id = dv.user_id AND (@UserNo is null or du.user_no = @UserNo) AND
(@Category is null or dv.Category = @Category) and (@Type is null or dv.Type = @Type) and
(@ID is null or dv.ID = @ID) and (@Author is null or dv.Author = @Author) and
(@CaseNo is null or dv.case_no = @CaseNo) and (@Status is null or dv.Status = @Status) and
(@ISBN is null or dv.ISBN = @ISBN) and (@Company is null or dv.Company = @Company)

else

SELECT du.user_ID, du.user_no, du.first_name,du.last_name, du.created_by,
CASE
WHEN du.acct_type = 'P' THEN 'Primary Member'
Else 'Joint Member'
END as acct_type
,du.rec_type, du.created_On
from dbo.t_user_input du
Where (@UserNo is null or du.user_no = @UserNo) and (@FirstName is null or du.first_name = @FirstName) and (@LastName is null or du.last_name = @LastName)and (@Date is null or du.created_On=@Date)

END

GO

Thanks.

Kent Dyer

This concerns me.. I think you are looking for only one result - correct?

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

I would get your SELECT statement sorted out before running it through your SPROC..

Basically, the issue that I see is that if the person is a author or is a member, you are getting ambiguous results? While the IF..ELSE logic would work, I think you need to trim it down to one SQL Statement - IMHO.. It will reduce processing time, and logic problems..

Why are you doing an ALTER? Shouldn't this be a CREATE PROCEDURE?
I also incorporated the date check in the first section..

Alter PROCEDURE [dbo].[usp_Search_BookDetails]
      -- Add the parameters for the stored procedure here
      @UserNo varchar(10),
      @FirstName varchar(100),
      @LastName varchar(100),
      @Status varchar(255),
      @ID int,
      @Author varchar(100),
      @Category varchar(50),
      @CaseNo char(20),
      @Date DateTime,
      @Type varchar(50),
      @Company varchar(100),
      @ISBN varchar(17),
      @iBook int
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
if @iBook = 1
SELECT du.user_ID
, du.user_no
, du.first_name
, du.last_name
, du.created_by,
     CASE
      WHEN du.acct_type = 'P'  THEN 'Primary User'
      Else 'Joint User'
     END as acct_type
,du.rec_type
, du.created_On

from dbo.t_user_input du
, dbo.t_book_info db
    WHERE du.user_id = dv.user_id
 AND (@UserNo is null or  du.user_no = @UserNo) AND
    (@Category is null or dv.Category = @Category)
 and  (@Type is null or dv.Type = @Type) and
      (@ID is null or dv.ID = @ID)
 and (@Author is null or dv.Author = @Author) and
        (@CaseNo is null or dv.case_no = @CaseNo)
 and (@Status is null or dv.Status = @Status)
and
(@ISBN  is null or dv.ISBN = @ISBN)
 and  (@Company is null or dv.Company = @Company)
and (@Date is null or du.created_On=@Date)

ELSE
 
SELECT du.user_ID
, du.user_no
, du.first_name
, du.last_name
, du.created_by,
  CASE
      WHEN du.acct_type = 'P'  THEN 'Primary Member'
      Else 'Joint Member'
  END as acct_type
,du.rec_type
, du.created_On
from dbo.t_user_input du
    Where (@UserNo is null or  du.user_no = @UserNo)
 and (@FirstName is null or du.first_name = @FirstName)
 and (@LastName is null or du.last_name = @LastName)
and (@Date is null or du.created_On=@Date)

END

Open in new window

HTH,

Kent

KavyaVS

ASKER

The Person couldn't be the Member and Author.

If ibook = 1 the procedure need to return the book results in addition to member results.
Here two Tables(User and Book) are used.

If ibook other than 1 the procedure need to return only member information.
(User Table).

How to use single select statement?

Thanks.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Surendra Nath

if the stored procedure is giving the required result set in the SSMS or query analyzer, then it is waste of time to look into the Stored procedure code, in order to determine what went wrong.. as the issue lies in the .net end, where you are reading it.

My first concern would be did you set the commandType property correctly to stored procedure

public void CreateSqlCommand() 
{
   SqlCommand command = new SqlCommand();
   command.CommandTimeout = 15;
   [b]command.CommandType = CommandType.StoredProcedure;[/b]
}

Open in new window

IF the commandType is not properly set, we observe these issues sometimes.

if this is not the case, then it would help, if you can post the .net code as well along with the stored procedure code.

KavyaVS

ASKER

Hi Neo_jarvis,
Please find the attached .Net code and procedure.

Thanks
Code.txt
SP.txt

Surendra Nath

Try one more.thing remove the command timeout from your .net code and give it a try.

If that does not work then create a table with your stored procedure parameters as columns and then imsert the values of your Parameters into this table from your sp.

And see if these values are expected.

KavyaVS

ASKER

The problem with stored procedure where clause.
Where (@UserNo is null or du.user_no = @UserNo)
and (@FirstName is null or du.first_name = @FirstName)
and (@LastName is null or du.last_name = @LastName)
and (@Date is null or du.created_On=@Date)

When I checked single parameter in the Where clause it is giving the desired result.
Ex: Where (@UserNo is null or du.user_no = @UserNo)
Where (@FirstName is null or du.first_name = @FirstName)
Where (@LastName is null or du.last_name = @LastName)
Where (@Date is null or du.created_On=@Date)
But if I checked more than one parameter it is not giving the results.While checking the more than one parameter I am passing the parameters from application as @FirstName="" and @LastName="" and @Date=DbNull.Value

Something wrong with using @FirstName is null,@LastName is null and @Date is null.
in the where clause.

Any suggestions please.

Thanks

SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Deja Anbu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KavyaVS

ASKER

Thanks