Consider the following 2 sets of DDL:

CREATE TABLE ERNMST (ERNCODE VARCHAR(50),ERNNAME VARCHAR(500),ERNDED VARCHAR(5))
INSERT INTO ERNMST VALUES('COM.ALL','COMPENSATORY ALLOWANCE','E')
INSERT INTO ERNMST VALUES('ESIC','E.S.I.C.','D')
INSERT INTO ERNMST VALUES('ITDS','INCOME TAX DEDUCTED AT SOURCE','D')
INSERT INTO ERNMST VALUES('CRE','CONVEYANCE REIMBURSEMENT','E')
INSERT INTO ERNMST VALUES('PF','PROVIDENT FUND','D')
INSERT INTO ERNMST VALUES('V.ALL','VARIABLE ALLOWANCE','E')
INSERT INTO ERNMST VALUES('SP.ALL','SPECIAL ALOOWANCE','E')
INSERT INTO ERNMST VALUES('ESP','EXCESS SALARY PAID','D')
INSERT INTO ERNMST VALUES('FALL','FURNISHING ALLOWANCE','E')
INSERT INTO ERNMST VALUES('HRA','HOUSE RENT ALLOWANCE','E')
INSERT INTO ERNMST VALUES('DON','DONATION','D')
INSERT INTO ERNMST VALUES('CCA','CITY COMPENSATORY ALLOWANCE','E')
INSERT INTO ERNMST VALUES('AD.SAL','ADVANCE AGAINST SALARY','D')
INSERT INTO ERNMST VALUES('PT','PROFESSIONAL TAX','D')
INSERT INTO ERNMST VALUES('REC','REC ALLOWANCE','D')
------------------------------------------------------------------------------------------------------------
CREATE TABLE QL01ADET (ECODE INT,SDATE DATETIME,TYPE VARCHAR(10),ERNCODE VARCHAR(50),AMT INT)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(7,'4/30/2001','HRA',2051)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(8,'4/30/2001','REC',2000)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(9,'4/30/2001','PT',2100)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(10,'4/30/2001','V.ALL',2200)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(7,'4/30/2001','SP.ALL',1051)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(8,'4/30/2001','ESP',1000)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(9,'4/30/2001','CRE',1100)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(10,'4/30/2001','ITDS',1200)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(7,'4/30/2001','ADSAL',200)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(8,'4/30/2001','COM.ALL',100)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(9,'4/30/2001','ESIC',400)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(10,'4/30/2001','FALL',450)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(7,'4/30/2001','DON',100)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(8,'4/30/2001','CCA',300)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(9,'4/30/2001','HRA',250)
INSERT INTO QL01ADET(ECODE,SDATE,ERNCODE,A
MT) VALUES(10,'4/30/2001','PT',500)
------------------------------------------------------------------------------------------------------------

Consider the following stored procedure:

CREATE PROCEDURE AlternateErnDed
@lasterncode varchar(20)=NULL
AS
DECLARE
@sql varchar(8000)

DROP TABLE DTable
DROP TABLE ETable

CREATE TABLE DTable(ERNID INT IDENTITY(1,1),ERNCODE VARCHAR(50),ERNNAME VARCHAR(500),ERNDED VARCHAR(5))
CREATE TABLE ETable(ERNID INT IDENTITY(1,1),ERNCODE VARCHAR(50),ERNNAME VARCHAR(500),ERNDED VARCHAR(5))

IF(@lasterncode=NULL OR @lasterncode='')
BEGIN
SET @sql='INSERT INTO DTable(ErnCode,ErnName,ErnDed)

SELECT DISTINCT(adet.ErnCode),ern.ErnName,ern.ErnDed
FROM QL01ADET AS adet,ERNMST AS ern
WHERE adet.ErnCode=ern.ErnCode AND ern.ErnDed="D" ORDER BY adet.ErnCode ASC'
PRINT ('NO LAST ERNCODE')
END
ELSE
BEGIN
SET @sql='INSERT INTO DTable(ErnCode,ErnName,ErnDed)

SELECT DISTINCT(adet.ErnCode),ern.ErnName,ern.ErnDed
FROM QL01ADET AS adet,ERNMST AS ern
WHERE adet.ErnCode<>"' + @lasterncode + '" AND adet.ErnCode=ern.ErnCode AND ern.ErnDed="D" ORDER BY adet.ErnCode ASC'
PRINT ('YES LAST ERNCODE')
END
EXEC (@sql)

SET @sql='INSERT INTO ETable(ErnCode,ErnName,ErnDed)

SELECT DISTINCT(adet.ErnCode),ern.ErnName,ern.ErnDed
FROM QL01ADET AS adet,ERNMST AS ern
WHERE adet.ErnCode=ern.ErnCode AND ern.ErnDed="E" ORDER BY adet.ErnCode ASC'
EXEC (@sql)

SET @sql='SELECT ErnID,ErnCode,ErnName,ErnDed
FROM DTable
UNION
SELECT ErnID,ErnCode,ErnName,ErnDed
FROM ETable
ORDER BY ErnID,ErnDed DESC'
EXEC (@sql)
-----------------------------------------------------------------------------------------------

Now when I am executing the above stored procedure in the Query Analyzer using

EXEC AlternateErnDed 'REC'

then as expected, all the records except for the record where the ErnCode is REC is retrieved (i.e. total 14 records) but if I am not passing any parameter to the above stored procedure i.e. executing it using

EXEC AlternateErnDed

then only those ErnCodes where ErnDed is "E" gets retrieved (i.e. only 7 records) where as the ErnCodes where the ErnDed value is "D" aren't retrieved. Actually all the 15 ErnCodes should be retrieved in this case. Why is this happening? How do I ensure that even if no parameter is passed to the stored procedure (which expects an OPTIONAL parameter), all the ErnCodes (i.e. total 15 records) get retrieved? In fact, to debug the procedure, I have introduced the lines

PRINT ('NO LAST ERNCODE') and PRINT ('YES LAST ERNCODE')

in the IF & the ELSE condition respectively. Now if a parameter (say, REC) is passed to the stored procedure, then as expected, it prints "YES LAST ERNCODE" but if no parameter is passed then also it prints out "YES LAST ERNCODE" where as it should print "NO LAST ERNCODE"!!!!!

Thanks,

Arpan

Report this post to a moderator

Hi Arpan,

Thank you for your DDL statements and the sample data. It is convenient for
us to test the issue on our sides.

It appears that the issue is related to the 'ANSI_NULLS' option. When this
option is ON, a select statement using WHERE column_name = NULL returns
zero rows even if there are null values in column_name. So the statement
'@lasterncode=NULL' is false even the variable @lasterncode is actually
NULL. For more information, please refer to the topic: SET ANSI_NULLS on
Microsoft SQL Server Books Online.

Please try the following steps to workaround this issue.
1. Drop the procedure
2. Add the 'set ANSI_NULLS OFF' statement before the procedure definition
3. Recreate the procedure.

Please see below. I have included an excerpt here for your reference.

--Drop the procedure
drop proc AlternateErnDed

--The procedure's new definition
set ANSI_NULLS OFF
GO
CREATE PROCEDURE AlternateErnDed
@lasterncode varchar(20)=NULL
AS
DECLARE
@sql varchar(8000)
if exists(select count(*) from sysobjects where name = 'DTable' and type=
'U')
begin
DROP TABLE DTable
end

if exists(select count(*) from sysobjects where name = 'DTable' and type=
'U')
begin
DROP TABLE ETable
end

CREATE TABLE DTable(ERNID INT IDENTITY(1,1),ERNCODE VARCHAR(50),ERNNAME
VARCHAR(500),ERNDED VARCHAR(5))
CREATE TABLE ETable(ERNID INT IDENTITY(1,1),ERNCODE VARCHAR(50),ERNNAME
VARCHAR(500),ERNDED VARCHAR(5))
IF(@lasterncode = NULL OR @lasterncode='')
...

If anything is unclear, please let me know.

Sincerely,

Linda Deng
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Report this post to a moderator

Hi Arpan,

Another workaround is to just change this statement as below simply:

IF(@lasterncode is null OR @lasterncode='')

Please try the steps:
1. Drop this procedure.
2. Change the corresponding statement in your original procedure definition
to the new one above.
3. Recreate this procedure.

If you have any further questions or concerns, please let me know.

Sincerely,

Linda Deng
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Report this post to a moderator