By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

how to “store” stored procedures

P: 93
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.
Apr 2 '09 #1
Share this Question
Share on Google+
5 Replies


Uncle Dickie
P: 67
I've not used 2008 but it should be something like:

Expand|Select|Wrap|Line Numbers
  1. USE [database name]
  2.  
  3. CREATE PROCEDURE [schema].[procedure name]
  4. AS
  5. BEGIN
  6.         your code here
  7. END
  8.  

Once created, if you need to modify it use ALTER PROCEDURE
Apr 2 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Open a query window and paste your stored procedure there then click RUN.


--- CK
Apr 2 '09 #3

P: 93
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.
Apr 2 '09 #4

ck9663
Expert 2.5K+
P: 2,878
Here you go...

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE PROCEDURE UpdateProc 
  3. (
  4.    @ContactID as int,
  5.    @Title as nvarchar (8),
  6.    @FirstName as nvarchar (50),
  7.    @MiddleName as nvarchar (50),
  8.    @LastName as nvarchar (50),
  9.    @Suffix as nvarchar (10),
  10.    @EmailAddress as nvarchar (50),
  11.    @Phone as nvarchar (25),
  12.    @PasswordHash as varchar (128),
  13.    @PasswordSalt as varchar (10)
  14. )
  15. AS
  16. UPDATE Person.Contact 
  17. SET
  18.    Title = @Title, FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, Suffix = @Suffix,
  19.    EmailAddress = @EmailAddress, Phone = @Phone, PasswordHash = @PasswordHash,
  20.    PasswordSalt = @PasswordSalt
  21. WHERE ContactID = @ContactID
  22. GO
  23.  
  24.  

--- CK
Apr 2 '09 #5

P: 93
Thanks. I didn't notice I was missing some commas, and those Name and Phone references didn't help.
Apr 2 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.