Web Development

Question

Gravatar
0 Votes
Locked

Need Help

By EVGA ·
I am really new to Programing in SQL and trying to figure this nature out.

I am working on this statement below but cannot get it two work. I am always getting some kind of error. Any help on if there is some kind of Store Procedure like this that would work would be great.


SET ANSI_NULLS ON
GO

Create PROCEDURE [dbo].[TP__DischargeProgram]

AS

Declare
@PRGDISCHARGEDATE DATETIME,
@ADMISSIONKEY INT,
@CLIENTKEY INT,
@TIMEDISCHARGE DATETIME


IF @PRGDISCHARGEDATE <> NULL AND @TIMEDISCHARGE <> Null
Begin
UPDATE FD__AGENCY_ADMISSION SET DISCHARGE_DATE = @PRGDISCHARGEDATE, DISCHARGE_TIME =@TIMEDISCHARGE, Admission_Status = 'I'
WHERE CLIENTKEY = @CLIENTKEY AND ADMISSIONKEY= @ADMISSIONKEY
END

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

RE: "I am always getting some kind of error." ...

by OldER Mycroft In reply to Need Help
gravatar
Collapse -

Error

by EVGA In reply to RE: "I am always getting ...

When I test This procedure I get Incorrect syntax near '-'

using
EXECUTE TP__DischargeProgram 10-30-09, 118 ,117 , 10:40

I changed some things around and got a
"procedure has no parameters and arguments were supplied"

gravatar
Collapse -

RE: "TP__DischargeProgram 10-30-09" ...

by OldER Mycroft In reply to Error

Now, it's years since I dabbled with SQL, but why is your execution date set in the future?

gravatar
Collapse -

Testing

by EVGA In reply to RE: "TP__DischargeProgram ...

Just testing dates. When i figure this out the application I use will pass the correct date but testing I just picked a date.

gravatar
Collapse -

Re: Need Help

by jgarcia102066 In reply to Need Help

Since you are new to SQL, I'll offer a bit of advise. If you can, change the FD__Agency_Admission table to have one DATETIME field to account for the discharged date and time. Don't manage date and time separately, it will give you big headaches.

As for your procedure call, you need to put ticks (') around DATETIME values when you are passing into a stored procedure.

In the specific case of your stored procedure:

EXECUTE TP__DischargeProgram '10/22/2009',118,117,'11:45 AM'

In your table the Discharge_Date will show '10/22/2009 00:00:00.000' and the Discharge_Time will show '1/1/1900 11:45:00.000'

If you use only one discharge field you could capture '10/22/2009 11:45:00.000' as the combined date and time values.

Hope this helps.

gravatar
Collapse -

Thanks For your help

by EVGA In reply to Re: Need Help

I can apprecite what you said about using the date and time in one field makes total sense. However our aplication requires them to be to different fields who knows why. But I did answer my question and it is working with this query

SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[TP__DischargeProgram]


@PRGDISCHARGEDATE DATETIME,
@ADMISSIONKEY INT,
@CLIENTKEY INT,
@TIMEDISCHARGE DATETIME
AS

IF @PRGDISCHARGEDATE is not NULL AND @TIMEDISCHARGE is not Null
Begin
UPDATE FD__AGENCY_ADMISSION SET DISCHARGE_DATE = @PRGDISCHARGEDATE, DISCHARGE_TIME =@TIMEDISCHARGE, Admission_Status = 'I'
WHERE CLIENTKEY = @CLIENTKEY AND ADMISSIONKEY= @ADMISSIONKEY
END


By changing it to "is not null" I was able to compare the date time fields. I did learn about the ticks so Thanks

Ran with

EXECUTE TP__DischargeProgram '10/22/2009',118,117,
'11:45 AM'

Back to Web Development Forum

Start or search

Related Discussions

Related Forums