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

Question

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.

Answer 1

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

Answer 2

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 */