sql-server - SQL Server : If @variable is null set @variable = x

我正在使用SQL Server。我想要这样，如果@variable为null，则将其设置为某个值。

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

最佳答案

使用IS NULL进行检查，例如:

IF (@start IS NULL)
   SET @start = '20130101'

或者，一行:

SET @start = ISNULL(@start, '20130101')

更新:
另外，您太早设置@week了:

declare @week datetime = DATEADD(DAY, 6, @start) -- @start is NULL

改成:

declare @week datetime
-- IF checks here to set @start/@end if null...
SET @week = DATEADD(DAY, 6, @start)

另外，还值得将循环重构为基于集合的方法来提高性能。理货/数字表类型方法是研究的一种选择。