how to do a Common Task with stored procedure

  • 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?

  • 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

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • 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

  • 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))

  • That doesn't sound to bad except for the fact we are sitting on 7.0. Good idea though. Thanks for the input.

  • 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

  • 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

  • These are all good ideas, Thanks for the feedback.

  • 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