[dba-SQLServer]Update sproc

Arthur -

Please excuse my ignorance when it comes to sprocs.

Where do I create the JustDate function?

 

Thanks,

Mark

 

-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com] 
Sent: Tuesday, February 11, 2003 12:08 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Update sproc

 

I think that it's the call to Now() that is confusing SQL. The error
points to INNER JOIN because that's the clause in which the error
occurs. Change the call from Int(Now()) to GetDate(). Since you int()'d
the date presumably you don't want the time part. Here is a udf that
simplifies this:

 

 

ALTER FUNCTION dbo.fn_JustDate

  ( @Date datetime )

RETURNS varchar(10)

AS

BEGIN

  RETURN ( CONVERT(varchar(10), at Date,101) )

END

 

Then you can change the last part to

 

SET tblCharges.PaidCode = 'CP', tblCharges.PaidDate =
JustDate(GetDate())

 

I think that should work.

 

Hth,

Arthur

 

-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Mark Boyd
Sent: February 11, 2003 11:43 AM
To: SQLServerList
Subject: [dba-SQLServer]Update sproc

 

I am trying to create a sproc in SQL2K that will update records.

I have the following code:

 

UPDATE tblCheckRequest 

INNER JOIN tblCharges ON (tblCheckRequest.ClaimNo = tblCharges.ClaimNo)
AND (tblCheckRequest.EndServDate = tblCharges.EndServDate) AND
(tblCheckRequest.CPT = tblCharges.CPT) AND (tblCheckRequest.DupCode =
tblCharges.DupCode) AND (tblCheckRequest.ProvNo = tblCharges.ProvNo) AND
(tblCheckRequest.PatientNo = tblCharges.PatientNo) 

SET tblCharges.PaidCode = 'CP', tblCharges.PaidDate = Int(Now());

 

When running this code, I receive the following message:

"Incorrect syntax near the keyword 'INNER'".

Should I not be using 'INNER JOIN ..'?

Very confused.

 

Any help is greatly appreciated.

Thanks,

Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030211/09138e56/attachment.html>