Setting a datetime in dynamic SQL

• Edmund Ward

  SSChasing Mays

  Points: 620

  More actions

  August 28, 2007 at 6:46 am

  #729744

  Oops! Newbie error! Changing the SET @sql bit to the following fixed this:

  SET @sql = 'SELECT @deldate = TransactionDate FROM OPENQUERY(' + @dmsDb + ', ''SELECT TOP 1 TransactionDate

  FROM VS_'

  + @dmsCompanyId + '_VehicleLogFile

  WHERE StockbookNumber = '

  + CAST(@stocknum AS varchar(20)) + ' AND NewAccountsStatus = ''''X''''

  ORDER BY TransactionDate'')'

  Note the extra '@deldate = ' bit at the beginning. Going away from my desk for lunch seemed to give me the fresh view of this to see the error.