Reputation: 15
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
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 , 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.'0'
Upvotes: 2
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