|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Stored Procedure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Arpan De 2002-11-26, 5:23 am |
| 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
| |
| linda deng[MS] 2002-11-28, 5:23 am |
| 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.
| |
| linda deng[MS] 2002-11-28, 7:23 am |
| 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.
|
|
|
|
|