June 13, 2009 at 10:39 am
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.
June 13, 2009 at 10:47 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
June 13, 2009 at 10:53 am
:w00t:
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply