Web Development

Ask something
Question Locked

Need Help

0 helpful
7 replies

by evga

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

7 replies

by evga

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"

by older mycroft

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

by evga

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

by jgarcia102066

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.

by evga

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'

This discussion is closed to new replies.