having issues with DECLARE - 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

having issues with DECLARE

Author

Topic

amorg53666
Starting Member

3 Posts

Posted - 2009-08-05 : 02:37:21

I am writing a query for my database that checks to see the last time a table was updated. Tables that are updated are coming from a table that has a list of tables. The column for this table is ID and TableName (kind of like a dictionary table that I can add tables to at any time in the event that I need to keep an eye on more stuff).

The goal is to create a table that keeps track of this table, as well as add a column called LastUpdated, which tells me when the table was last updated. This script would be a scheduled event that I would run every 6 hours to make sure things are running as they should.

Here's what I got so far:

******************************************************************

USE [LiveDB]

GO

--drop table if exists
IF OBJECT_ID( N'SysDRTables_CoCH_LastUpdated', N'U') IS NOT NULL
drop table [dbo].[SysDRTables_CoCH_LastUpdated]
GO

--create the table
CREATE TABLE
[dbo].[SysDRTables_CoCH_LastUpdated](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[LastUpdated] [datetime],
CONSTRAINT [PK_SysDRTables_CoCH_LastUpdated] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

--Cursor
DECLARE @id int, @tab varchar(50), @updated datetime
DECLARE test_cursor CURSOR FOR
select ID, TableName from SysDRTables_CoCH
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @id, @tab
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('SET @updated = (SELECT max(RowUpdateDateTime) FROM ' + @tab + ' (NOLOCK))')
EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')
FETCH NEXT FROM test_cursor INTO @id, @tab
END
CLOSE test_cursor
DEALLOCATE test_cursor

**********************************************************************

I keep getting the following error(s):

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@updated".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@id".

Any assistance you can offer would be great. I am using SQL 2000, but I think this would work on 2005 (when I end up upgrading the whole database, or whatever the latest and greatest is when I decide to do so).

Thank you in advance with any suggestions.

amorg53666
Starting Member

3 Posts

Posted - 2009-08-05 : 02:38:42

One last thing....I know that the conditional deletion and the recreation of the table is fine...but just something in the cursor section.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-05 : 02:55:09

Hi

Can you check the colored part

EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')]

I think it should be like this....


DECLARE @id int, @tab varchar(50), @updated datetime,@SQL VARCHAR(max)
set @id			=	10
set @tab		=	'10'
set @updated	=	getdate()
SET @SQL	=	'INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES ('+CONVERT(VARCHAR(4),@id)+','+@tab+','''+CONVERT(VARCHAR(10),@updated,120)+''')'
SELECT @SQL
EXEC(@SQL)


INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (10,10,'2009-08-05')

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 03:29:48

Use sp_executesql instead of exec, to get value back from the executed query.

N 56°04'39.26"
E 12°55'05.63"

amorg53666
Starting Member

3 Posts

Posted - 2009-08-05 : 04:46:03

quote:
Originally posted by rajdaksha

Hi

Can you check the colored part

EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')]

I think it should be like this....

DECLARE @id int, @tab varchar(50), @updated datetime,@SQL VARCHAR(max)
set @id = 10
set @tab = '10'
set @updated = getdate()
SET @SQL = 'INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES ('+CONVERT(VARCHAR(4),@id)+','+@tab+','''+CONVERT(VARCHAR(10),@updated,120)+''')'
SELECT @SQL
EXEC(@SQL)

INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (10,10,'2009-08-05')

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

The only issue I have with this is that you're setting the value to @id and @tab to values, where in reality the select statement should set them in the FETCH clause.

I can see the benefit of creating the query and setting it in @sql, so I will add this.

Oh...and @tab is short for table, so that's why it's a varchar.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-05 : 04:55:45

Hi

I think first statment is right colored part...but you missed in second part

EXEC('SET @updated = (SELECT max(RowUpdateDateTime) FROM ' + @tab + ' (NOLOCK))')

EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')

I think it should be..like this

('+@id+','+@tab+','+ @updated+'))'

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources