MySQL Bugs: #26140: View's SELECT contains a variable or parameter, Error 1351

Bug #26140	View's SELECT contains a variable or parameter, Error 1351
Submitted:	7 Feb 2007 9:12	Modified:	7 Feb 2007 10:52
Reporter:	Hansjörg Schock	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Windows (WIN XP Prof SP2)
Assigned to:		CPU Architecture:	Any

Description:
I have tried to create a Stored Procedure which creates a VIEW.
The Procedure has the IN parameter "id".
This parameter should be used later in:

WHERE
     B.SystemsID = id

When I execute the SP, MySQL reports the error:

View's SELECT contains a variable or parameter, Error 1351

If I fill in for example:
WHERE
     B.SystemsID = '7'

all is OK.

How to repeat:
CREATE PROCEDURE  `test`.`proc_gettabs`(IN id INT)
BEGIN
CREATE OR REPLACE VIEW viewpc_gettabs AS
SELECT
A.TabName,A.TabsID,A.TabWidth,C.English
FROM
tabs A, groups B, text C
WHERE
     B.SystemsID = id
AND B.TabsID = A.TabsID
AND A.TextID = C.TextID
GROUP BY B.TabsJID ;
END $$

Thank you for a problem report. Sorry, but this is a documented limitation. Read http://dev.mysql.com/doc/refman/5.0/en/create-view.html:

"A view definition is subject to the following restrictions:
...
-      Within a stored routine, the definition cannot refer to routine parameters or local variables."

What a bummer,
what is the reason for that?
So you can never use a View with parameters or variables?

kind regards
Hansjoerg

This is a limitation of current implementation. What is the reason? I don't know (but serious enough for the limitation to be documented explicitely!) 

I hope you will be able to create views using parameters eventually. Maybe, with prepared statements (http://dev.mysql.com/doc/refman/5.0/en/sqlps.html). CREATE VIEW is not supported with prepared statements yet, in 5.0.x, according to the manual. So, not in 5.0.27.