Using exec in update query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Using exec in update query

Author

Topic

ashishmgupta
Starting Member

12 Posts

Posted - 2006-11-11 : 06:01:06

DECLARE @ReportBuilder uniqueidentifier
SET @ReportBuilder='F60421CC-D231-48AF-9C84-4C80244D8D1F'
UPDATE #tmpTestExecutions
SET StatusId=
Case
WHEN Application = @ReportBuilder
THEN exec 'SELECT FKStatusId FROM '+#tmpTestExecutions.ClientName+'..fwxPublishingQueue WHERE PKPublishingQqueueId='+#tmpTestExecutions.PkTestExecutionId
END

It says invalid syntax near 'exec'.How to update a column in a table from the result of exec?

Thank you in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-11 : 06:09:11

I am unable to understand what you are trying to do? You are using EXEC inside CASE statement which is not permitted and secondly what you are updating StatusID with?

Harsh Athalye
India.
"Nothing is Impossible"

ashishmgupta
Starting Member

12 Posts

Posted - 2006-11-11 : 06:19:09

I am trying to update the status of all the clients in the temp table when the application in of specific type.I didn't want to use cursor.If this statement is not permitted,is there any workaround?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-11 : 06:31:25

Please explain what are you trying to do. Provide table strucure and explain what do you want to achieve with some sample data and the result that you want.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-11 : 06:31:29

Is this what you want?

DECLARE @ReportBuilder uniqueidentifier
SET @ReportBuilder='F60421CC-D231-48AF-9C84-4C80244D8D1F' 

UPDATE t
SET StatusId = (select Top 1 FKStatusId 
                FROM fwxPublishingQueue
                WHERE PKPublishingQqueueId = t.PkTestExecutionId)
From #tmpTestExecutions t
Where Application = @ReportBuilder

Harsh Athalye
India.
"Nothing is Impossible"

ashishmgupta
Starting Member

12 Posts

Posted - 2006-11-11 : 06:42:07

Thank you for the reply.
fwxPublishingQueue table exists in multiple databases. "#tmpTestExecutions.ClientName" in my earlier query gives the database name and which I am suffixing with the fwxPublishingQueue to find the table in the appropriate database and thats the reason why I am using exec so that ClientName comes from #tmpTestExecutions and the same can be used to get FKstatusid from the fwxPublishingQueue from the appropriate database and FKStatusId is set to the StatusId in the
#tmpTestExecutions.

This could be achieved by using cursor but there could be performance hit.Do you have any other qworkaround? Please let me know If It is still not clear to you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-11 : 07:17:29

In that case, you will have to add an identity column to your temp table and then loop through the records.

Declare @i int,
        @max int

Select @i = min(id), @max = max(id) from #tmpTestExecutions

While @i <= @max
Begin
     Select @db = ClientName from #tmpTestExecutions
     Where id = @i

     set @sql = 'Update t
                 SET StatusId = (Select Top 1 FKStatusId FROM ' + @db + '..fwxPublishingQueue
     WHERE PKPublishingQqueueId = t.PkTestExecutionId)
                 From #tmpTestExecutions t
                 Where Application = ''' + @ReportBuilder + '''
                 and id = ' + convert(varchar(10), @i)

     exec(@sql)

     set @i = @i + 1

End

Harsh Athalye
India.
"Nothing is Impossible"

ashishmgupta
Starting Member

12 Posts

Posted - 2006-11-11 : 08:02:16

Is this not as good as a cursor when you consider performance impact?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-11 : 23:06:48

Nope!!

Because Cursor with it brings inherent locking overheads whereas this approach is free from that.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources