T_sql

[MTSkull]

Not sure where this question really fits, so I will ask it here.

 

I have a SQL Server Stored Procedure that returns a value. How do I call that procedure from anouther stored procedure and capture the return value?

 

Declare @MyNum bigint

Exec SP_GetNextInt 'MyTable'

 

I want to capture the return from SP_GetNextInt in @MyNum.

 

Thanks

MTS :confused:

[Joe Mamma]

Not sure where this question really fits, so I will ask it here.

 

I have a SQL Server Stored Procedure that returns a value. How do I call that procedure from anouther stored procedure and capture the return value?

 

Declare @MyNum bigint

Exec SP_GetNextInt 'MyTable'

 

I want to capture the return from SP_GetNextInt in @MyNum.

 

Thanks

MTS :confused:

I believe

select @MyNum = SP_GetNextInt

 

note. . .

SP_ is usually not used as that reflects system level stored procedures

does SP_GetNextInt return a scalar value???

 

consider making it a user defined function that way you can actually use it in a select statement, ie:

 

select dbo.GetNextInt(), myField, MyOther field from myTable where [criteria here]

 

remember that user defined functions must be prefixed by owner name

[Nerseus]

Normally you'd use EXEC to call a proc and get the value back (or just call another proc in general).

DECLARE @i int

EXEC @i = sp_proc 'param'

 

You might need parens, I can't remember:

EXEC @i = sp_proc('param')

 

As Joe mentioned, if you really want a prefix on your procs, I'd use something like "UP_..." instead of "SP_..." as SP is used for the system procs.

 

Also, if the proc is simply returning a scaler and not doing any other work, I'd move it into a function. If the proc is doing multiple things, like updates or inserts and then returning the int, I'd keep it in a proc.

 

-Nerseu

[jvcoach23]

sp_ is a special.. if you put that on your proc, they will run a little slower.. cause sql thinks that they are system proc.. it will end up looking in master db for them if memory servers