September 28, 2015 at 2:18 pm
Why can you use parameters that are different in sp_executesql but the names have to match exactly if you call them directly.
If I have an sp:
ALTER PROCEDURE [dbo].[R01OrderForm]
@RegionId varchar(max),
@DistrictId varchar(max),
@DealerId varchar(max),
@ProductSegmentId varchar(max),
@ReportPeriodId int,
@LoginLevel varchar(10)
AS
I have to call it like:
EXEC R01OrderForm
@RegionID='10, 30, 40, 50, -99',
@DistrictID=' 20',
@DealerID=' 20,-99',
@ProductSegmentID='-99, 1321, 1322',
@ReportPeriodId='35',
@LoginLevel='DM'
The parameter names are exact.
But in sp_executesql, you can call them with different names? How does it know which names go with which parameters? Is it just because the order is the same?
EXEC sp_executesql N'EXEC R01OrderForm @Region, @District, @Dealer, @ProductSegment, @ReportPeriod, @LoginLevel',
N'@Region nvarchar(19),
@District nvarchar(163),
@Dealer nvarchar(max) ,
@ProductSegment nvarchar(57),
@ReportPeriod nvarchar(2),
@LoginLevel nvarchar(2)',
@Region = N'10, 30, 40, 50, -99',
@District = N'43, 2',
@Dealer = N'1',
@ProductSegment = N'3, 31',
@ReportPeriod = N'35', @LoginLevel = N'DM';
There is no @RegionID or @DistrictID in the parameter list here but that is what is in the query.
Thanks,
Tom
September 28, 2015 at 2:27 pm
Because in the string for sp_executesql, you're calling the sp as
EXEC R01OrderForm @Region, @District, @Dealer, @ProductSegment, @ReportPeriod, @LoginLevel
That code will assign the values to the parameters in the order they're defined. In this case, it would be equivalent to a call like this:
EXEC R01OrderForm
@RegionID=@Region,
@DistrictID=@District,
@DealerID=@Dealer,
@ProductSegmentID=@ProductSegment,
@ReportPeriodId=@ReportPeriod,
@LoginLevel=@LoginLevel
September 28, 2015 at 2:52 pm
So sp_executesql doesn't care what the parameters are - just that you have the same types in the same order as in the query.
So even if you use the same names (@RegionID for my example), it still must be in the same order unlike a normal EXEC where you can put the parameter names in any order but must match the names exactly?
Thanks,
Tom
September 29, 2015 at 4:24 am
tshad (9/28/2015)
in the same order as in the query.
Order is not important. I would recommend that you change the call to your SProc from:
EXEC R01OrderForm @Region, @District, ...
to
EXEC R01OrderForm @Region = @Region, @District = @District, ...
because that means that the sequence of your parameters does not have to match the sequence that is defined for the SProc. Particularly important if someone modifies the Sproc to add a new parameter definition, in the middle of all the existing ones!
You can do this:
EXEC sp_executesql N'EXEC R01OrderForm @Region = @Region, [highlight="#ffff11"]@District = @ParamDistrict[/highlight],
@Dealer = @Dealer, @ProductSegment = @ProductSegment,
@ReportPeriod = @ReportPeriod, @LoginLevel = @LoginLevel',
N'@Region nvarchar(19),
[highlight="#ffff11"]@ParamDistrict[/highlight] nvarchar(163),
@Dealer nvarchar(max) ,
@ProductSegment nvarchar(57),
@ReportPeriod nvarchar(2),
@LoginLevel nvarchar(2)',
@Region = N'10, 30, 40, 50, -99',
@Dealer = N'1',
@ProductSegment = N'3, 31',
@ReportPeriod = N'35', @LoginLevel = N'DM',
[highlight="#ffff11"]@ParamDistrict = @MyDistrictParameter[/highlight];
It is also OK to have parameter Definitions which are not actually used in the SQL String. This tends to make more sense for Adhoc Dynamic SQL where the WHERE clause varies:
strWHERE = "WHERE 1=1"
IF MyRegion <> "" THEN strWHERE = strWHERE & " AND Region = @MyRegion"
IF MyDistrict <> "" THEN strWHERE = strWHERE & " AND ParamDistrict = @MyDistrict"
...
so that only relevant columns are included in the WHERE clause, and any form field parameters which the user has left blank are excluded. The Parameter list still includes all the possible @parameters, and you provide values for all of them, just the actual SQL doesn't reference some of them. I find this makes it very easy to build such dynamic SQL because you can conditionally create the "meat" of the query and not have to bother to conditionally create the @parameter definitions
Are you using sp_ExecuteSQL to JUST Execute your R01OrderForm procedure? If so why not just execute it direct, rather than using dynamic SQL and sp_ExecuteSQL?
September 29, 2015 at 4:32 am
Kristen-173977 (9/29/2015)
tshad (9/28/2015)
in the same order as in the query.Order is not important. I would recommend that you change the call to your SProc from:
EXEC R01OrderForm @Region, @District, ...
to
EXEC R01OrderForm @Region = @Region, @District = @District, ...
because that means that the sequence of your parameters does not have to match the sequence that is defined for the SProc. Particularly important if someone modifies the Sproc to add a new parameter definition, in the middle of all the existing ones!
Absolutely right. The exception to that being natively compiled stored procedures in SQL Server 2014 and greater. There the ordinal calls are preferred. Actually makes me crazy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply