Parameter names of Stored Procedures

[Dewald]

Hi all, I've often wondered about this and always simply worked my way around the issue but I would be curious to know what the correct approach is. Say I have a stored proc like so:

CREATE PROCEDURE myStoredProc
@myFirstParam INT = 0,
@mySecondParam INT = 0
AS
BEGIN
-- Do something
END

Now I want to call this proc, let's say from another proc where there is a local variable with the name @myLocalVar. I want to pass the value of that local variable to the second parameter of the stored proc so I call it as follows:

EXEC myStoredProc @mySecondParam = @myLocalVar

So far so good, but what if the name of that local variable was @mySecondParam (the same as the parameter name of the proc)? The following won't work:

EXEC myStoredProc @mySecondParam = @mySecondParam

So do I really have to create a new local variable with a different name so that I can pass it to the proc?

DECLARE @myNewVariable INT;
SET @myNewVariable = @mySecondParam;
EXEC myStoredProc @mySecondParam = @myNewVariable

Or is there a way I can pass a variable to a proc if the variable has the same name as the parameter name of the proc? PS. I'm deliberately using an example where the first parameter of the proc is ommitted because I'm specifically interested in the scenario where the parameter names of a stored proc has to be specified (i.e. not inferred by their order).

[Mycroft Holmes]

This works for me, local and procedure variable is the same.

DECLARE
@ContentTypeID INT

SET @ContentTypeID = 4

EXEC dbo.ContentTypeSelect @ContentTypeID = @ContentTypeID -- int

Never underestimate the power of human stupidity RAH

[Dewald]

How silly of me. I simply assumed that EXEC MyProc @MyParam = @MyParam wouldn't work. Just tested it and no problem at all. :doh: Thanks.

[jschell]

You do know that you can just list the input values without specifying the parameter name right? Thus your example becomes.

EXEC myStoredProc @mySecondParam

[sri080188]

yap it will work