update table set col = exec proc? possible?

Question

Post reply

update table set col = exec proc? possible?

Colin MacKenzie

SSC-Addicted

Points: 480
More actions
February 2, 2005 at 9:51 am

#68040

Hey... have a problem that's been plauging me for a bit.
I have to update a table, but if 1 value is null, I need to execute another proc and set the column to the returned string from the proc.

So far, my code is:

select @sqlStr = 'update a
set col' + cast(@x as varchar(10)) + ' = isnull(isnull(sv.TextValue, (exec GetES_SelectedQuestionOptions (select col' + cast(@x as varchar(10)) + ' from ' + @TableName + ' where ContactId = 0), sv.ES_ContactId), ''-'')
from ' + @TableName + ' a
inner join ES_StatValue sv on (select col' + cast(@x as varchar(10)) + ' from ' + @TableName + ' where ContactId = 0) = sv.ES_ObjectId
left join ES_Stat s on sv.ES_StatId = s.Id
where sv.ES_ContactId = a.ContactId'
exec(@sqlStr)
(yes, I know it's dynamic sql... I know a lot of people don't LIKE dynamic sql, but I have no choice)
notice the exec statement "(exec GetES_SelectedQuestionOptions (select col' + cast(@x as varchar(10)) + ' from ' + @TableName + ' where ContactId = 0), sv.ES_ContactId)"

is there ANY way I can do this, or somethign similar?

Thanks a TON!
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

if you convert your procedure to a function you will be able to do it pretty easily:

Update Set Colx = coalesce ( sv.TextValue, dbo.YourFunction(xx),'-')

from...

But I think you should have to take a better look at your design first. when you need to use @tableName as a variable this is usually not a good sign

HTH

* Noel

Colin MacKenzie SSC-Addicted Points: 480 More actions · Answer 2

thank you, and agreed, I'd love to be able to use more static sql...

however, I have to create a table with a n number of columns, and it's only applicable to the specific set of parameters passed into the proc. I can't create a temp table because of the varying number of columns.

I'll give the function a shot, here's hoping.

Thanks

Cheers

noeld SSC Guru Points: 96590 More actions · Answer 3

can't create a temp table because of the varying number of columns

Yes you can:

Create Table #Tbl1 ( i int)

declare @xx varchar(1000)

set @xx = 'Alter Table #Tbl1 add col1 int'

exec(@xx)

set @xx = 'Alter Table #Tbl1 add col2 int'

exec(@xx)

select * from #Tbl1 ---

* Noel

Colin MacKenzie SSC-Addicted Points: 480 More actions · Answer 4

ok, so playing with the function idea. There's just 1 problem. the proc that I was trying to run in my original update statement dealt with it's own temp tables, as I'm sure you know, functions don't like those. So, ok, I figured I'd just exec the proc directly from the function and return the output, again as I'm sure you know, functions will only run other functions and extended procs.

Any other idea's?

Cheers