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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy