April 7, 2008 at 4:03 am
Hi
in a stored procedure i want to check for a null date and assign a default value if it is null:
alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
if isnull(end_date) then
set @end_date = @start_date
end if
April 7, 2008 at 4:05 am
bill.humphrey (4/7/2008)
Hiin a stored procedure i want to check for a null date and assign a default value if it is null:
alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
if isnull(end_date) then
set @end_date = @start_date
end if
Try this instead:
if @end_date is null set @end_date = @start_date
April 7, 2008 at 4:09 am
I get an error "incorrect syntax near keyword 'if'.
alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
if @end_date is null set @end_date = @start_date
as
begin
SELECT TOP 100 PERCENT id, report_date, priority_text,
MIN(DATEDIFF(mi, dt_switch, dt_arrive)) AS resp,
COUNT(id) AS no_of_resp,
CASE WHEN MIN(datediff(mi,
dt_switch, dt_arrive)) <= 19 THEN 1 ELSE 0 END AS InStd
--INTO #a_temptable_catb
FROM dbo.cadcasedetails
WHERE (NOT (dt_arrive IS NULL))
AND (report_date >= CONVERT(DATETIME, @start_date, 103))
AND (report_date < CONVERT(DATETIME, @end_date, 103))
--AND (report_date < CONVERT(DATETIME, @start_date, 103))
AND (priority_text = 'B')
GROUP BY id, report_date, priority_text
--SELECT COUNT(InStd) AS total_resp, SUM(InStd) AS in_std_resp, ROUND(CAST(SUM(InStd) AS float) / COUNT(InStd) * 100, 2) AS Expr3
--FROM #a_temptable_catb
--DROP TABLE #a_temptable_catb
END
GO
April 7, 2008 at 4:18 am
[font="Verdana"]
bill.humphrey (4/7/2008)
I get an error "incorrect syntax near keyword 'if'.alter procedure sp_list_orcon_cat_b_v2
@start_date nvarchar(19),
@end_date nvarchar(19),
@priority nvarchar(1),
@response integer
AS
if (@end_date is null) then
begin
set @end_date = @start_date
end
begin
SELECT TOP 100 PERCENT id, report_date, priority_text,
MIN(DATEDIFF(mi, dt_switch, dt_arrive)) AS resp,
COUNT(id) AS no_of_resp,
CASE WHEN MIN(datediff(mi,
dt_switch, dt_arrive)) <= 19 THEN 1 ELSE 0 END AS InStd
...
Check it now after applying the changes highlighted in bold. Refer the Create Procedure from BOL as well.
Thanks,
Mahesh
[/font]
MH-09-AM-8694
April 7, 2008 at 4:18 am
Bill
Try this - doesn't need any IFs and does it in one line:
SET @end_date = COALESCE(@end_date, @start_date)
John
April 7, 2008 at 4:18 am
ah I shifted the if statement inside the begin clause and it accepts it
April 8, 2008 at 8:34 am
Replace your if statement with the following:
Set @end_date = ISNULL(@end_date, @start_date).
The ISNULL() function replaces a null value with the specified replacement value. It is not used to check for a null value as you used it in your if statement.
You can also use COALESCE(@end_date, @start_date). This function returns the first non-null value.
KDW
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply