#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0

    Grab first X number of Records (SPROC)


    Hi All,

    I am totally new to MSSQL and stored procedures. I am trying to create a stored procedure that takes that is sent an int that represent a number of records to be returned.. not sure how to do this but this is what i've got:

    ALTER PROCEDURE GetNewUserJob

    @campaign_id int,
    @amount int

    AS
    /* SET NOCOUNT ON */
    SELECT TOP @amount campaign_to_email.email_id, email_addresses.email_address
    FROM campaign_to_email INNER JOIN email_addresses ON
    campaign_to_email.email_id = email_addresses.email_id
    WHERE (campaign_to_email.campaign_id = @campaign_id)
    AND campaign_to_email.in_job IS NULL
    AND campaign_to_email.is_sent IS NULL
    RETURN

    Any help is appreciated! Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,376
    Rep Power
    391
    Code:
    ALTER PROCEDURE GetNewUserJob
    
    @campaign_id int,
    @amount int
    
    AS
    /* SET NOCOUNT ON */
    set rowcount @amount
    SELECT campaign_to_email.email_id, email_addresses.email_address
    FROM campaign_to_email INNER JOIN email_addresses ON
    campaign_to_email.email_id = email_addresses.email_id
    WHERE (campaign_to_email.campaign_id = @campaign_id)
    AND campaign_to_email.in_job IS NULL
    AND campaign_to_email.is_sent IS NULL
    RETURN
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    10
    Rep Power
    0

    nice!


    Thanks for the help!!

    rw
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    NY
    Posts
    18
    Rep Power
    0
    You can also use the TOP keyword in the select statement:
    SELECT TOP 2 * FROM dbo.Orders

    -- Messorian

IMN logo majestic logo threadwatch logo seochat tools logo