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]

This is not the right way to return values. You got to use an output parameter for returning values from the procedure. Read BOL (Book Online) for using output parameter in Stored Procedures.

_________________________
- Vijay G
Strive for Perfection

You have to call the EXECUTE function to call an SP and here's how to retrieve the return value:

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

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp