Setting a datetime in dynamic SQL

• Edmund Ward

  SSChasing Mays

  Points: 620

  More actions

  August 28, 2007 at 5:19 am

  #69945

  Hi, I'm having trouble the assiging a value to an output variable in a dynamic SQL query:

  DECLARE @deldate datetime

   

  SET @sql = 'SELECT 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'')'

   

  EXECUTE sp_executesql @sql, N'@deldate datetime OUTPUT', @deldate OUTPUT

   

  PRINT @sql

  PRINT ISDATE(@deldate)

  IF @deldate IS NULL

  PRINT isNULL(cast(@deldate AS varchar(20)),'@deldate is NULL')

  If I execute the printed @sql statement, I get a result set containing a date, but my attempts to print the variable @deldate suggest that it is null. What's going on here? Btw @dmsDb, dmsCompanyId and @stocknum are declared earlier in the code (not shown here) and printing of the @sql statement shows that these are fine.

  Cheers, Mundo

• 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.