Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40

    Unanswered: using "SELECT TOP" in stored procedure

    How could I use SELECT TOP in stored procedure
    For ex I want convert this line to Stored procedure

    "Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"

    I dont want use ROWCOUNT
    because its not working good it will not show some data of some fields
    for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject

    this is my current code

    Code:
    CREATE PROCEDURE [dbo].[SP_Articles] 
    (
    @Articles int
    )
    AS 
    SET ROWCOUNT @Articles
    SELECT tblArticles.*
    FROM tblArticles
    WHERE published = 1 ORDER BY Adate DESC;
    SET ROWCOUNT 0
    GO
    and I use this code to call that from ASP

    Code:
    strSQL = "EXECUTE SP_Articles @Articles = " & intArticles
    
    rsArt.CursorType = 1
    rsArt.Open strSQL, strCon
    so just tell me how to use TOP in stored procedure
    Thanks

  2. #2
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    select top 1 * from table

  3. #3
    Join Date
    Oct 2003
    Posts
    357
    Do mean Top @Articles?
    It is not possible until you use Dynamic SQL
    But use Set Rowcount as you used
    Madhivanan

    Failing to plan is Planning to fail

  4. #4
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Thanks
    Please tell me how to use Dynamic SQL
    Please do it for me and convert it,

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Please do it for me?"

    Please pay me to do it for you.

    Or look up "exec" and "sp_executesql" in Books Online, which will show you how to execute a dynamic SQL statement. Heck, they may even use TOP @N as an example.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Hey man
    I'm not talking to you
    OK?
    OK?
    Here is a Forum , we are here to help each other
    So be quiet

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, you shouldn't of oughtn't of done that...

    "we are here to help each other "???

    So exactly HOW are YOU helping ME? Or anybody?

    Hmmm....

    How many posts have YOU answered for people, hmmmmm...let me guess...probably some value between zero and negative one, I'm thinking.

    Let's see...farshad (click)...Find All Posts (click)...hmmm...

    Yes, all your posts seem to start with words such as "How do I", "I want", and "Please help me". So heck, why not come right out and say "do my work for me", then, eh?

    What do I win?

    This forum exists to help PROFESSIONALS (DBA and non-DBA) who are trying to using SQL Server for serious work. We provide answers for free, and we take a dim view of two types of people:
    1) Students taking up our time to anwer their homework questions
    2) People to lazy to look anything up or learn anything on their own.

    Let me know when you top 1000 posts, and then you can lecture me about what the forum exists for.

    What's really sad is that you are such a noob you don't even realize that I gave you an answer to your question.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    What is this ?
    hah?
    Hey guys look at this :

    Quote Originally Posted by blindman
    Please pay me to do it for you.
    You told me here is a place for PROFESSIONALS
    So just tell me, what are you doing here?


    I have got my answer :

    Code:
    @Articles INT 
    AS 
    BEGIN 
        DECLARE @sql VARCHAR(1000) 
        SET @sql = 'SELECT TOP ' + CONVERT(VARCHAR, @Articles) +' *' 
            + ' FROM tblArticles 
            + ' WHERE published = 1'
            + ' ORDER BY date DESC' 
        EXEC(@sql) 
    END
    GO

    I'm sorry for you
    Last edited by farshad; 10-16-05 at 18:59.

  9. #9
    Join Date
    Oct 2003
    Posts
    357
    >>Please tell me how to use Dynamic SQL

    Refer this
    http://www.sommarskog.se/dynamic_sql.html
    Madhivanan

    Failing to plan is Planning to fail

  10. #10
    Join Date
    May 2004
    Location
    Dubai
    Posts
    40
    Quote Originally Posted by Madhivanan
    Thanks a lot

Posting Permissions

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