Take the 2-minute tour ×

I am learning how to write a stored procedure. I kinda get it but I don't. I know it goes something like this..

[<parameter name> [schema.]<data type> [VARYING] [=<default value>] [OUT[PUT]]
[,<parameter name> [schema.]<data type> [VARYING] [=<deafult value>] [OUT[PUT]]
 <code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

So what I am stuck on and I am trying to understand is this.. Write a stored procedure that accepts a Territory ID, Territory Description, and Region ID and inserts them as new row in the Territories table in Northwind.

Ok so I know I could do something like this I believe:

USE Northwind
CREATE PRO spTerritory
SELECT Territory ID,Territory Description,RegionID
From dbo.Territories

But then I can be wrong. But I dont know where to insert then as a new row in the table. I know I would use something like @newrow or @rows along that line. If someone can help me understand who to do a sproc I would be greatful.

share|improve this question
Never name a stored proc starting with sp, that is used by Microsoft for system storedprocs and you will lose execution time every time it runs because it has to check for a system proc first. –  HLGEM Nov 10 '11 at 15:36
@HLGEM -- It's sp_ that's the problem prefix. Using simply sp is fine and won't cause the same problems, although you might want to avoid it as a standard convention just in case an sp_ slips in. –  Chris J Nov 10 '11 at 15:39
@ChrisJ, you are correct –  HLGEM Nov 10 '11 at 15:41
How about, don't start the name with "sp" because it's just silly ;) –  Tom H. Nov 10 '11 at 16:04
I am sorry about the sp name the reason I use it because that is how it is stated in my book –  norris1023 Nov 10 '11 at 16:11

2 Answers 2

up vote 8 down vote accepted

You are looking for the INSERT statement.

CREATE PROCEDURE InsertTerritory (
     @territoryId int
    ,@territoryDescription nvarchar(200)
    ,@regionId int)

    INSERT INTO Territories (Id, [Description], RegionId)
    VALUES (@territoryId, @territoryDescription, @regionId)

share|improve this answer

Your Answer


