Help with Stored Proc please - SQL Server Forums

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

Help with Stored Proc please

Author

Topic

mlawrence
Starting Member

5 Posts

Posted - 2014-09-26 : 05:21:42

Hi,
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.

I keep getting an error "Conversion failed when converting date and/or time from character string".

Here is the script...

DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)

DECLARE CursorSelect CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name

OPEN CursorSelect
FETCH NEXT FROM CursorSelect
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'insert DailyAudit (AuditDate,DataTable,RecordCount) select ' + getdate() + ',' + @table + ',' + Count(*) + ' from ' + @table
exec @sql
FETCH NEXT FROM CursorSelect
INTO @table
END
CLOSE CursorSelect
DEALLOCATE CursorSelect

Can anyone help please?
Thanks a lot!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-26 : 05:56:04

Change this:

SET @sql = 'insert DailyAudit (AuditDate,DataTable,RecordCount) select ' + getdate() + ',' + @table + ',' + Count(*) + ' from ' + @table

to:

SET @sql = 'insert DailyAudit (AuditDate,DataTable,RecordCount) select ''' + convert(varchar(10),getdate(),101) + ''',''' + @table + ''',' + cast(Count(*) as varchar(10)) + ' from ' + @table

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

mlawrence
Starting Member

5 Posts

Posted - 2014-09-26 : 06:15:55

Thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources