March 14, 2012 at 9:59 am
Hello all,
Thank you to those who helped with my last issue.
Unfortunately, there is now another problem when I try to create a procedure with parameters.
CREATE PROCEDURE GET_CD_ARTISTS ( IN p_CD VARCHAR(60) )
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;
The code gives me error
Msg 156, Level 15, State 1, Procedure GET_CD_ARTISTS, Line 1
Incorrect syntax near the keyword 'IN'.
Any reasons why this error happens?
Thanks.
March 14, 2012 at 10:02 am
I am assuming that the IN is to indicate that the parameter that follows is an input parameter, drop it.
Also, may I suggest that you look up CREATE PROCEDURE in Books Online? It will provide you with the syntax of the command.
March 14, 2012 at 10:04 am
You have a few syntax issues there. Not sure where you came up with "IN". I assume you are trying to indicate an inbound parameter? Also your parameter is a variable not an object name so you need to preface it with an ampersand "@".
Here is your proc without the syntax errors.
CREATE PROCEDURE GET_CD_ARTISTS
(
@p_CD VARCHAR(60)
)
as begin
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;
end
I added the begin end block because I always add those to every proc. It keeps it all wrapped up nice and tidy and avoids accidental extension to a proc with additional select statements or something.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2012 at 10:10 am
Wow!Thank you guys again!
It appears my training book is outdated.
Looks like they made some significant syntax changes from SQL 2006 to SQL 2008.
I will have to find a tutorial for procedures somewhere else then.
March 14, 2012 at 10:15 am
thesequel (3/14/2012)
Wow!Thank you guys again!It appears my training book is outdated.
Looks like they made some significant syntax changes from SQL 2006 to SQL 2008.
I will have to find a tutorial for procedures somewhere else then.
It appears your training book is for a different product.
There is no "Microsoft SQL Server 2006".
-- Gianluca Sartori
March 14, 2012 at 10:16 am
No such thing as SQL Server 2006...but I believe the IN keyword for procedures is from MySql and not SQL Server. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2012 at 10:20 am
Sean Lange (3/14/2012)
No such thing as SQL Server 2006...but I believe the IN keyword for procedures is from MySql and not SQL Server. 😉
Correct. MySQL uses "IN" to designate input parameters.
-- Gianluca Sartori
March 14, 2012 at 10:59 am
Gianluca Sartori (3/14/2012)
Sean Lange (3/14/2012)
No such thing as SQL Server 2006...but I believe the IN keyword for procedures is from MySql and not SQL Server. 😉Correct. MySQL uses "IN" to designate input parameters.
And in Oracle you specify IN, IN OUT, or OUT. This comes after the parameter however. IIRC, IN is the default so doesn't have to be specified.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply