update statement

[DKL01]

create procedure spGetFileCategories
@documentid int

as

create table #temp10
( categoryid int,
documentid int,
path varchar(200)
)

insert into #temp10 select CATEGORYID, DOCUMENTID, " " from DOCUMENTCATEGORY where DOCUMENTID = @documentid

/* update statement here */


go

******************************

insert statement in the above proc inserts following values
categoryid documentid path
125 277
256 277

I need to write an update statement which updates path for each row. I have another
stored procedure spgetparent which accepts categoryid and retuns path for each categoryid.

I'm having problems writing update statement using spgetparent. I really appreciate any help.

Thanks

 

[TomSark]

You cannot set a column value to the result set fromcalling a stored procedure. Likewise, you cannot call a proc in a select statement for the purposes of creating an expression for a column value.

If you have SQL 2000, use a user defined function, which can be called from an update's set statement.

Or you can do this (example):

update a
SET a.path = PATHALIAS.newpath
from #temp10 a,
(select 'C:\PATH' as newpath) as PATHALIAS -- this is a cool trick!!! Tom Davis
tdavis@sark.com

http://www.sarkdallas.com