John Mahoney
asked on
Set a variable value in SQL Procedure
I want to check if a value is present in a field, if it is not present (null) then I want to set the value to be set to todays date using the GETDATE() function
This has to happen via a stored procedure.
What I need to happen, is for the stored procedure to set this value to the value of DateInitalPlan if it's present for this client in this table but if there is no value I want the value set to today's date.
In other words if the date is there i want it kept, if it's not I want it set to today's value. However my code is not working.
This works for the other fields but it is not setting the value of the field if it's null to today's date.
CREATE PROCEDURE [dbo].[spFillForm]
-- Add the parameters for the stored procedure here
(
@clientid_c varchar(40), -- this is passed from the form to the SP
@DOB DATETIME OUT, -- this value is output to field with this name in the form
@AdmitDate DATETIME OUT, -- output to named field
@InitialPlanDate DATE OUT -- output to named field
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Select @DOB = birthdate_d -- SETS DOB FROM client table
from ar.client
where ar.client.uniqueid_c = @clientid_c;
Select @AdmitDate = admdate_d -- SETS ADMIT DATE FROM clients table
from ar.client
where ar.client.uniqueid_c = @clientid_c;
-
Select @InitialPlanDate = ISNULL(DateInitialPlan, GETDATE() )
From
cd.arcTXPlan
where clientid_c = @clientid_c ; -- NOT WORKING.
END
This has to happen via a stored procedure.
What I need to happen, is for the stored procedure to set this value to the value of DateInitalPlan if it's present for this client in this table but if there is no value I want the value set to today's date.
In other words if the date is there i want it kept, if it's not I want it set to today's value. However my code is not working.
This works for the other fields but it is not setting the value of the field if it's null to today's date.
CREATE PROCEDURE [dbo].[spFillForm]
-- Add the parameters for the stored procedure here
(
@clientid_c varchar(40), -- this is passed from the form to the SP
@DOB DATETIME OUT, -- this value is output to field with this name in the form
@AdmitDate DATETIME OUT, -- output to named field
@InitialPlanDate DATE OUT -- output to named field
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Select @DOB = birthdate_d -- SETS DOB FROM client table
from ar.client
where ar.client.uniqueid_c = @clientid_c;
Select @AdmitDate = admdate_d -- SETS ADMIT DATE FROM clients table
from ar.client
where ar.client.uniqueid_c = @clientid_c;
-
Select @InitialPlanDate = ISNULL(DateInitialPlan, GETDATE() )
From
cd.arcTXPlan
where clientid_c = @clientid_c ; -- NOT WORKING.
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER