I am using SQL Server. I want it so if @variable is null, it is set to some value.
set nocount on
IF object_id('tempdb..##tmp') IS NOT NULL
BEGIN
DROP TABLE ##tmp
END
CREATE TABLE ##tmp (week varchar (25), users int, stamps int)
declare @inter varchar(100)
declare @qt_users int
declare @qt_stamps int
declare @comando varchar(5000)
declare @start date = null
declare @end date = null
declare @week datetime = DATEADD(DAY, 6, @start)
--if @start is null, set it to a value:
if (null!=@start)
begin
set @start = '20130101'
end
if (null!=@end)
begin
set @end = GETDATE()
end
while @start < @end
begin
select @qt_users = COUNT(distinct id_user)
from stamps
where dt_synchronization between @start and @week
select @qt_stamps = COUNT(id_stamp)
from stamps
where dt_synchronization between @start and @week
set @inter = convert(varchar(10),@start,105) + ' até ' +
convert(varchar(10),@week,105)
set @comando = 'insert into ##tmp(week, users, stamps) values (''' +
@inter + ''','''+
cast(@qt_users as varchar) +
''',''' + cast(@qt_stamps as varchar) + ''')'
exec (@comando)
set @start = @week + 1
set @week = dateadd(day, 6, @start)
end
select week, users, stamps from ##tmp
Use IS NULL to check instead, e.g.:
IF (@start IS NULL)
SET @start = '20130101'
Or, in one line:
SET @start = ISNULL(@start, '20130101')
Update: Also, you are setting @week too soon:
declare @week datetime = DATEADD(DAY, 6, @start) -- @start is NULL
change to:
declare @week datetime
-- IF checks here to set @start/@end if null...
SET @week = DATEADD(DAY, 6, @start)
On a side note, also worth refactoring your loop to set-based approach for performance. A tally/numbers table type approach is an option to research.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With