Dynamic sql statements handling in sql server 2005

Creating Temporary temple i,e dynamic sql statements handling and execution.


Create Function Get_Checksum (

@DatabaseName varchar(100), @TableName varchar(100))RETURNS FLOAT

as

BEGIN

Declare @SQL nvarchar(4000) Declare @ColumnName varchar(100) Declare @i int Declare @Checksum float Declare @intColumns table (idRecord int identity(1,1), ColumnName varchar(255)) Declare @CS table (MyCheckSum bigint) Set @SQL = 'Insert Into @IntColumns(ColumnName)' + Char(13) + 'Select Column_Name' + Char(13) + 'From ' + @DatabaseName + '.Information_Schema.Columns (NOLOCK)' + Char(13) + 'Where Table_Name = ''' + @TableName + '''' + Char(13) + ' and Data_Type = ''int''' -- print @SQL exec sp_executeSql @SQL Set @SQL = 'Insert Into @CS(MyChecksum)' + Char(13) + 'Select ' Set @i = 1 While Exists( Select 1 From @IntColumns Where IdRecord = @i) begin Select @ColumnName = ColumnName From @IntColumns Where IdRecord = @i Set @SQL = @SQL + Char(13) + CASE WHEN @i = 1 THEN ' Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))' ELSE ' + Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))' END Set @i = @i + 1 end Set @SQL = @SQL + Char(13) + 'From ' + @DatabaseName + '..' + @TableName + ' (NOLOCK)' -- print @SQL exec sp_executeSql @SQL Set @Checksum = (Select Top 1 MyChecksum From @CS) Return isnull(@Checksum,0)


Declare @viewname as varchar(100)
SET @viewname = 'insite.view_Mike_Test'
Declare @query as nvarchar(2000)
SET @query = N'select * into #tempview from (Select ec.* from ' + @viewname+ ' ec
inner join insite.DataCollectionHistory dch ON ec.DataCollectionHistoryID = ec.DataCollectionHistoryID
WHERE dch.ParameterSETName = ec.parametersetname) a '
--exec(@query)
print @query

EXECUTE sp_executesql @query