Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: SP and IN clause

    Thanks in advance,

    From a web page (UD4), I am trying to run a SP. I want to pass 3 variable to the SP. A beginning date, ending date and string to be used in an IN() statement. I can hard code the SP as below and it returns a value. But When I declare @DeptHdr and change the code to Dept IN (@DeptVar) and then try to pass the string 42500, 43500, it does not return any values.

    Is it possible to pass a variable this way into an IN statement?

    thanks,
    Lee

    @BegDate Smalldatetime,
    @EndDate Smalldatetime
    AS Select Dept, Sum((Grade7 + Grade8 + Grade9 + Grade10 + Grade3)* OutOfService) As TotHrs, Sum(TotalCost) AS TotCost
    From dbo.tblWorkOrder
    Where (Dept IN (42500, 43500)) AND CompletionDate Between @BegDate AND @EndDate
    Group By Dept
    GO

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    What is the data type of the field "Dept"? If it is a string you may use

    Dept IN ('42500', '43500')
    Last edited by gyuan; 01-10-04 at 17:49.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    This example is being used in the pubs database:

    CREATE PROCEDURE test @list varchar(400)
    AS
    exec ('select * from authors where zip in (' + @list + ')')


    exec test '94025,94618,94705'

Posting Permissions

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