[dba-SQLServer]Update sproc

Arthur, Francisco, Doris -
Thanks for all your help.
I used a little from each of your replies to finally correct my issue.

The sproc runs smoothly, updating the fields correctly.
However, I still can't get the date to display without the time.
I tried using Arthur's idea with a udf, but am getting errors.
Is there an easier way to shorten the GetDate() function?

Thanks again,
Mark

-----Original Message-----
From: Francisco H Tapia [mailto:my.lists at verizon.net] 
Sent: Tuesday, February 11, 2003 12:10 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Update sproc

Mark,
   You can't just copy and paste Access code over to SqlServer.  From
the QA
(Query Analyzer) you can select the keyword UPDATE and then just hit the
key
combo Shift + F1 and help will automatically be brought up for the
proper
syntax of how to use UPDATE in Books Online (BOL).  I've taken the
liberty
to massage your sql statment, here is what I came up with, also note
that
Now() is an Access function not a Sql Server one thus it won't work, you
can
try using the GetDate() wich generates the date you want but not the
serial
number.  Also INT() is not a valid vunction in Sql Server... I hope this
sets you in the right direction...

UPDATE tblCheckRequest
SET tblCharges.PaidCode = 'CP', tblCharges.PaidDate = GetDate()
FROM 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)

-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Mark Boyd" <MarkBoyd at McBeeAssociates.com>
To: "SQLServerList" <dba-SQLServer at databaseadvisors.com>
Sent: Tuesday, February 11, 2003 8:43 AM
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.
:
:
:
:


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com