Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Red face Unanswered: Stored Procedure Problem

    I want to check if a DB field matches a list of values such as

    select * from tblX where fldX in (1,2,3,4)

    but in a stored proc ie

    select * from tblX where fldX in (@var)

    but often there will be no value passed into @var ie any value at all

    I am going in circles. What should the stored proc be and what variables?

    the actual query is a lot bigger and selecting on this field is a small part of what it does. essentially I want to miss out "where fldX in (@var)" if the value passed to @var is null

    Cheers

  2. #2
    Join Date
    Aug 2003
    Posts
    7

    Re: Stored Procedure Problem

    Maybe try something like this:

    declare @sql varchar(4000)

    set @sql = 'select * from tblX'
    if @var <> null
    begin
    set @sql = @sql + ' where fldX in (@var)'
    end

    exec sp_executesql @sql

    You have to pass in the @var parameter, and it's a little more work, but that should work. I'm sure there are other ways to do it that are better, though.

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    Thumbs up

    Thanks for that reply.

    I went with your suggestion. I was worried that it would recompile the SP all the time and slow down (not that I really know anything about SPs and compilation), but it seems to be still very quick and does the job fine

    Cheers

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What type of variable is @Var? Is it a string representation like '1, 2, 3' or is it a single integer value, or a table variable? I've gotten away with sql code like this before without using dynamic sql but running statements like:

    select * from tblX where (fldX in (@var)) or (fldX is null)

    ...but whether this works depends on what @Var is. The IN statement is not all that versatile about accepting parameters.

    blindman

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I would drop IN and dynamic SQL like a rock. There is a much more elegant way of doing it, and it works no matter whether @var has values or not.

    - Create a temp table with one field of the required data type
    - Parse your @var and store all its values into this temp table
    - Either JOIN with this table blindly (NPI), or JOIN only if temp table contains at least one row.

  6. #6
    Join Date
    Aug 2003
    Posts
    3

    temp tables

    I am using a db of several tens of thousands of people submitting these searches every few seconds. Surely there would be some impact on constantly inserting and deleting out of temporary tables.

    @var in the original post was a varchar such as '1,2,4,7'. There are in actual fact 10 different @var's being passed, hence I either had to read in all the values, which meant reading select x_key from 10 tables and concatonating with a comma (slow) or hard coding (no).

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There is no need to delete. Besides, if you use @table instead of #table, you woiuldn't even generate an IO (unless we are talking about 10,000 values which may cause paging). Recently I rewrote a search routine used here by 40K internal and over 3 million external users using the concept that I described in my previous posting. The search is performed against 40K+ records in 7 tables. Max delay is under a second. I am not so sure about your "Surely"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •