MySQL - Sintaxis CREATE PROCEDURE (con TEMPORARY TABLE)

Buenas,
Llevo toda la mañana volviéndome loco con un CREATE PROCEDURE, he mirado todos los foros y no he conseguido detectar el error de sintaxis. Pongo a continuación el código por si alguien lo viera:

El error que da es:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 25

[La línea 25 corresponde al final del CREATE TEMPORARY -> );]

CREATE PROCEDURE spActClientes(
IN telefonoC char(9) ,
IN nombreC varchar(150) ,
IN direccionC varchar(500) ,
IN poblacionC varchar(100) ,
IN postalC smallint(8) unsigned,
IN email varchar(200) ,
IN nPucheros smallint(6) ,
IN valorPucheros decimal(10, 2) ,
IN bloqueaC tinyint(1) ,
IN memo varchar(2000)
)
BEGIN
CREATE TEMPORARY TABLE tTemp(
`telefonoC` char(9) ,
`nombreC` varchar(150) ,
`direccionC` varchar(500) ,
`poblacionC` varchar(100) ,
`postalC` smallint(8) unsigned,
`email` varchar(200) ,
`nPucheros` smallint(6) ,
`valorPucheros` decimal(10, 2) ,
`bloqueaC` tinyint(1) ,
`memo` varchar(2000)
);

INSERT INTO tTempo
VALUES
telefonoC,nombreC,direccionC,poblacionC,postalC,email,nPucheros,valorPucheros,bloqueaC,memo;
END;

Muchas gracias

Por si a alguien le interesa, este tema ha sido resuelto en:
http://www.forosdelweb.com/f86/sintaxis-create-procedure-con-temporary-table-632262/#post2610869

El problema que tienes es que quieres hacer que tu PROCEDURE cree una tabla temporal pero esto debes hacer en el siguiente orden,
CREATE PROCEDURE spActClientes
AS
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_option] ...
[partition_options]
GO

y asi se creeara tu tabla temporal, no nececitas rigurosamente que al crear tu sp le introduscas variable, puedes crear la tabla directamente como sigue

CREATE PROCEDURE sp_[Nombre_SP]
AS
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_option] ...
[partition_options]
GO

Asi cuando llamaes el EXCEC de tu sp crearas ña tabla temporal

O Tambien puedes usar esto para crear tablas dinamicas

Create temporary table with dynamic SQL - Karl Fuchs
14-Mar-08 06:37:52

Hi,

I try to create a table with Dynamic SQL.

The static SQL-Statement

CREATE PROCEDURE dbo.stProc_TblStatistik @DSNr nvarchar(5),
@dtBeginn smalldatetime,
@dtEnde smalldatetime AS

SET NOCOUNT ON

CREATE TABLE #tmpStatistik (intVorschrift int, intUnerlAnfang int,
intEing int, intErl int, intStattg int, intTeilStattgmWB int,
intTeilStattgoWB int, intAbl int, intSonstErl int, intUnerlEnde
int,
intWB int, intRuh int)

--Insert Records
INSERT INTO #tmpStatistik(intVorschrift) SELECT ID_VorschrZ FROM
_sys_tbl_VorschrZ

-- Update with Subquery
UPDATE #tmpStatistik SET intUnerlAnfang=( SELECT COUNT(D.txtNr) AS
AnzEingang
FROM dbo.tbl_VerfW D WHERE D.intVorschrift1 =
(D.dtZdADat IS NULL OR D.dtZdADat >= @dtBeginn) AND D.dtErfDat <
@dtBeginn AND DSNr = @DSNr)

....

runs well.

The SQL-Statement for Dynamic SQL however makes some trouble:

ALTER PROCEDURE dbo.stProc_TblStatistikDyn @DSNr nvarchar(5),
@dtBeginn smalldatetime,
@dtEnde smalldatetime AS

SET NOCOUNT ON

DECLARE @tblName nvarchar(20)
DECLARE @strSQL nvarchar(4000)
DECLARE @paramList nvarchar(400)

SET @tblName = '#' + @DSNr + 'tmpStatistik'

print @tblName

SET @strSQL = 'CREATE TABLE ' + @tblName + '(intVorschrift int,
intUnerlAnfang int,
intEing int, intErl int, intStattg int, intTeilStattgmWB int,
intTeilStattgoWB int, intAbl int, intSonstErl int, intUnerlEnde
int,
intWB int, intRuh int)'
print @strSQL
EXEC sp_executesql @strSQL

--Insert Records
SET @strSQL = 'INSERT INTO ' + @tblName + '(intVorschrift) SELECT
ID_VorschrZ FROM _sys_tbl_VorschrZ'
print @strSQL
EXEC sp_executesql @strSQL

SELECT @paramList = '@dtBeginn smalldatetime, @dtEnde smalldatetime,
@DSNr nvarchar(5)'
-- Update Records
SET @strSQL = 'UPDATE ' + @tblName + ' SET intUnerlAnfang=( SELECT
COUNT(D.txtNr) AS AnzEingang
FROM dbo.tbl_VerfW D WHERE D.intVorschrift1 = ' + @tblName +
'.intVorschrift AND
(D.dtZdADat IS NULL OR D.dtZdADat >= @dtBeginn) AND D.dtErfDat <
@dtBeginn AND DSNr = @DSNr)'
print @strSQL
EXEC sp_executesql @strSQL, @paramList, @dtBeginn, @dtEnde, @DSNr

The Error-Message is 'Invalid Objectname #84308tmpStatistik'. I also
tried it with the Object-Name of the static SQL-Statement, but I got
the same Error.

The SQL-Strings created with Set @strSQL seems ok:

CREATE TABLE #tmpStatistik(intVorschrift int, intUnerlAnfang int,
intEing int, intErl int, intStattg int, intTeilStattgmWB int,
intTeilStattgoWB int, intAbl int, intSonstErl int, intUnerlEnde
int,
intWB int, intRuh int)
INSERT INTO #tmpStatistik(intVorschrift) SELECT ID_VorschrZ FROM
_sys_tbl_VorschrZ

Any ideas about it?

Thank you in Advance

Karl
Suerte man