CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Jobin
Jobin

Reputation: 91

SQL Procedure not showing any value

Below is the procedure I created. When I run the query individually I find records in the database but when I execute the procedure it is not fetching any records. where have I gone wrong?

ALTER PROCEDURE [dbo].[GetTransferList] 

@date1 datetime='2015-01-01 00:00:00.000',
@date2 datetime='2017-01-01 00:00:00.000',
@shipto varchar(50)=''

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

    -- Insert statements for procedure here
    DECLARE
        @sql        NVARCHAR(MAX),                                
        @paramlist  NVARCHAR(4000),                              
        @nl         CHAR(2) = CHAR(13) + CHAR(10),
        @ParamDefinition  NVarchar(2000);

    SET @sql = 'SELECT A.ItemDescription,A.PurchaseOrderID,A.QuantityReceived,A.Price,A.StoreID,
            C.ItemType,C.BinLocation,
            B.PONumber,B.ShipTo,B.StoreID
            FROM [dbo].[PurchaseOrderEntry] A, [dbo].[PurchaseOrder] B,[dbo].[Item] C 
            WHERE A.PurchaseOrderID=B.ID AND A.ItemID=C.ID  ';

    IF (@date1 IS NOT NULL) AND  (@date2 IS NOT NULL  )                                           
        SET @sql += ' AND B.[RequiredDate] between @date1 AND @date2';

    IF @shipto IS NOT NULL --!='ALL'                                            
        SET @sql += ' AND B.ShipTo = @shipto ';

    SET @sql += ' GROUP BY C.BinLocation,A.Price,C.ItemType, B.ID ,A.ItemDescription,
            A.PurchaseOrderID,A.QuantityReceived,A.StoreID,B.PONumber,B.ShipTo,B.StoreID'

    Set @ParamDefinition =' @shipto varchar(50),
            @date1 datetime,
            @date2 datetime'

    Execute sp_Executesql     @sql, 
                            @ParamDefinition, 
                            @shipto, 
                            @date1,
                            @date2

    If @@ERROR <> 0 GoTo ErrorHandler
        Set NoCount OFF
    Return(0)

    ErrorHandler:
    Return(@@ERROR)
END

the query fetching value is below, 

SELECT A.ItemDescription,A.PurchaseOrderID,A.QuantityReceived,A.Price,A.StoreID,
            C.ItemType,C.BinLocation,
            B.PONumber,B.ShipTo,B.StoreID
FROM [dbo].[PurchaseOrderEntry] A, [dbo].[PurchaseOrder] B,[dbo].[Item] C 
WHERE A.PurchaseOrderID=B.ID AND A.ItemID=C.ID
GROUP BY C.BinLocation,A.Price,C.ItemType, B.ID ,A.ItemDescription,
            A.PurchaseOrderID,A.QuantityReceived,A.StoreID,B.PONumber,B.ShipTo,B.StoreID

Upvotes: 0

Views: 119

Answers (2)

Fred
Fred

Reputation: 5808

I suspect your biggest problem is @shipto varchar(50)=''. In your proc you then test for null

So this line....

IF @shipto IS NOT NULL

Will not be true if you don't pass anything in it will equal an empty string (unless you actually pass a null value in)

Change this to:

@shipto varchar(50) = null

Then you can test for null as you are doing and this code

IF @shipto IS NOT NULL --!='ALL'                                            
    SET @sql += ' AND B.ShipTo = @shipto ';

Will be test true if you do not provide this parameter.

If you ever want to add AND B.ShipTo = '' just pass in a value of '' to @shipto parameter.

However, this bit doesn't need to be dynamic as this will do the same thing

b.shipto = Coalesce(@shipto, B.ShipTo)

The same goes for your date parameters. They will only ever be null if you actually pass in a null value.

As you have no aggregate functions such as SUM, MAX, COUNT etc you don't need the group by.

So my personal preference here would be....

ALTER PROCEDURE [dbo].[GetTransferList] 

@date1 datetime=null,
@date2 datetime=null,
@shipto varchar(50) = null
--This will make your parameters optional. IE. You dont have to provide them when you call it
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    IF(@date1 IS NULL) SET @date1 = cast('1899-1-1' as datetime)  --This could be anytime in the past thatis prior to any records you hold
    IF(@date2 IS NULL) SET @date2 = cast('2100-1-1' as datetime) -- A date well in the future
    --You could also set directly as the default values for the parameter but if you do you will have a problem if `null` is passed in.

    SELECT A.ItemDescription,A.PurchaseOrderID,A.QuantityReceived,A.Price,A.StoreID,
            C.ItemType,C.BinLocation,
            B.PONumber,B.ShipTo,B.StoreID
            FROM [dbo].[PurchaseOrderEntry] A, [dbo].[PurchaseOrder] B,[dbo].[Item] C 
            WHERE A.PurchaseOrderID=B.ID AND A.ItemID=C.ID AND B.[RequiredDate] between @date1 AND @date2
            AND B.ShipTo = COALESCE(@shipto, b.ShipTo)



    If @@ERROR <> 0 GoTo ErrorHandler
        Set NoCount OFF
    Return(0)

    ErrorHandler:
    Return(@@ERROR)
END

Now you can provide one date (return all records up to a date or all records after a date) or two dates (return all records between dates) or not provide any to see them all. Same for @shipto.

Upvotes: 1

Rameshkan B
Rameshkan B

Reputation: 88

I strongly believe that your date parameter is not matching with the available records, as the query that works fine out side the procedure does not have date condition.

The Date parameters in the proc is marked with default values if the front end is C# (not sure about other front end code).

  1. The date parameter will be passed with minimum date value or
  2. Null value will be passed if you are not added date parameters in the front end code

But both the case date parameter will have some date values. Assign the date values to your working query as condition and check.

As asked by Mr.Fred, grouping in your query may not required unless if you are going to have any aggregated fields.

Upvotes: 0

Related Questions