getting count-dynamically from one table to result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting count-dynamically from one table to result

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 07:10:04

here i m inserting count dynamically from one table to result table

create Procedure GetCount(@LastRunDate as varchar(100))
AS
begin
-----------------------------------------------------------------------------------------
Declare @LastRunDate as varchar(100)
--temp table containing source table names and their corrosponding field names
IF OBJECT_ID(N'tempdb..#Count_MasterTBL', N'U') IS NOT NULL
DROP TABLE #Count_MasterTBL

CREATE TABLE #Count_MasterTBL
(Table_Name VARCHAR(100),
Date_Col_Name VARCHAR(100),
Data_Processed_Date DATETIME
)

delete from #Count_MasterTBL

Insert into #Count_MasterTBL values('table1','PRODU_DATE_DT1', getdate())
Insert into #Count_MasterTBL values('table2','PRODU_DATE_DT2', getdate())
Insert into #Count_MasterTBL values('table3','PRODU_DATE_DT3', getdate())

--select * from #Count_MasterTBL
create table #Count_ResultTBL
(Table_Name VARCHAR(100),

Date_Col_Name VARCHAR(100),
Record_Count int ,
Data_Processed_Date DATETIME
)
----------------------------------------------------------------------------------------------
Declare @SQL VarChar(1000)
Declare @RowCount int
DECLARE @GetRowCount CURSOR

SET @GetRowCount = CURSOR FOR
SELECT *
FROM #Count_MasterTBL
order by Table_Name

OPEN @GetRowCount
FETCH NEXT
FROM @GetRowCount INTO @RowCount
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQL ='SELECT * FROM ' + @GetRowCount.Table_Name + ' where ' + @GetRowCount.Date_Col_Name <= '+ QUOTENAME(CONVERT(VARCHAR(20), @LastRunDate, 121), ''')
print @SQL
--Exec ( @SQL)
insert into #Count_ResultTBL
(Table_Name ,
Date_Col_Name,
Record_Count ,
Data_Processed_Date
)
VALUES
(Table_Name,
Date_Col_Name,
@RowCount,
getdate()
)
END

CLOSE @GetRowCount
DEALLOCATE @GetRowCount

select * from #Count_ResultTBL

end

---------------------------------
can anybody what's wrong...i m doing.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 07:29:36

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 08:20:56

pls help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources