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

[7 Feb 2007 9:12] Hansjörg Schock
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 $$
[7 Feb 2007 10:12] Valeriy Kravchuk
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."
[7 Feb 2007 10:52] Hansjörg Schock
What a bummer,
what is the reason for that?
So you can never use a View with parameters or variables?

kind regards
Hansjoerg
[8 Feb 2007 11:40] Valeriy Kravchuk
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.