help in writing dynamic sql query

28 Dec 2005 8:46 PM

Kumar

Hi,

I got most of this code from somewhere else on the net to compare two tables
...iam just trying to customize it to my requirement.

Here I want to compare table1(new),table2(old) to find out whats the new
values added or deleted in "name" column of Table1 when compared to table2.

-- Exec comparetables 'table1','table2','name','added'

Iam getting the added vlaues but iam not getting the deleted vlaues ..i
think the logic is good,somewhere iam doing mistake at dynamic sql code

pls help me guys ..

here is the code
create PROCEDURE CompareTables(@table1 varchar(100),

@table2 Varchar(100), @T1ColumnList varchar(1000),@status varchar(15),

@T2ColumnList varchar(1000) = '')

AS

-- Table1, Table2 are the tables or views to compare.

-- T1ColumnList is the list of columns to compare, from table1.

-- Just list them comma-separated, like in a GROUP BY clause.

-- If T2ColumnList is not specified, it is assumed to be the same

-- as T1ColumnList. Otherwise, list the columns of Table2 in

-- the same order as the columns in table1 that you wish to compare.

--

-- The result is all rows from either table that do NOT match

-- the other table in all columns specified, along with which table that

-- row is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''ADDED'' as newTableName, '+ @t1ColumnList + ' FROM ' +

@Table1 + ' UNION ALL SELECT ''DELETED'' as newTableName, '+ @t2ColumnList
+ ' FROM ' + @Table2

set @SQL = 'SELECT ' + @t1ColumnList + ' FROM (' + @SQL + ')A GROUP BY ' +

@t1ColumnList + ' HAVING COUNT(*) = 1 and min(newtableName) = '''+@status +
''''

exec ( @SQL)

28 Dec 2005 9:47 PM

Ryan Powers

The problem is with this part of the sql min(newtableName) = '''+@status

You are basically joining the added and deleted tables on all columns and
returning ones that did not match on all columns. But, then you are saying
only return the ADDED side.

Remove everything from and min(newtablename) until the end.

Then it should work.

Show quote

"Kumar" wrote:

> Hi,
>
> I got most of this code from somewhere else on the net to compare two tables
> ..iam just trying to customize it to my requirement.
>
> Here I want to compare table1(new),table2(old) to find out whats the new
> values added or deleted in "name" column of Table1 when compared to table2.
>
> -- Exec comparetables 'table1','table2','name','added'
>
> Iam getting the added vlaues but iam not getting the deleted vlaues ..i
> think the logic is good,somewhere iam doing mistake at dynamic sql code
>
> pls help me guys ..
>
> here is the code
> create PROCEDURE CompareTables(@table1 varchar(100),
>
> @table2 Varchar(100), @T1ColumnList varchar(1000),@status varchar(15),
>
> @T2ColumnList varchar(1000) = '')
>
> AS
>
> -- Table1, Table2 are the tables or views to compare.
>
> -- T1ColumnList is the list of columns to compare, from table1.
>
> -- Just list them comma-separated, like in a GROUP BY clause.
>
> -- If T2ColumnList is not specified, it is assumed to be the same
>
> -- as T1ColumnList. Otherwise, list the columns of Table2 in
>
> -- the same order as the columns in table1 that you wish to compare.
>
> --
>
> -- The result is all rows from either table that do NOT match
>
> -- the other table in all columns specified, along with which table that
>
> -- row is from.
>
>
>
> declare @SQL varchar(8000);
>
>
>
> IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
>
>
>
> set @SQL = 'SELECT ''ADDED'' as newTableName, '+ @t1ColumnList + ' FROM ' +
>
> @Table1 + ' UNION ALL SELECT ''DELETED'' as newTableName, '+ @t2ColumnList
> + ' FROM ' + @Table2
>
>
> set @SQL = 'SELECT ' + @t1ColumnList + ' FROM (' + @SQL + ')A GROUP BY ' +
>
> @t1ColumnList + ' HAVING COUNT(*) = 1 and min(newtableName) = '''+@status +
> ''''
>
> exec ( @SQL)