July 13, 2006 at 7:25 am
Hello friends...
I have wired situation
I want some dynamic name of table and i want data from that table but i am not getting please give me path....
Declare @TableName varchar(50)
declare cursor
open cursor
fetch cursor
while @@fetch_status = 0
begin
select * from @TableName
fetch next from cursor
end
close cursor
deallocate cursor
I am getting error
Server: Msg 137, Level 15, State 2, Line 55
Must declare the variable '@TableName'
Regards,
Papillon
July 13, 2006 at 7:42 am
Hi,
This cannot be the entire T-SQL script because it is incomplete. Anyway, to run the dynamic sql you can use the following method.
while @@fetch_status = 0
begin
declare @cmd varchar(8000)
set @cmd = 'select * from ' + @TableName
exec sp_executesql @cmd
fetch next from cursor
end
close cursor
deallocate cursor
Hope that helps,
July 13, 2006 at 8:11 am
Hello Karl
i ran your code but it is giving me error
declare @cmd varchar(8000)
set @cmd = 'select * from ' + @TableName
exec sp_executesql @cmd
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 23
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Regards,
Papillon
July 13, 2006 at 8:13 am
July 13, 2006 at 9:54 am
I'm curious as to what you're actually trying to do with this, Papillon. What is the cursor for?
Anyway, you should probably read this...
http://www.sommarskog.se/dynamic_sql.html#Dyn_table
And be aware of this (which you can probably use instead of the cursor)...
exec sp_MSforeachtable
@whereand = 'and name like ''%sys%''',
@command1 = 'select * from ?'
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 14, 2006 at 8:52 am
Check out this script. It currently drops all triggers from a database but the script can be used as a framework for tasks where you have to loop through a table and create dynamically SQL statements.
Cheers Rene
--START SCRIPT
-- Turn off row counting to get error messages only.
--
SET NOCOUNT ON
--
-- General variables
--
DECLARE @ServerName VARCHAR(50)
DECLARE @DBName VARCHAR(50)
DECLARE @TriggerName VARCHAR(50)
DECLARE @ViewName VARCHAR(50)
DECLARE @returnMessage VARCHAR(50)
DECLARE @Column VARCHAR(10)
--
-- String variables for dynamic sql
-- statement.
--
DECLARE @SQL_CURSOR NVARCHAR(4000)
DECLARE @SQL_DROP NVARCHAR(4000)
--
-- Variables for message.
--
DECLARE @message VARCHAR(255)
--
-- Variables for error message.
--
DECLARE @returnCode INT
DECLARE @rowCount INT
DECLARE @errorMessage VARCHAR(255)
--
-- Assign values to Variables
--
SET @ServerName = '' --Change server name!!!!
SET @DBName = ''
--
-- Declare the cursor statement first
--
SET @SQL_CURSOR = ''
SET @SQL_CURSOR = @SQL_CURSOR + 'SELECT name '
SET @SQL_CURSOR = @SQL_CURSOR + 'FROM '+ @ServerName + '.' + @DBName + '.dbo.sysobjects '
SET @SQL_CURSOR = @SQL_CURSOR + 'WHERE type = "TR"'
--Execute the DECLARE CURSOR statement
EXEC('DECLARE own_view CURSOR FOR '+@SQL_CURSOR)
--Open the cursor
OPEN own_view
--Fetch the first row
FETCH NEXT FROM own_view
INTO @TriggerName
--Nothing found in the first fetch
IF @@FETCH_STATUS 0
BEGIN
SET @returnMessage = 'Could not find database ' +@DBName
END
IF @@FETCH_STATUS = 0
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @ServerName + '.' +@DBName + '-Drop trigger: ' +@TriggerName
PRINT @message
--
--Prepare the drop trigger statement
--
use PriMCasIII
SET @SQL_DROP = ''
SET @SQL_DROP = @SQL_DROP + 'DROP TRIGGER '
SET @SQL_DROP = @SQL_DROP + 'dbo.' +@TriggerName --Drop trigger
--Execute the drop trigger statement
EXECUTE (@SQL_DROP)
--
--Error handling
--
SELECT @returnCode = @@error
IF @returnCode 0
Begin
SELECT @errorMessage = 'Error on DROP TRIGGER: ' + @TriggerName + ', RC: ' + Convert(char(6),@returnCode)
Raiserror 20001 @errorMessage
-- Return(20001)
End--End of IF
-- Get the next name
FETCH NEXT FROM own_view
INTO @TriggerName
END--End of WHILE
SET @returnMessage = 'Dropped triggers for database '+@DBName
END--End of IF
CLOSE own_view
DEALLOCATE own_view
-- Turn on row counting again.
SET NOCOUNT ON
--END SCRIPT
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply