DECLARE @objname nvarchar(776)
SET @objname = '#temp'  -- give the table name here
 
DECLARE @objid int
DECLARE @sysobj_type char(2)
SELECT @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)


DECLARE @previousdate DATETIME, @currentdate datetime
DECLARE @union VARCHAR(15)
DECLARE @sql_final VARCHAR(MAX)
DECLARE @sql_fill_previous_table VARCHAR(1000)
DECLARE @sql_fill_current_table VARCHAR(1000)
DECLARE @sql_NEW_ORDER_ADDED VARCHAR(1000)
DECLARE @sql_ORDERS_SHIPPED VARCHAR(1000)
DECLARE @sql_ORDER_AMENDED_PREVIOUS VARCHAR(1000)
DECLARE @sql_ORDER_AMENDED_CURRENT VARCHAR(1000)

SET @union = 'UNION ALL'	
SET @previousdate = '9/14/2010'
SET @currentdate = '9/15/2010'	
SET @sql_fill_previous_table = 'SELECT * INTO #previousdate FROM #temp WHERE data_date = ' + '''' + CAST(@previousdate AS VARCHAR(50)) + ''''
SET @sql_fill_current_table =  ' SELECT * INTO #currentdate FROM #temp WHERE data_date = ' + '''' + CAST(@currentdate AS VARCHAR(50)) +''''



SET @sql_NEW_ORDER_ADDED = '
SELECT ''NEW ORDER ADDED'' AS [STATUS], * ,''NEW'' AS STAMP
FROM #currentdate c
WHERE c.custid NOT IN (SELECT custid FROM #previousdate)'

SET @sql_ORDERS_SHIPPED = '
SELECT ''ORDERS SHIPPED'', * ,''SHIPPED''
FROM
#previousdate p
WHERE p.custid NOT IN (SELECT custid FROM #currentdate) '

SET @sql_ORDER_AMENDED_PREVIOUS = '
SELECT ''ORDER AMENDED'', p.*,''PREVIOUS VALUE''
FROM
#previousdate p
INNER JOIN #currentdate C 
ON c.custid = p.custid
WHERE c.Data_Date <> p.Data_Date OR '




SET @sql_ORDER_AMENDED_CURRENT = '
SELECT ''ORDER AMENDED'', c.*,''CURRENT VALUE''
FROM
#previousdate p
INNER JOIN #currentdate C 
ON c.custid = p.custid
WHERE c.Data_Date <> p.Data_Date OR '

	
CREATE table #temp_table
(
id INT IDENTITY(1,1),
Column_Names VARCHAR(150)
)	
INSERT INTO #temp_table	
SELECT name FROM sys.all_columns WHERE object_id = @objid AND name NOT IN ('custid','Data_Date')

DECLARE @count INT
DECLARE @count_max INT
DECLARE @or_part VARCHAR(200)

SET @count = 1  
SET @count_max = (SELECT MAX(id) FROM #temp_table)
SET @or_part = ''
WHILE (@count < @count_max)
BEGIN
     DECLARE @column VARCHAR(150)
     
     SELECT @column = Column_Names FROM #temp_table WHERE id = @count
     SET   @or_part = ' c.' + @column + ' <> ' + ' p.'+@column +' OR '
     
     SET @sql_ORDER_AMENDED_PREVIOUS +=  @or_part
     SET @sql_ORDER_AMENDED_CURRENT +=   @or_part
     SET @count = @count + 1
END



SET @sql_final = @sql_fill_previous_table +  @sql_fill_current_table + @sql_NEW_ORDER_ADDED + @union +
				 @sql_ORDERS_SHIPPED + @union +
				 @sql_ORDER_AMENDED_PREVIOUS + @union +
				 @sql_ORDER_AMENDED_CURRENT
				 
				 
PRINT @sql_final
-- EXEC(@sql_final)

quote:

---

Originally posted by ashley.sql

You cannot hard code dynamic SQL it has to be created using information schema.


-----------------------------------------------------------------------------------------------
Ashley Rhodes

---

;WITH pre(custid,data_date) AS (SELECT custid,data_date FROM #temp WHERE data_date=@previousdate),
	cur(custid,data_date) AS (SELECT custid,data_date FROM #temp WHERE data_date=@currentdate),
	shipped(custid) AS (SELECT custid FROM pre EXCEPT SELECT custid FROM cur),
	added(custid) AS (SELECT custid FROM cur EXCEPT SELECT custid FROM pre)
SELECT 'ORDERS SHIPPED' Status, a.*, 'SHIPPED' STAMP FROM #temp a INNER JOIN shipped b ON a.custid=b.custid
UNION
SELECT 'NEW ORDER ADDED', a.*, 'NEW' FROM #temp a INNER JOIN added b ON a.custid=b.custid
UNION
SELECT 'ORDER AMENDED', a.*, 'PREVIOUS VALUE' 
FROM #temp a INNER JOIN pre b ON a.custid=b.custid AND a.data_date=b.data_date
WHERE a.custid NOT IN(SELECT custid FROM shipped)
UNION
SELECT 'ORDER AMENDED', a.*, 'CURRENT VALUE' 
FROM #temp a INNER JOIN cur b ON a.custid=b.custid AND a.data_date=b.data_date
WHERE a.custid NOT IN(SELECT custid FROM added)
ORDER BY custid, STATUS, Data_Date