Update and using variables? - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Update and using variables?

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-08-18 : 09:21:11

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)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-18 : 12:38:03

Why don't you want to hard code the 30 columns? Doing this via dynamic SQL and a cursor is going to give you a huge performance hit.

Tara

KLang23
Posting Yak Master

115 Posts

Posted - 2005-08-18 : 13:14:04

Hi,

Personally, I would use a query like the one you've coded to enumerate the column names as a tool for generating my update query, but I wouldn't neccessarily use it to perform the update.

OTOH - depending on the circumstance, I may code a dynamic update statement consisting of only those columns that have values or changed values, but I wouldn't enumerate the columns from the system tables in my production code.

BTW: The stored procedures generated by the Replication Snapshot agent to update tables on subscribers use an interesting method of determining what to update. It's just interesting "reading".

Good luck.

SQLTEAMSteve
Starting Member

8 Posts

Posted - 2005-08-18 : 13:47:50

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

KLang23
Posting Yak Master

115 Posts

Posted - 2005-08-19 : 16:26:22

I see what you're trying to do.
Here's a trick to turn a vertical result set into a horizontal variable:

Declare @Table_Name sysname
Set     @Table_Name = 'My_Table_Name'
declare @x varchar(2000)
set @x = ' '   -- Or set Concat_NULL_Yields_NULL Off
Select @x = @x +
      'T1.' + Left(T2.Name,30)  + ' = T2.' +  Left(T2.Name,30) + ', '
From  sysobjects t1
     ,syscolumns t2
Where t1.name = @Table_Name
  and t1.id = t2.id
Order By ColOrder
print @x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources