Rampidbyter Rampid byter: A byte of my .Net developing life.

I know anyone who uses a SQL database, or maybe even MySQL does stored procedures at some point. I also know at some point they’re going to want to abstract stored procedures to reduce redundancy. Anyway, if you’re looking to call an existing stored procedure from within a stored procedure it’s as simple as this…

InsertCustomerAddress – stored procedure being built

. . .

@customerID    bigint,
@address1 varchar(120),
@address2 varchar(100) = NULL,

. . .

DECLARE @returnValue smallint

EXECUTE @returnValue = InsertAddress @ address1, @address2

INSERT INTO dbo.CustomerAddress
(
                CustomerID,
                AddressID
)
. . .

You see that I declared a value of @returnValue in the datatype smallint. From there I simply executed another stored procedure that returns the unique ID created by inserting a new Address record. From there I used that return address ID to insert into my customer address reference table along with the customers ID.

That seems to be about it. Just call the Execute function from within the stored procedure; you don’t necessarily need to set a value to capture the return value as many procedures may not return any values. Just call Execute with a space, the name of the stored procedure to call, and the parameters to pass in. It is possible to put the keyword ‘output’ next to each parameter whose values will be altered within the called procedure.