troubleshooting Question

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

Avatar of KavyaVS
KavyaVS asked on
ASP.NETMicrosoft SQL Server 2008
11 Comments1 Solution395 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros