Kevin Wilson wrote :
>
>
>Why can't I use a select to set a procedure variable? It barks out this
>error where it hit the line "SET B = SELECT ..."
>
>CREATE DBPROC CLEANSNFLAGSDUPES(IN PREFIX VARCHAR(4), IN MESSAGE
>VARCHAR(1000)) AS
>VAR FID INTEGER; B VARCHAR(6); NROWS INTEGER;
>SET FID = 0;
>SET NROWS = 0;
>DECLARE C1 CURSOR FOR SELECT * FROM SNFLAGS
>WHERE PREFIX = :PREFIX AND MESSAGE = :MESSAGE AND ACTIVE = 'Y';
>WHILE ($rc = 0) DO
> BEGIN
> FETCH C1 INTO :FID;
> IF $rc = 0 THEN
> BEGIN
> SET B = SELECT BODY FROM SNFLAGS WHERE FLAGID = :FID;
> SET NROWS = SELECT COUNT(*) FROM SNFLAGS
> WHERE PREFIX =
>:PREFIX AND BODY =
>:B
> AND MESSAGE =
>:MESSAGE AND ACTIVE
>= 'Y';
> IF NROWS > 0 THEN
> BEGIN
> UPDATE SNFLAGS SET ACTIVE = 'N'
> WHERE PREFIX = :PREFIX AND BODY = :B
> AND MESSAGE = :MESSAGE AND
>ACTIVE = 'Y';
>
> UPDATE SNFLAGS SET ACTIVE = 'Y' WHERE
>FLAGID = :FID;
> END;
> END;
> END;
>CLOSE C1;
>
>
>Thanks mucho for the help,
>
>Kev
>
>--
You cannot assign the result of a query to a variable by the assignment
statenment,
because this is not part of the supported syntax :
<assignment_statement> ::= [SET] <variable_name> = <expression>
But you can use a single select statement to assign the value to the
variable :
SELECT BODY INTO :B FROM SNFLAGS WHERE FLAGID = :FID;
SELECT COUNT(*) INTO :NROWS FROM SNFLAGS
WHERE PREFIX => :PREFIX AND BODY =>:B
AND MESSAGE => :MESSAGE AND ACTIVE >= 'Y';
Best Regards,
Thomas
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
