CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresbetween
A J
A J

Reputation: 15

T-SQL and Stored Procedure gives different results with same query

I am new to group. Have a weird issue. My T-SQL query returns 69 rows, but the same query in a stored procedure returns zero results.

In one of the posts in StackOverflow, someone has switched the parameters / variables in EXEC statement, but that too does not work for me as shown below.

Instead of BETWEEN, I also tried with >= and <=, but no luck.

Thank you.

SELECT 
    T1.CustKey
FROM 
    INVOICEROWS T0 
INNER JOIN 
    INVOICE T1 ON T1.INVKEY = T0.INVKEY 
INNER JOIN 
    ITEMS T2 ON T2.PARTCODE = T0.PARTCODE  
INNER JOIN 
    CUSTOMERS T5 ON T5.CustKey = T1.CustKey
WHERE 
    T1.INVDATE BETWEEN '2018-03-26' AND '2018-03-26'
    AND ((T1.CustKey BETWEEN 'ABC' AND 'ABC') OR (T5.CustGroupKey = ''))
    AND ((T0.ItemKey BETWEEN 'PQR' AND 'STU') OR (T2.ItemGroupKey = ''))

RESULT: 69 rows with same value 'ABC' (which is correct)

ALTER PROCEDURE [dbo].[PROC1]
    (@DATEFROM AS DATE,
     @DATETO AS DATE,
     @CUSTKEYFROM AS NVARCHAR,
     @CUSTKEYTO AS NVARCHAR,
     @CUSTGROUPKEY AS SMALLINT,
     @ItemKeyFrom NVARCHAR(20),
     @ItemKeyTo NVARCHAR(20),
     @ItemGroupKey NVARCHAR(11)
    )
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT 
        T1.CustKey
    FROM 
        INVOICEROWS T0 
    INNER JOIN 
        INVOICE T1 ON T1.INVKEY = T0.INVKEY 
    INNER JOIN 
        ITEMS T2 ON T2.PARTCODE = T0.PARTCODE  
    INNER JOIN 
        CUSTOMERS T5 ON T5.CustKey = T1.CustKey
    WHERE 
        T1.INVDATE BETWEEN @DATEFROM AND @DATETO
        AND ((T1.CustKey BETWEEN @CUSTKEYFROM AND @CUSTKEYTO) OR (T5.CustGroupKey = @CUSTGROUPKEY))
        AND ((T0.ItemKey BETWEEN @ItemKeyFrom AND @ItemKeyTo) OR (T2.ItemGroupKey = @ItemGroupKey))
END

Running this stored procedure:

EXEC PROC1 '2018-03-26', '2018-03-26', 'ABC', 'ABC', '', 'PQR', 'STU', ''

returns zero rows

EXEC PROC1 '2018-03-26', '2018-03-26', 'ABC', 'PQR', '', 'ABC', 'STU', ''

also returns zero rows

Upvotes: 1

Views: 2351

Answers (2)

Razvan Socol
Razvan Socol

Reputation: 5694

In the stored procedure, you have declared the @CUSTGROUPKEY parameter as SMALLINT, but in the query you compare T5.CustGroupKey to ''. When you pass the value '' to a SMALLINT parameter, it becomes 0 and when you compare it to a nvarchar column you are searching for a '0', you are forcing all the values in that column to be converted to a smallint, before the comparison (due to the precedence of data types). Although the wrong parameter type could cause errors, it's not the reason why you are not getting any results. See the answer of marc_s for that.

Upvotes: 2

marc_s
marc_s

Reputation: 755541

I bet your problem is the wrong declaration of two of your stored procedure parameters:

ALTER PROCEDURE [dbo].[PROC1]
    (@DATEFROM AS DATE,
     @DATETO AS DATE,
     @CUSTKEYFROM AS NVARCHAR,
     @CUSTKEYTO AS NVARCHAR,
     @CUSTGROUPKEY AS SMALLINT,
     @ItemKeyFrom NVARCHAR(20),
     @ItemKeyTo NVARCHAR(20),
     @ItemGroupKey NVARCHAR(11)
    )

You have @CUSTKEYFROM and @CUSTKEYTO declared as just NVARCHAR. This means: you get a string of exactly ONE character length! This is rarely what you expect or want.....

Try to declare those two parameters with a length - as you should always do! - and I bet now your stored procedure will return the desired results!

ALTER PROCEDURE [dbo].[PROC1]
    (@DATEFROM AS DATE,
     @DATETO AS DATE,
     -- add explicit LENGTH to the NVARCHAR! However long you need it
     @CUSTKEYFROM AS NVARCHAR(25),
     @CUSTKEYTO AS NVARCHAR(25),

See Bad habits to kick : declaring VARCHAR without (length) for a great and extensive explanation of this "bad habit" that you should really kick - right now

Upvotes: 5

Related Questions