Sp required based on year - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sp required based on year

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-06-11 : 06:23:18

Hi,
iam having one stored procedure where i am updatig table if record exists or inserting if not exists.but whilw updating i need to update it based on year too.

procedure is some how like this.


Create Procedure SPEMRPATACCESSDATE(@PatientId Varchar(500),@AccessDate Datetime) AS
Declare
    @Id		        NUMERIC(20,0),
    @First_Access_Date	DATETIME,
    @Last_Access_Date	DATETIME,
    @Patient_ID	        VARCHAR(20),
    @Created_Date       DATETIME
BEGIN
--FOR INSERTING THE TABLE EMRPATIENTACCESSDATE
    SET @Patient_ID=@PatientId
	  IF EXISTS (SELECT * FROM EMRPATIENTACCESSDATE WHERE PATIENT_ID=@PatientId)
			BEGIN
			   UPDATE EMRPATIENTACCESSDATE SET LAST_ACCESS_DATE = @AccessDate
			END
	  ELSE
			BEGIN
			    INSERT INTO EMRPATIENTACCESSDATE(PATIENT_ID,FIRST_ACCESS_DATE,LAST_ACCESS_DATE)
			      SELECT @PatientId,'',@AccessDate
			END

--LATER INSERTING THE TABLE EMRPATIENTACCESSDATEHISTORY DUMPING AVAILABLE DATAS
                        BEGIN		               
                            INSERT INTO EMRPATIENTACCESSDATEHISTORY(PATIENT_ID,ACCESS_DATE) VALUES(@Patient_ID,@AccessDate);
	                END
END
GO

like i want year(datefield) to verify in update where condition using date parameter of sp.how to do..

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-11 : 06:45:35

Can be elaborate your query.

as i understand, you need to update only Year to Column LAST_ACCESS_DATE?

Lets unLearn

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-06-11 : 07:08:32

try this in WHERE clause
YEAR(LAST_ACCESS_DATE) = YEAR(@AccessDate)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-11 : 08:20:20

"YEAR(LAST_ACCESS_DATE) = YEAR(@AccessDate)"

doing this will mean not being able to use any INDEX involving the LAST_ACCESS_DATE column.
not too bad if the input table is small, but poor otherwise.

search here for alternatives posted by TKizer, kristen and others. (it involves a dateadd formula on the right hand side)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-11 : 08:46:01

You have much bigger problems than that! Your UPDATE clause updates all records in table, not only the desired record.
Try this

CREATE PROCEDURE dbo.SPEMRPATACCESSDATE
(
	@PatientId VARCHAR(500),
	@AccessDate DATETIME
)
AS

SET NOCOUNT ON

UPDATE	EMRPATIENTACCESSDATE
SET	LAST_ACCESS_DATE = @AccessDate
WHERE	PATIENT_ID = @PatientId

IF @@ROWCOUNT <> 1
	INSERT	EMRPATIENTACCESSDATE
		(
			PATIENT_ID,
			FIRST_ACCESS_DATE,
			LAST_ACCESS_DATE
		)
	VALUES	(
			@PatientId,
			@AccessDate,
			@AccessDate
		)

INSERT	EMRPATIENTACCESSDATEHISTORY
	(
		PATIENT_ID,
		ACCESS_DATE
	)
VALUES	(
		@Patient_ID,
		@AccessDate
	)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources