Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Red face Unanswered: Weird SP problem

    Hello,

    I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.

    I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP).
    SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP
    but it refer it as a string and not a code so I can't run this sub-query in the SP.

    Example:
    Code:
    CREATE PROCEDURE usp_test
    
    @subSQL varchar(200)
    
    AS
    
    SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
    
    GO
    
    ----------------
    
    exec usp_test 'SELECT TOP 15 id FROM tbl2'
    What's wrong here? Am I working in a wrong method?

    Thanks for the helpers,
    BuildHome

  2. #2
    Join Date
    Feb 2006
    Posts
    9
    The problem has been resolved by using Dynamic SQL

    P.S:
    You can delete this topic...

  3. #3
    Join Date
    Jun 2004
    Posts
    50
    Please post the solution.
    Monk
    The person who confesses ignorance shows it once; the person who conceals it shows it many times.

  4. #4
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Code:
    CREATE PROCEDURE usp_test
    @subSQL varchar(200)
    AS
    BEGIN
        declare @sql varchar(1000)
        select @sql = 'SELECT id FROM tbl WHERE id IN (' + @subSQL + ') ORDER BY id DESC'
        exec (@sql)
    END
    -----------------
    KH


Posting Permissions

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