August 27, 2003 at 8:04 am
This is a common task that seems to be very difficult to implement with a stored procedure. Does anyone know the best way to do the following?
I want to write a query that will select all the fields from a table with criteral of several items from a column. For instance if I have a sales rep table and join it with sales, I want my program to be able to select 3 or 4 sales reps and limit the query to that. I want it to be dynamic.
Select * From sales join salesreps on sales.repid = salesreps.repid
where salesrep.repid In (2, 8, 9, 12).
The sales repid's will be selected from the program.
The problem is the user should be able to select any abitrary number of sales reps they want on the report.
The only two ways that I know are to pass a comma seperated string into the stored procedure and use sp_executesql which is not the most efficient or to declare many extra parameters(one for each sales rep) and default them to ''. Then in the Where clause Or them all together. This is a very clumbsy way to do this.
Is there a better way than either of these two?
August 27, 2003 at 8:16 am
There are a couple of different ways to attack this... it's been a thread before. Maybe the easiest to explain is create a temp table, dump the values into the temp table, and join on it.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
August 27, 2003 at 9:11 am
Alright, my question then is, how do you get the data (several salesreps) into the temp table with a stored procedure?
These sales reps are going to be coming from a user who selects the from a program.
Edited by - ccundy on 08/27/2003 09:12:06 AM
August 27, 2003 at 9:21 am
The way we commonly use is a UDF function (provided you are on SQL2000) that will take in a list of values, in our case we pipe delimit them, and convert the output to a table
IE: pass in '12|45|78|56|23'
and have the function return
IDValue
------
12
45
78
56
23
(The function makes use of charindex and substring functions to cut the string up and insert values using a while loop one at a time into the function's return table)
Then in your select statement, you can just add this to the where clause, ie:
declare @SalesReps varchar(1000)
set @SalesReps = '2|8|9|12'
Select * From sales
inner join salesreps on sales.repid = salesreps.repid
where salesrep.repid in (select IDValue from dbo.funConvertPipelistToTable(@SalesReps))
August 27, 2003 at 10:39 am
That doesn't sound to bad except for the fact we are sitting on 7.0. Good idea though. Thanks for the input.
August 28, 2003 at 1:39 am
This is an example:
@strProjectnvarchar(255) = NULL
set nocount on
-- Declarations
...
-- Create Temporary Tables
create table #tblProject
(proj_projectid nvarchar(9) not null primary key)
if ((@strProject IS NULL) OR (@strProject = ''))
insert into #tblProject(proj_projectid)
select prj_code
from Project
else
begin
select @strProject = REPLACE(@strProject,',',''',''')
select @sql ='insert into #tblProject(proj_projectid) '+
' select prj_code from Project where prj_code IN (''' +
@strProject + ''')'
exec (@sql)
end
select distinct p_id, p_startdate
from Planning
where p_startdate <= @EndDate
and p_enddate >= @StartDate
and p_project IN (select proj_projectid from #tblProject)
order by p_startdate
August 28, 2003 at 2:51 am
Try this
Declare @Repids varchar(50)
Set @Repids = '2,8,9,12'
Set @Repids = ',' + @Repids + ','
Select *
From sales
join salesreps
on sales.repid = salesreps.repid
where CharIndex( ',' + cast( salesrep.repid as varchar(10)) + ',' , @Repids) > 0
August 28, 2003 at 6:48 am
These are all good ideas, Thanks for the feedback.
August 28, 2003 at 11:10 am
ccundy,
Here is a link you may find of interest:
http://www.algonet.se/~sommar/arrays-in-sql.html
He has a couple of functions that convert lists into tables.
--Lenard
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply