Hi
my problem is related with procedure as well as function. Actually, I want to get return value from proc1 to proc2. For that I am giving u a very simple examle in steps....

Step 1: I have to create a proc sp1

create proc sp1 @p1 int,@p2 int
as
return @p1 * @p2

Step 2: Now i execute sp1 and created proc sp2

create proc sp2
as
declare @x int
set @x = sp1 5,6
print @x

or

create proc sp2
as
declare @x int
declare @y int
declare @z int
set @y=4
set @z=6
set @x = sp1 @y,@z
print @x


Step 3: I want to run that proc sp2
but it gives error : Incorrect syntax near '5'.

Pls Solve this problem.

Note : Here is not allowed to use OUTPUT parmameter

Thanks in advance
Kumar Ashish
[email protected]

__________________
Thanks
Kumar Ashish
[email protected]

I think you can't do what you are trying to do with a stored procedure.

SPs return rowset as a result set, which actually is not easy to use in a later stages of the procedure. It can be done, and involves using the openrowset TSQL function, but it's usually not necessary and the overhead of using it is probably enough not to try.

The SPs result sets are on the other hand easy to use from other clients, it's just TSQL that has problems with it.

Your options are to make sp1 return an output paramter. The syntax would be:

create proc sp1 @p1 int,@p2 int, @p3 int output
as
set @p3 = @p1 * @p2

And then in sp2:

create proc sp2
as
declare @x int
declare @y int
declare @z int
set @y=4
set @z=6
execute sp1 @y, @z, @x output
print @x

You do have another option and that's to make sp1 a FUNCTION, not a PROC.

Regards,
Palli