Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Unanswered: STORED PROC WITH VARIABLE MULTIPLE OR <>

    I need to create a SQL Server Stored Proc that will handle a variable number of Or conditions. This is currently being done with a MS Access Query as follows

    Do Until rst.EOF

    myw = myw = "(rst!Field1 <> 0) OR (rst!Field1 <> 1) "

    Loop

    mysql = "UPDATE Table SET Field2 = 1 WHERE " & myw

    The above code is very simplified.

    I Want to create a stored proc to do this but I cannot send it the SQL to the Stored Proc (or can I) so I need to use parameters instead. I want to do something like

    Do until rst.EOF

    Set cmd = MakeStoredProc("sp_Table_UpdateField2_ForField1")
    Set prmField1 = cmd.CreateParameter("Field1", adInteger, adParamInput, , rst!Field2)
    cmd.Parameters.Append Field1

    cmd.Execute

    Loop

    Again the above is very simplified. So how can you get the the SQL for the Stored Proc for something like the following from a loop

    WHERE = (Field1 <> 0) OR (Field1 <> 1) OR (Field1 <> 2) ...

    Thanks in advance for your help
    JayD
    Boulder, CO, USA

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you rummage around a bit using the forum search feature, you'll find a table valued function I wrote called fSplit() (or I can always dig it up and repost it). It takes a delimted list (like "1, 2, 3, 4, 5") and turns it into a table.

    You could pass the parameters in as a VARCHAR parameter, then use dbo.fSplit and the NOT IN syntax to exclude those values from the row(s) you returned.

    Otherwise, you'll have to concoct some way to do this using dynamic SQL which can be done, but it is a lot more work.

    -PatP

Posting Permissions

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