Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Procedure paramaters Expand / Collapse
Author
Message
Posted Saturday, June 13, 2009 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 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
Posted Saturday, June 13, 2009 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
mark (6/13/2009)
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.


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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #734369
Posted Saturday, June 13, 2009 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 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 »

Add to briefcase

Permissions Expand / Collapse