Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:
SET @.statusID = (SELECT statusID FROM statusList WHERE statusName='Created')
INSERT INTO Products (productname, statusID) VALUES (''widget1', @.statusID)
I want to simply the insert to perform (in one sproc):
SET @.statusID = EXEC deriveStatusID ('Created')
INSERT INTO Products (productname, statusID) VALUES (''widget1', @.statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @.statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Two steps:
1. Create the SP to return new ID like:
CREATE PROCEDURE [dbo].[deriveStatusID] @.Created Varchar(100),@.statusIDint OUTPUT
AS
SELECT@.statusID =statusID FROM statusList WHEREstatusName=@.Created
2. Modify your second SP as:
....
Declare @.NewStatusId Int
EXECderiveStatusID 'Created', @.NewStatusId OUTPUT
INSERT INTO Products (productname, statusID) VALUES (''widget1',@.NewStatusId)
No comments:
Post a Comment