Professional Web Applications Themes

SELECT TOP sub query Stored PROC - Microsoft SQL / MS SQL Server

Why do I get invalid sytax, can you not use variables with in the TOP Clause. Is there a work around. ALTER PROC sp_depositoriesretreive include int, exclude int AS SELECT TOP include depositoryID, FROM depositories WHERE depositoryID NOT IN (SELECT TOP exclude depositoryID FROM depositories ORDER BY depositoryname) ORDER BY depositoryname GO Thanks...

  1. #1

    Default SELECT TOP sub query Stored PROC

    Why do I get invalid sytax, can you not use variables with in the TOP
    Clause. Is there a work around.

    ALTER PROC sp_depositoriesretreive
    include int,
    exclude int
    AS
    SELECT TOP include
    depositoryID,
    FROM depositories
    WHERE depositoryID NOT IN
    (SELECT TOP exclude depositoryID FROM depositories ORDER BY
    depositoryname)
    ORDER BY depositoryname
    GO

    Thanks


    IntraRELY Guest

  2. #2

    Default Re: SELECT TOP sub query Stored PROC

    use dynamic sql to achieve this.

    Ex:

    ALTER PROC sp_depositoriesretreive
    include int,
    exclude int
    AS
    exec (' SELECT TOP '+ include+ ' depositoryID, FROM depositories WHERE
    depositoryID NOT IN (SELECT TOP ' + exclude + 'depositoryID FROM
    depositories ORDER BY depositoryname) ORDER BY depositoryname')

    GO


    "IntraRELY" <com> wrote in message
    news:phx.gbl... 


    Vishal Guest

  3. #3

    Default SELECT TOP sub query Stored PROC


    Please refer

    http://www.algonet.se/~sommar/dynamic_sql.html#Top

    HTH
    Srinivas Sampangi 
    with in the TOP 
    ORDER BY 
    sampangi Guest

  4. #4

    Default Re: SELECT TOP sub query Stored PROC

    Perfect, Vishal you rule. From what I saw all you added was
    [contactfirstname1 + '' '' + contactlastname1].

    Why did that work ('' ''), I dont understand?

    Thanks Again,

    Steve Wofford
    www.IntraRELY.com

    "Vishal Parkar" <com> wrote in message
    news:%phx.gbl... [/ref]
    version, [/ref]
    > WHERE [/ref][/ref]
    TOP 
    > >
    > >[/ref]
    >
    >[/ref]


    IntraRELY Guest

  5. #5

    Default Re: SELECT TOP sub query Stored PROC

    The statement that you have used will create syntax like

    contactfirstname1 contactlastname1 AS "contact",

    which is invalid whereas, my Dynamic SQL will generate a syntax as follows.

    contactfirstname1 + ' ' + contactlastname1 AS "contact"

    -Vishal


    "IntraRELY" <com> wrote in message
    news:#zLEg$phx.gbl... [/ref][/ref]
    work. [/ref]
    > version, [/ref][/ref]
    getting [/ref][/ref]
    BY 
    > > WHERE [/ref]
    > TOP 
    > >
    > >[/ref]
    >
    >[/ref]


    Vishal Guest

  6. #6

    Default Re: SELECT TOP sub query Stored PROC

    Thanks again Vishal your prompt response is well appreciated.

    Steve

    "Vishal Parkar" <com> wrote in message
    news:%phx.gbl... 
    follows. [/ref][/ref]
    BY [/ref]
    > work. 
    > > version, [/ref]
    > getting [/ref]
    > BY [/ref][/ref]
    depositories [/ref][/ref]
    the 
    > >
    > >[/ref]
    >
    >[/ref]


    IntraRELY Guest

Similar Threads

  1. Question Double IIF to a SQL query, stored proc, or view
    By Unregistered in forum Brainstorming Area
    Replies: 0
    Last Post: July 23rd, 08:36 PM
  2. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  3. Simple stored proc in query question
    By Bob Castleman in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:20 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139