I need help for writing a Stored Procedures in SQL Server 2005

I’m a newbie in Stored Procedures in SQL Server 2005. Let’s say we have a table with these columns: Id (GUID), UserId (string), DayOfYear (int), Score (int).

I need a SP with one input (inputDayOfYear) which copy last day (inputDayOfYear=10 => last day DayOfYear=9) data with new Id but old UserId and Score, and current DayOfYear (th input parameter) to new rows.

• One important thing is to ignore 开发者_运维技巧those records that have data of DayOfYear same as inputDayOfYear.
• Performance is also very important here; there are may be over 1000 rows for each DayOfYear.
• Return value is not necessary but number of new rows would be fine.

I’ll be appreciated if you introduce me to some good resources to learn SP, too.

If i understand your request correctly this should help you

    DECLARE @Table TABLE(
        ID UNIQUEIDENTIFIER,
        UserID INT,
        [DayOfYear] INT,
        Score INT
)

INSERT INTO @Table (ID,UserID,DayOfYear,Score) SELECT NEWID(), 1, 1, 10
INSERT INTO @Table (ID,UserID,DayOfYear,Score) SELECT NEWID(), 2, 1, 100
INSERT INTO @Table (ID,UserID,DayOfYear,Score) SELECT NEWID(), 2, 2, 1000
INSERT INTO @Table (ID,UserID,DayOfYear,Score) SELECT NEWID(), 1, 3, 10

DECLARE @inputDayOfYear INT
SELECT @inputDayOfYear = 3

/* THIS GOES INTO SP*/
INSERT INTO @Table (ID,UserID,DayOfYear,Score)
SELECT  NEWID(),
        UserID,
        @inputDayOfYear,
        Score 
FROM    @Table
WHERE   [DayOfYear] = @inputDayOfYear - 1
AND     UserID NOT IN (SELECT UserID FROM @Table WHERE [DayOfYear] = @inputDayOfYear)
/* THIS GOES INTO SP*/

SELECT * FROM @Table

/* SP TO CREATE */
CREATE PROCEDURE InsertValues(@inputDayOfYear AS INT)
AS
BEGIN
    INSERT INTO @Table (ID,UserID,DayOfYear,Score)
    SELECT  NEWID(),
            UserID,
            @inputDayOfYear,
            Score 
    FROM    @Table
    WHERE   [DayOfYear] = @inputDayOfYear - 1
AND     UserID NOT IN (SELECT UserID FROM @Table WHERE [DayOfYear] = @inputDayOfYear)
END
/* SP TO CREATE */

This MSDN page has been the most useful resource I have found for learning and exploring T-SQL and stored procedures.

MSDN T-SQL Reference page