T-SQl Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQl Help

Author

Topic

calgary001
Starting Member

2 Posts

Posted - 2012-05-04 : 17:45:23

My script is as below:

DECLARE @tablename SYSNAME,
@columnname SYSNAME,
@col_value AS INT,
@sql AS VARCHAR(1000)

DECLARE table_column CURSOR FOR
SELECT schema_name(o.schema_id)+'.'+o.name AS table_name,
c.name AS column_name
FROM sys.columns c JOIN sys.objects o ON c.object_id=o.object_id
WHERE c.is_identity=1
AND o.type ='U'
AND c.object_id IN (SELECT object_id FROM sys.change_tracking_tables)
ORDER BY table_name
OPEN table_column
FETCH NEXT FROM table_column INTO @tablename,@columnname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='SELECT @col_value'+'=MAX('+@columnname+') FROM '+@tablename
EXEC @sql
FETCH NEXT FROM table_column INTO @tablename,@columnname
END
CLOSE table_column
DEALLOCATE table_column

Error message is:
Msg 2812, Level 16, State 62, Line 9
Could not find stored procedure 'SELECT @col_value=MAX(testid) FROM test.test'.

I appreciate your suuggestion!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 17:51:19

That specific error message is because the syntax for executing dynamic sql requires brackets. Otherwise SQL Server thinks you are trying to execute a stored proc. So change to


...
SET @sql='SELECT @col_value'+'=MAX('+@columnname+') FROM '+@tablename
EXEC (@sql)
FETCH NEXT FROM table_column INTO @tablename,@columnname
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-04 : 18:42:02

are you trying to return value of variable through dynamic query? if it is you should using sp_executesql rather than EXEC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calgary001
Starting Member

2 Posts

Posted - 2012-05-07 : 10:44:41

Thanks! I fixed it.

DECLARE @tablename SYSNAME,
@columnname SYSNAME,
@col_value AS INT,
@sql AS NVARCHAR(1000),
@parmDefination AS NVARCHAR(1000)

DECLARE table_column CURSOR FOR
SELECT schema_name(o.schema_id)+'.'+o.name AS table_name,
c.name AS column_name
FROM sys.columns c JOIN sys.objects o ON c.object_id=o.object_id
WHERE c.is_identity=1
AND o.type ='U'
AND c.object_id IN (SELECT object_id FROM sys.change_tracking_tables)
ORDER BY table_name
OPEN table_column
FETCH NEXT FROM table_column INTO @tablename,@columnname
WHILE @@FETCH_STATUS = 0
BEGIN
--- Get current max PK value ---
SET @sql='SELECT @col=MAX('+@columnname+') FROM '+ @tablename
SET @parmDefination='@col INT OUTPUT'
EXEC sp_executesql @sql,@parmDefination, @col=@col_value OUTPUT

IF @col_value IS NOT null
PRINT @tablename+' '+convert(varchar(10),@col_value)

FETCH NEXT FROM table_column INTO @tablename,@columnname
END
CLOSE table_column
DEALLOCATE table_column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 16:25:48

welcome
didnt understand the reason for this logic though. What are you trying to do with max values for identity columns of the table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources