November 12, 2009 at 7:33 am
I'm pretty sure this isn't a concern, and I tested it on my own and found no problem, but before I implement it into production I'd like to verify that I'm correct in my assessment.
If I have a stored procedure like the following:
CREATE PROCEDURE [dbo].[sp_getNewsletterStatsFromStatsTable]
(
@newsletterType INT = 1,
@newsletterFrequency INT = 1,
@newsletterLanguage INT = 0,
@newsletterRegion INT = 1,
@PageSize INT = 500000,
@CurrentPage INT = 1,
@StartDate SMALLDATETIME = NULL,
@EndDate SMALLDATETIME = NULL,
@newsletterSource INT = 0
)
And I call it with the following code:
DECLARE @newsletterType INT
DECLARE @newsletterSource INT
SET @newsletterType = 3
SET @newsletterSource = 2
EXEC sp_getNewsletterStatsFromStatsTable @newsletterType = @newsletterType, @newsletterSource = @newsletterSource
Can there be any problems that arise, due to the fact that A) I'm using a variable to pass in to a stored proc, but also using a variable to identify which parameter it is being assigned to, and B) the variable that I'm assigning and the variable that I'm assigning to both have the same name?
November 12, 2009 at 7:37 am
You can't declare a variable with the same name as a parameter.
I just tested this:
create proc ParamTest
(@Param1 int)
as
declare @Param1 int;
select @Param1 = 5;
select @Param1;
Got this:
Msg 134, Level 15, State 1, Procedure ParamTest, Line 4
The variable name '@Param1' has already been declared. Variable names must be unique within a query batch or stored procedure.
Or am I misunderstanding what you're doing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2009 at 7:41 am
Yeah you've misunderstood, sorry if I wasn't clear. The code:
DECLARE @newsletterType INT
DECLARE @newsletterSource INT
SET @newsletterType = 3
SET @newsletterSource = 2
EXEC sp_getNewsletterStatsFromStatsTable @newsletterType = @newsletterType, @newsletterSource = @newsletterSource
Is being run from somewhere else, it's not contained within the stored procedure - It is invoking the stored procedure.
November 12, 2009 at 7:53 am
I have never faced any problem by calling procedures like this. You can use the same name.
-Vikas Bindra
November 12, 2009 at 7:56 am
Yes, you can do that. You'll have problems with it if any of the parameters are for output values, but it does work for input parameters.
Because of the issues with output parameters, I avoid this practice, just for consistency.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2009 at 8:33 am
I second GSquared's note. I've typically done this so the code is easier to read. Same var name in calling batch as in the proc.
November 13, 2009 at 6:59 am
My prefered practice is to name parameters with their direction. @Date_in, or @Account_out, that kind of thing. Local variables don't get a suffix. Makes it really easy to tell the difference when you're ten pages into a complex proc.
Thus, I would end up with:
exec dbo.MyProc @Date_in = @Date
@Date would be the local variable. I find that easier to read and it helps me in debugging.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply