Your SP1 does not return a value, it returns a recordset. See
Re: Using result set Message #
644072>Sorry it's not work.
>It did some tests. My value return always 0.
>
>Here's a sample...
>declare @x int
>exec @x = SP1
>select @x
>
>result: 0
>
>Normally, SP1 should be return a value.
>
>Create procedure SP1 as
>declare @Y int
>set @y = 1
>select @y
>
>Thank.
--sb--