SQL Server 2008 Passing Multi-value Parameters or Parameter Array to a Stored Procedure -

SQL Server 2008 Passing Multi-value Parameters or Parameter Array to a Stored Procedure -

i have 2 tables tbl_properties , tbl_locations

tbl_properties has list of properties (including fk location_id) tbl_location has list of locations

i using next stored procedure search properties in location:

sppropgetsearch ( @location_id int ) select p.prop_id, p.prop_title, p.prop_bedrooms, p.prop_price, l.location_title tbl_properties p inner bring together tbl_locations l on l.location_id = p.location_id (p.location_id = @location_id or @location_id = '0') order p.prop_dateadded desc

i pass location_id (such '1002') , works fine , returns properties located within location / area. now, want pass multiple location_ids such '1002', '1005', '1010' search properties located in of areas.

how that? appreciate detailed reply not database expert.

i found next illustration , it's working fine. please can , check if there's vulnerability

use adventureworks2012 go create procedure usp_employeelist @cities nvarchar(30) declare @citiesxml xml set @citiesxml = cast(('<a>'+replace(@cities,',' ,'</a><a>')+'</a>') xml) select businessentityid , firstname , jobtitle , city humanresources.vemployee city in ( select a.value('.', 'varchar(max)') @citiesxml.nodes('a') fn(a) ) order businessentityid go --execute stored procedure using multiple values --through 1 parameter in stored procedure utilize adventureworks2012 go exec usp_employeelist 'cambridge,newport hills,berlin,bordeaux' go

use table-valued parameters. first step create type:

create type dbo.listofint table (value int);

i tend utilize generic names these allow reuse without confustion names (e.g. if named locationids become confusing store list of properties in type).

then can reference type in stored procedure:

create procedure dbo.sppropgetsearch @locationids dbo.listofint readonly begin select p.prop_id, p.prop_title, p.prop_bedrooms, p.prop_price, l.location_title tbl_properties p inner bring together tbl_locations l on l.location_id = p.location_id p.location_id in (select value @locationids) or @location_id = 0 order p.prop_dateadded desc; end

you can phone call using like:

declare @locationids dbo.listofint; insert @locationids (value) values (1002), (1005), (1010); execute dbo.sppropgetsearch @locationids;

edit

found error, here:

or @location_id = 0

which leads me on new point, looks want have alternative homecoming if 0 passed. using if/else:

create procedure dbo.sppropgetsearch @locationids dbo.listofint readonly begin if exists (select 1 @locationids) begin select p.prop_id, p.prop_title, p.prop_bedrooms, p.prop_price, l.location_title tbl_properties p inner bring together tbl_locations l on l.location_id = p.location_id p.location_id in (select value @locationids) order p.prop_dateadded desc; end else begin select p.prop_id, p.prop_title, p.prop_bedrooms, p.prop_price, l.location_title tbl_properties p inner bring together tbl_locations l on l.location_id = p.location_id order p.prop_dateadded desc; end end go

so if table valued parameter passed empty, homecoming records, if contains records contain location_ids supplied. putting or in queries makes impossible sql server utilize appropriate index.

addendum

to reply comment

instead of using: if exists (select 1 @locationids) how can utilize or status in clause

my reply don't suggested using if/else reason, not on complicate things, improve performance. had hoped deter approach when said "putting or in queries makes impossible sql server utilize appropriate index.".

you rewrite query follows:

select p.prop_id, p.prop_title, p.prop_bedrooms, p.prop_price, l.location_title tbl_properties p inner bring together tbl_locations l on l.location_id = p.location_id p.location_id in (select value @locationids) or not exists (select 1 @locationids) order p.prop_dateadded desc;

the problem approach is, have 2 options in same query, , these options need 2 different exection plans. if have index on p.location_id, , have records in @locationids, best query plan utilize index seek on tbl_properties.location_id. if @locationids empty, index seek pointless , best plan clustered index scan (table scan) on tbl_properties. since sql server uses cached plans, can cache on or other, means if stores index seek option, every time pass empty table have sub-optimal plan, or alternatively, if caches table scan plan, every time pass values location id not taking advantage of index there.

one workaround option (recompile):

select p.prop_id, p.prop_title, p.prop_bedrooms, p.prop_price, l.location_title tbl_properties p inner bring together tbl_locations l on l.location_id = p.location_id p.location_id in (select value @locationids) or not exists (select 1 @locationids) order p.prop_dateadded desc alternative (recompile);

this forces query recompiled ever execution ensure have optimal plan current execution. since have 2 options, lot of unnessecary recompilation. best alternative have 2 queries, each it's own cached execution plan, , using if/else flow operator flow appropriate query based on has been passed @locationids.

sql arrays sql-server-2008 stored-procedures parameters