Update stmt and using variables.

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 2000 Forums

Transact-SQL (2000)

Update stmt and using variables.

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-08-18 : 09:23:04

Hi, Is there a better way of doing this???

I have to run an update statement in my script that actually updates or 30 columns. I am hoping I do not have to hard code the thirty columns.

Here is an example of what I have. I really want to know if you can do the @setSTring variable in the Update statement. If so how do you execute the SQL statement. Your help is greatly appreciated...

If you have a better suggestion, I would love to know about it.

DECLARE getColumns_cursor CURSOR FOR
Select syscolumns.name from syscolumns
inner join sysobjects on syscolumns.id = sysobjects.id
where sysobjects.Name = 'Table1'

OPEN getColumns_cursor
DECLARE @setString varchar(600)
DECLARE @getColumnName varchar(50)
SET @setString = 'Set '
-- Perform the first fetch.
FETCH NEXT FROM getColumns_cursor INTO @getColumnName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
set @setString = @setString + 'BOCC.' + @getColumnName
+ ' = ' + 'NOCC.' + @getColumnName + ', '
--print @setString
FETCH NEXT FROM getColumns_cursor INTO @getColumnName
END
--print @setString
CLOSE getColumns_cursor
DEALLOCATE getColumns_cursor
GO

UPDATE table1
@setSTring
WHERE RECORD_STATUS <> 'D' AND
dbo.table1.ID NOT IN
(SELECT ID
FROM nocc.dbo.table1)

SQLTEAMSteve
Starting Member

8 Posts

Posted - 2005-08-18 : 13:32:08

Use Openquery, that's how! You may need to phase-out your approach, and if you can, drop that cursor and opt for a While-Loop instead.

The openquery syntax would look like this:

declare @sSQL nvarchar(2500)
declare @setString varchar(10)
set @setString='new_value'
set @sSQL=('update table1 set column1=''' + @setString + '''
where record_status not in (''D'') and ID not in(select id from nocc.dbo.table1 with(nolock))')

print @ssql
exec sp_executeSQL @sSQL

Go ahead and run the code up to the 'print @sSQL', it will work.

_________________________
Stephen R Montgomery

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 13:43:10

Duplicate post. Only post once please.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53964

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources