Create Procedure paramaters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Create Procedure paramaters

Create Procedure paramaters

Rate Topic

Display Mode

Topic Options

Author

Message

mark-1113653

Posted Saturday, June 13, 2009 10:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 5:22 AM
Points: 2, Visits: 7

Good morning everyone.

I am attempting to complete some basic SQL training and I continue to receive the following error when attempting to create a procedure with a variable.

"Msg 207, Level 16, State 1, Procedure GET_CD_ARTISTS, Line 8 Invalid column name 'p_CD'

The code I am using is below this line:

CREATE PROCEDURE GET_CD_ARTISTS (@p_CD VARCHAR(60))
AS
SELECT cd.CD_TITLE, a.ARTIST_NAME
FROM COMPACT_DISCS cd, ARTIST_CDS ac, ARTISTS a
WHERE cd.COMPACT_DISC_ID = ac.COMPACT_DISC_ID
AND ac.ARTIST_ID = a.ARTIST_ID
AND cd.CD_TITLE = p_CD;

I just can't figure out where the variable is causing an invalid column error.

Thank you in advance for the help.

Post #734366

ALZDBA

Posted Saturday, June 13, 2009 10:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 6,861, Visits: 8,048

mark (6/13/2009)

We all often forget the @ when typing a proc in a hurry.
Enclose your sproc code in a BEGIN / END sequence.
This is meerly for completeness check of your proc at implement time.

And use the "set nocount on " to avoid unneeded network trafic

btw also start using the JOIN syntax for joining objects.

CREATE PROCEDURE GET_CD_ARTISTS (@p_CD VARCHAR(60))
AS
BEGIN
SET NOCOUNT ON ;
SELECT cd.CD_TITLE, a.ARTIST_NAME
FROM COMPACT_DISCS cd
INNER JOIN ARTIST_CDS ac
on ac.COMPACT_DISC_ID = cd.COMPACT_DISC_ID
INNER JOIN ARTISTS a
on a.ARTIST_ID = ac.ARTIST_ID

Where cd.CD_TITLE = @p_CD;

END

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #734369

mark-1113653

Posted Saturday, June 13, 2009 10:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 5:22 AM
Points: 2, Visits: 7

LOL Thank you. I can hardly believe it is a simple as an incorrect name reference. I thought the @ symbol was only a part of the designation and not the name.

Post #734371

« Prev Topic | Next Topic »

Permissions