March 22, 2002 at 3:11 pm
all the tables in my sql2000 db have a field which i need to update. I wrote a cursor which pulls up from sysobjects all tablenames. i pass the table names in an update stmt. when i try to save the cursor i get an error that the variable needs to be declared. I have declared the variable. Is there a better way to do this
March 22, 2002 at 3:39 pm
Could you post your code so we can see exactly what you're attempting?
Andy
March 22, 2002 at 4:06 pm
CREATE PROCEDURE usp_UPD
AS
DECLARE @tName VARCHAR(30)
DECLARE @CURSOR_USERNAME CURSOR
SET @CURSOR_USERNAME = CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE xtype='U'
OPEN @CURSOR_USERNAME
FETCH NEXT FROM @CURSOR_USERNAME INTO @tName
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE [dbname].[dbo].[@tName]
SET vchBy = 'XYZ'
WHERE vchBy ='ABC'
FETCH NEXT FROM @CURSOR_USERNAME INTO @tName
END
GO
March 22, 2002 at 6:47 pm
You need to build up the update statement as a string and then run with Exec or sp_executesql. SQL won't evaluate the tablename the way you have it now. You need something like this:
set @sql='update ' + @tablename + ' set fld1=''xyz'' where fld=''xx'''
exec(@sql)
You either have to double up your single quotes that would be in the update statement itself or use a true double quote - if you choose that method then you need to use set quoted_identifier off earlier in the proc.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply