[dba-SQLServer]Update sproc

You didn't say whether you were using A2K or A2002. If the latter, go to the
queries tab and create a new function. Paste the code I supplied in and save
it. If A2K, then you need to open Query Analyzer and do the same thing
there.

 

-----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 12:16 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [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/ce741523/attachment.html>