Writing my first Stored Procedure

Question

Post reply

Writing my first Stored Procedure

Sasha H

Valued Member

Points: 58
More actions
September 19, 2005 at 10:19 am

#168742

Hi all,

I've just joined this forum and hope to learn a lot as time goes on...
I am currently trying to write a basic sproc, which enables a user to use parameters (firstname and lastname) to obtain an address.

TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

You need to add an "AS". I would also suggest using the following

IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[sp_myStoredProcedure]')

AND OBJECTPROPERTY( id, N'sp_myStoredProcedure') = 1)

DROP PROCEDURE [dbo].[sp_myStoredProcedure]

GO

CREATE PROCEDURE sp_myStoredProcedure

@PERSON_ID varchar(5),

@PERSON_SUR_NM int, -- not sure why this would be an integer...

@PERSON_FIRST_NM int, -- not sure why this would be an integer...

AS

GO

The upper portion will drop the existing procedure for when you make changes.

I would also suggest not naming it with an "sp_". That is no longer necessary and good naming conventions are very important...

I wasn't born stupid - I had to study.

Otto Schreibke SSC Eights! Points: 943 More actions · Answer 2

Good reason not to use SP_:

If you do SQL/Server will look for the sproc in Master, and then your user database if it doesn't find it even if you qualify the name, for example userdb..sp_myproc.

If you happen to use the same name as one of the MS stored procs in master, that is the sp which will be executed.

Best Regards,

Otto Schreibke

The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)

Sasha H Valued Member Points: 58 More actions · Answer 3

Thanks for the reply!

The stored procedure should actually return the following items:

Address_ID

Person_ID

Address_Text

Would this still be possible with the above code.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Basically two ways:

CREATE PROCEDURE sp_myStoredProcedure

@PERSON_ID varchar(5) OUTPUT,

@PERSON_SUR_NM integer,

@PERSON_FIRST_NM integer,

@ADDRESS_ID integer OUTPUT,

@ADDRESS_TEXT varchar(2000) OUTPUT

AS

GO

-- OR --

CREATE PROCEDURE sp_myStoredProcedure

@PERSON_ID varchar(5),

@PERSON_SUR_NM integer,

@PERSON_FIRST_NM integer

AS

SELECT AddressID, AddressText FROM Address WHERE PersonID = @PERSON_ID

GO

I wasn't born stupid - I had to study.