June 10, 2010 at 2:31 am
I have a stored procedure with a cursor that won't match the value I passed to it with the values in the table the cursor uses. I assume this has to do with the sysname data type that's being used in the system view.
How can I get this to work ? I stripped the code down to the essentials.
This would work fine if the view used varchar instead of sysname/nvarchar(128)
Thanks,
Walter
ALTER procedure [dbo].[tabel_def_test]
as
begin
declare @tablename sysname --nvarchar(128) Or maybe something else ?
declare @searchname sysname --nvarchar(128)
declare mycurs cursor for
select table_name
from information_schema.columns -- System view with column defs.
where table_name = @searchname -- Only return the rows for a given table.
set @searchname = 'TTDSLS951100' -- Table name to find in the view.
open mycurs
FETCH NEXT FROM mycurs into @tablename
print @@fetch_status -- This always returns '-1'
-- @tablename is empty.
-- Do some processing here...
WHILE @@FETCH_STATUS = -1 -- Always...
BEGIN
print @@fetch_status -- This always returns '-1'
FETCH NEXT FROM mycurs into @tablename
-- @tablename is empty.
-- Do some processing here...
END
close mycurs
deallocate mycurs
end
June 10, 2010 at 3:08 am
Walter, your SET statement must come before the DECLARE CURSOR
Like:
set @searchname = 'TTDSLS951100'
declare mycurs cursor for
select table_name
from information_schema.columns -- System view with column defs.
where table_name = @searchname -- Only return the rows for a given table.
June 10, 2010 at 5:15 am
Thanks ColdCofee,
Maybe I don't understand correctly but I thought that the DECLARE CURSOR was supposed to be executed just once.
I meant to do something like this.
declare cursor for select table_name from columns view. -- Do this only once.
-- Table t1 holds a list of table names.
for each record in table t1 put the table to find into @searchname
open cursor
fetch loop that retrieves all the records that match @searchname
Process each fetched record
close cursor
-- fetch the next record into @searchname from table t1 until there are no more.
endfor
I know there are different easier ways to solve this particular case but I need to get this solved
for my Oracle-to-SQLServer2005 conversion project.
Thanks,
Walter
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply