Using select or set with exec

[ChrisFarrugia]

Dear All, I need to set the value of a variable with the result of a stored procedure. I need something on these lines: set @productCount = exec procGetCount I did not manage to get this to work. I am using Microsoft Sql Server 7 and 2000. Thanks a lot, Chris

[Alsvha]

You can't (in those versions at least) that I know of. Either you have to make it into a function or use output parameters.

--------------------------- Blogging about SQL, Technology and many other things

[SomeGuyThatIsMe]

in sql 2000 sp4(mabye in other versions of 2000 but i dont have any to test on) EXEC @var = procGetCount but you have to RETURN the value out of the stored procedure for it to work and sql 2000 can only return int values. example procedure code DECLARE @var INTEGER SET @var = 3 RETURN @var you can also put SELECT @var before the return if you need to as well.