how to “store” stored procedures

[BobLewiston]

I've written a few SQL stored procedures in a text editor. But how do I actually "store" them in (add them to?) a database using SQL Server 2008 Management Studio Express? I've tried to research this topic in Management Studio's onboard Help, but apparently Help assumes I know more about the subject than I actually do, because I don't even see the relevance of the answers I'm getting to my questions. Any help would be appreciated, including pointing me to a good SQL tutorial that assumes the reader knows nothing, yet teaches more than the bare-bones minimum.

[Uncle Dickie]

I've not used 2008 but it should be something like:

USE [database name]

CREATE PROCEDURE [schema].[procedure name]
AS
BEGIN
        your code here
END

Once created, if you need to modify it use ALTER PROCEDURE

[ck9663]

Open a query window and paste your stored procedure there then click RUN.


--- CK

[BobLewiston]

ck9663:

I clicked New Query and pasted my procedure, but before I could even click RUN, SQL Server Management Studio made numerous objections.

Here's my procedure (sorry, I didn't code-wrap it so the bold font would show):

CREATE PROCEDURE UpdateProc (
@ContactID as int,
@Title as nvarchar (8),
@FirstName as Name:nvarchar (50),
@MiddleName as Name:nvarchar (50)
@LastName as Name:nvarchar (50)
@Suffix as nvarchar (10)
@EmailAddress as nvarchar (50)
@Phone as Phone:nvarchar (25)
@PasswordHash as varchar (128)
@PasswordSalt as varchar (10))
AS
UPDATE Person.Contact SET
Title = @Title, FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, Suffix = @Suffix,
EmailAddress = @EmailAddress, Phone = @Phone, PasswordHash = @PasswordHash,
PasswordSalt = @PasswordSalt
WHERE ContactID = @ContactID
GO

Here are Management Studio's objections:

Parameters: words shown in bold. All are "incorrect syntax". Data types are given exactly as they appear in the table "Person.Contact ".

Body of procedure (every word except UPDATE, SET and WHERE):
Person.Contact: "invalid object name"
all column names: "invalid column name"
all scalar variables (with "@"): "must declare scalar variable"

It must be that I need to associate this proceure with the table. But how? I tried highlighting the table, but that didn't work.

BTW, I thought that, technically speaking, only SELECT constituted a query, and UPDATE, INSERT and DELETE were just called commands.

As you can see, I'm a newbie stumbling around in the dark, so ANY help would be most appreciated.

P.S. Just tried it again, the exact same way. This time it dropped its objections to column names and to object name "Person.Contact ". I have no idea why.

[ck9663]

Here you go...

CREATE PROCEDURE UpdateProc 
(
   @ContactID as int,
   @Title as nvarchar (8),
   @FirstName as nvarchar (50),
   @MiddleName as nvarchar (50),
   @LastName as nvarchar (50),
   @Suffix as nvarchar (10),
   @EmailAddress as nvarchar (50),
   @Phone as nvarchar (25),
   @PasswordHash as varchar (128),
   @PasswordSalt as varchar (10)
)
AS
UPDATE Person.Contact 
SET
   Title = @Title, FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, Suffix = @Suffix,
   EmailAddress = @EmailAddress, Phone = @Phone, PasswordHash = @PasswordHash,
   PasswordSalt = @PasswordSalt
WHERE ContactID = @ContactID
GO

--- CK

[BobLewiston]

Thanks. I didn't notice I was missing some commas, and those Name and Phone references didn't help.