Loading ...
Sorry, an error occurred while loading the content.
 

Re: [SQLQueriesNoCode] Efficiently find what's not in a list

Expand Messages
  • Paul Livengood
    There are lots of ways to do it Here is one.   DECLARE @tIDsToCheck TABLE(  EmployeeID   INT)   INSERT INTO @tIDsToCheck VALUES (1),(3), (7), (9)  
    Message 1 of 8 , Aug 9, 2011
      There are lots of ways to do it
      Here is one.
       
      DECLARE @tIDsToCheck TABLE(
       EmployeeID   INT)
       
      INSERT INTO @tIDsToCheck VALUES (1),(3), (7), (9)
       
      SELECT EmployeeID
      FROM @tIDsToCheck
      WHERE EmployeeID NOT IN (SELECT id FROM users)
      From: Dermot <paikkos@...>
      To: SQLQueriesNoCode@yahoogroups.com
      Sent: Tuesday, August 9, 2011 3:27 AM
      Subject: Re: [SQLQueriesNoCode] Efficiently find what's not in a list


       

      On 9 August 2011 10:22, Noman Aftab <noman17pk@...> wrote:
      >
      >
      >
      > Hi,
      > just put a NOT after WHERE
      >
      > SELECT id, name, FROM users WHERE id NOT IN (1, 3, 7, 9);
      >

      That would give you every other ID in the table that is not in the
      list. I'd like to know, which ID in that list is not valid. So if ID 7
      had been deleted from the table at some point, that would be the
      number returned.

      Thanks,
      Dermot



      [Non-text portions of this message have been removed]
    • Noman Aftab
      Hi, now this is driving me crazy. I have a SP which takes CurrencyCode as parameter. Code: CREATE PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3) AS --My
      Message 2 of 8 , Sep 11, 2011
        Hi,
        now this is driving me crazy.

        I have a SP which takes CurrencyCode as parameter.

        Code:

        CREATE PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3)
        AS
        --My SP logic

        Then if I execute the SP with the following code:

        exec sp_executesql 
        N'[sp_Get_Request_List]',
        N'@CurrencyCode char(3)',@CurrencyCode='USD'

        ...it gives the captioned error message.

        Procedure or function '' expects parameter '@CurrencyCode', which was not supplied.



        But  if I give a default value to NULL for CurrencyCode, as in:


        PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3) = NULL
        AS
        --My SP logic


        Then, the SP is executed but I always get the value as NULL instead of expected 'USD' value, which I am unable to use it in my where condition.

        Where exactly am I wrong?




        Best Wishes,
        Noman Aftab

         
        http://corpus.quran.com/wordbyword.jsp

        [Non-text portions of this message have been removed]
      • Noman Aftab
        Hi, Just to update you guys...it has this problem when the SP is executed with dynamic Sql. Sent from my iPhone ... [Non-text portions of this message have
        Message 3 of 8 , Sep 11, 2011
          Hi,
          Just to update you guys...it has this problem when the SP is executed with dynamic Sql.



          Sent from my iPhone

          On 11 Sep 2011, at 14:38, Noman Aftab <noman17pk@...> wrote:

          > Hi,
          > now this is driving me crazy.
          >
          > I have a SP which takes CurrencyCode as parameter.
          >
          > Code:
          >
          > CREATE PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3)
          > AS
          > --My SP logic
          >
          > Then if I execute the SP with the following code:
          >
          > exec sp_executesql
          > N'[sp_Get_Request_List]',
          > N'@CurrencyCode char(3)',@CurrencyCode='USD'
          >
          > ...it gives the captioned error message.
          >
          > Procedure or function '' expects parameter '@CurrencyCode', which was not supplied.
          >
          > But if I give a default value to NULL for CurrencyCode, as in:
          >
          > PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3) = NULL
          > AS
          > --My SP logic
          >
          > Then, the SP is executed but I always get the value as NULL instead of expected 'USD' value, which I am unable to use it in my where condition.
          >
          > Where exactly am I wrong?
          >
          > Best Wishes,
          > Noman Aftab
          >
          >
          > http://corpus.quran.com/wordbyword.jsp
          >
          > [Non-text portions of this message have been removed]
          >
          >


          [Non-text portions of this message have been removed]
        • Joe Fawcett
          I don t think you can specify @CurrencyCode in your statement as that s not a parameter name for sp_ExecuteSQL. (And you shouldn t specify some names and not
          Message 4 of 8 , Sep 12, 2011
            I don't think you can specify @CurrencyCode in your statement as that's not
            a parameter name for sp_ExecuteSQL. (And you shouldn't specify some names
            and not others):

            exec sp_executesql
            N'[sp_Get_Request_List]',
            N'@CurrencyCode char(3)','USD'

            Joe
            http://joe.fawcett.name/



            On 11 September 2011 11:38, Noman Aftab <noman17pk@...> wrote:

            > **
            >
            >
            > Hi,
            > now this is driving me crazy.
            >
            > I have a SP which takes CurrencyCode as parameter.
            >
            > Code:
            >
            > CREATE PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3)
            > AS
            > --My SP logic
            >
            > Then if I execute the SP with the following code:
            >
            > exec sp_executesql
            > N'[sp_Get_Request_List]',
            > N'@CurrencyCode char(3)',@CurrencyCode='USD'
            >
            > ...it gives the captioned error message.
            >
            > Procedure or function '' expects parameter '@CurrencyCode', which was not
            > supplied.
            >
            > But if I give a default value to NULL for CurrencyCode, as in:
            >
            > PROC [dbo].[sp_Get_Request_List]@CurrencyCode char(3) = NULL
            > AS
            > --My SP logic
            >
            > Then, the SP is executed but I always get the value as NULL instead of
            > expected 'USD' value, which I am unable to use it in my where condition.
            >
            > Where exactly am I wrong?
            >
            > Best Wishes,
            > Noman Aftab
            >
            >
            > http://corpus.quran.com/wordbyword.jsp
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >


            [Non-text portions of this message have been removed]
          Your message has been successfully submitted and would be delivered to recipients shortly.