stored procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure help

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-08 : 05:51:36

hello everybody,
iam unable to generate the sequence number in the procedure.everything is working fine..the problem is when i execute the procedure i dump the records into a table.i have a oolumn in the table which need to store autogenerated numbers which i get through a function...but finally i get the same number for all the records inserted...the format of the number is 'AD1100000001'.it should get increment for next record i.e 'AD1100000002' and so on...
Thanx in advance...below is my code

USE [HMS_DEV]
GO
/****** Object: StoredProcedure [dbo].[USP_INSERT_INTERN_BILLINGS] Script Date: 05/13/2011 11:51:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
CREATE DATE : 07/03/2010
EXECUTION : [USP_INSERT_INTERN_BILLINGS] @DATE = '04/08/2011', @CREATEDDATE = '04/08/2011', @STATUS = 1, @CREATEDBY = 1,@MODIFIEDBY = 1, @MODIFIEDDATE = '04/06/2011' ,@ORG_ID=1,@CENTER_ID=1
*/

ALTER PROC [dbo].[USP_INSERT_INTERN_BILLINGS]
(
@DATE DATETIME,
@CREATEDDATE DATETIME = NULL,
@STATUS INT,
@CREATEDBY INT = NULL ,
@MODIFIEDBY INT = NULL ,
@MODIFIEDDATE DATETIME = NULL,
@ORG_ID INT,
@CENTER_ID INT
)
AS
BEGIN

IF EXISTS (SELECT NAME FROM MSDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TEMP_INTERIM%')
BEGIN
DROP TABLE #TEMP_INTERIM
END
SELECT
B.IPID,
CONVERT(VARCHAR,B.ADMISSION_DATE,112) AS ADMISSION_DATE,
B.IP_NUMBER,
B.PATIENT_CATEGORY_ID,
IPS.SERVICE_RATE*(SUM(IPS.SERVICE_QUANTITY))SERVICE_AMOUNT,
ISNULL(B.IP_LIMIT , 0) EMPLOYEE_LIMIT ,
C.MRN_NO,
C.PATIENT_FIRST_NAME + ' ' + C.PATIENT_MIDDLE_NAME + ' ' + C.PATIENT_LAST_NAME 'PATIENT_NAME',
ISNULL(F.COMPANY_NAME,' ') COMPANY_NAME,
ISNULL(E.LIMIT_AMOUNT,0) CREDIT_LIMIT,
ISNULL(B.DEPOSIT_AMOUNT,0) DEPOSIT_AMOUNT,
CASE WHEN B.PATIENT_CATEGORY_ID =1 -- FOR NORMAL PATIENT
THEN ( ISNULL(SERVICE_AMOUNT,0) -ISNULL(B.DEPOSIT_AMOUNT,0) )
WHEN B.PATIENT_CATEGORY_ID =2 -- FOR CREDIT PATIENT
THEN ( ISNULL(SERVICE_AMOUNT,0) - ISNULL(E.LIMIT_AMOUNT,0) )
WHEN B.PATIENT_CATEGORY_ID =3 -- FOR EMPLOYEE
THEN (ISNULL(SERVICE_AMOUNT,0) - ISNULL(B.IP_LIMIT,0) ) END AS BALANCE_CASH_AMOUNT,
CONVERT(VARCHAR,IPS.SERVICE_DATE,112) AS SERVICE_DATE
INTO #TEMP_INTERIM
FROM IP_ADMISSION B
JOIN PATIENT_DEMOGRAPHY C
ON B.PATIENT_ID = C.PATIENT_ID
LEFT JOIN IP_ADMISSION_CREDIT E
ON B.IPID = E.IPID AND E.[PRIMARY] ='Y'
LEFT JOIN CREDIT_COMPANY F
ON E.COMPANY_ID= F.COMPANY_ID
INNER JOIN IP_SERVICES IPS
ON IPS.IPID=B.IPID
WHERE B.DISCHARGE_DATE IS NULL
AND IPS.SERVICE_DATE BETWEEN B.ADMISSION_DATE
AND CONVERT(VARCHAR,@DATE,112)
GROUP BY B.IPID,
B.ADMISSION_DATE,
B.IP_NUMBER,
B.PATIENT_CATEGORY_ID,
IPS.SERVICE_RATE,
B.IP_LIMIT,
C.MRN_NO,
C.PATIENT_FIRST_NAME,
C.PATIENT_MIDDLE_NAME,
C.PATIENT_LAST_NAME,
F.COMPANY_NAME,
E.LIMIT_AMOUNT,
B.DEPOSIT_AMOUNT,
IPS.SERVICE_AMOUNT,
IPS.SERVICE_DATE

SELECT COUNT(*) FROM #TEMP_INTERIM

ALTER TABLE #TEMP_INTERIM ADD ID INT IDENTITY

DECLARE @I VARCHAR(50) , @IBN VARCHAR(50), @CNT INT, @IPID INT
SET @IPID = 1
-- COUNT FROM THE TABLE
SELECT @CNT = COUNT(IP_NUMBER) FROM #TEMP_INTERIM
DECLARE @CHECK INT
SELECT @CHECK = COUNT(*) FROM IP_INTERIM_BILLING
WHERE CONVERT(VARCHAR, @DATE, 112) = (SELECT CONVERT(VARCHAR, MAX(CREATED_DATE), 112) FROM IP_INTERIM_BILLING ) --ONLY ONE RECORD IS INSERTED
PRINT @DATE
PRINT @CREATEDDATE
PRINT @CHECK
IF (@CHECK =0)
BEGIN
INSERT INTO IP_INTERIM_BILLING
(
INTERIM_BILL_NO,
[DATE],
IPID,
PATIENT_ID,
SERVICE_AMOUNT,
DEPOSIT_AMOUNT,
BALANCE_AMOUNT,
[STATUS],
CREATED_BY,
CREATED_DATE,
MODIFIED_BY,
MODIFIED_DATE
)
SELECT
DBO.GETAUTOGENERATENUMBERS(@ORG_ID,@CENTER_ID,'INTERIMBILLING_NO'),-- @INTERIM_BILL_NO,
@DATE,
A.IPID,
C.PATIENT_ID,
A.SERVICE_AMOUNT,
A.DEPOSIT_AMOUNT,
A.BALANCE_CASH_AMOUNT,
@STATUS,
@CREATEDBY,
@CREATEDDATE,
@MODIFIEDBY,
@MODIFIEDDATE
FROM #TEMP_INTERIM A
JOIN IP_ADMISSION B
ON A.IPID = B.IPID
JOIN PATIENT_DEMOGRAPHY C
ON B.PATIENT_ID = C.PATIENT_ID
END
ELSE
PRINT 'Already Exists'

PRINT 'LAST DISPLAY'
SELECT DISTINCT IP.INTERIM_BILL_NO,
TI.IPID,
TI.ADMISSION_DATE,
TI.IP_NUMBER,
TI.MRN_NO,
TI.PATIENT_NAME,
TI.COMPANY_NAME,

CASE WHEN TI.PATIENT_CATEGORY_ID =2 -- FOR CREDIT PATIENT
THEN ISNULL(TI.CREDIT_LIMIT,0)
WHEN TI.PATIENT_CATEGORY_ID =3 -- FOR EMPLOYEE
THEN ISNULL(TI.EMPLOYEE_LIMIT,0)
ELSE
0 END
AS LIMIT ,
TI.SERVICE_AMOUNT,
TI.DEPOSIT_AMOUNT,
TI.BALANCE_CASH_AMOUNT
FROM #TEMP_INTERIM TI
LEFT OUTER JOIN IP_INTERIM_BILLING IP ON IP.IPID = TI.IPID
WHERE CONVERT(VARCHAR,IP.DATE,110) = CONVERT(VARCHAR,@DATE,110)

END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 08:41:07

I am inferring that the function to generate auto numbers is GETAUTOGENERATENUMBERS. Can you post the code for that function? Also, there may be better/easier/faster ways to generate the auto number other than calling a UDF

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 08:47:38

I think instead of this code:
>>
ALTER TABLE #TEMP_INTERIM ADD ID INT IDENTITY
<<

You need adding IDENTITY function in SELECT INTO statement like this:

SELECT ...., IDENTITY(int, 1, 1) AS ID 
INTO #Temp...
SELECT ....

______________________

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-04-08 : 08:59:33

quote:
Originally posted by sunitabeck

I am inferring that the function to generate auto numbers is GETAUTOGENERATENUMBERS. Can you post the code for that function? Also, there may be better/easier/faster ways to generate the auto number other than calling a UDF

Yes Sunitha you are right that is the function iam using.Below is the code for the function
USE [HMS_DEV]
GO
/****** Object: UserDefinedFunction [dbo].[GETAUTOGENERATENUMBERS] Script Date: 05/13/2011 18:07:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GETAUTOGENERATENUMBERS] (@ORGID INT,@CENTERID INT,@MODE VARCHAR(MAX))
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @FORMAT VARCHAR(MAX),
@CODE VARCHAR(20),
@MAXNO VARCHAR(MAX),
@CURYEAR VARCHAR(10),
@CURDATE DATETIME,
@CCYY VARCHAR(4)

SELECT @CODE = CODE FROM CENTERS WHERE CENTER_ID = @CENTERID AND ORG_ID = @ORGID
SELECT @CURDATE = GETDATE()
SELECT @CURYEAR = [YEAR] FROM FINANCIALYEAR WHERE @CURDATE BETWEEN STARTDATE AND ENDDATE

SET @CCYY = @CODE + @CURYEAR

IF EXISTS(SELECT [YEAR] FROM FINANCIALYEAR WHERE @CURDATE BETWEEN STARTDATE AND ENDDATE)
BEGIN
IF @MODE = 'INTERIMBILLING_NO'
BEGIN
SELECT @FORMAT = IP_INTERIMBILL_FORMAT FROM ORGANIZATION_CONFIG

IF @FORMAT = 'CCYY99999999'
BEGIN
SELECT @MAXNO = REPLACE(STR(CONVERT(INT,RIGHT(ISNULL(MAX(INTERIM_BILL_NO),0),8)) + 1, 8), SPACE(1), '0') FROM IP_INTERIM_BILLING
where INTERIM_BILL_NO LIKE ''+ @CCYY +'%'
SET @MAXNO = @CODE + CONVERT(VARCHAR(2),@CURYEAR) + @MAXNO
END
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 09:50:18

I don't fully understand all the logic you are using in the query and in the function, but it seems like there is nothing in that query that would cause it to return different numbers for each per row invocation of the function.

If you think of the select statement that has the function as if the operation is being done on all the rows at the same time, you can see why this would happen.

So you would need to do something else to get distinct ID's. Since you already have an identity column in #tmp table, one option would be to pass the ID as a parameter to the function, which then can be used to increment the number you want to return.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources