Create a stored procedure using SQLAlchemy

[Daniel]

Hello,

I have a stored procedure for SQL Server and I would like to be able
to execute the code to create the stored procedure using SA. Here's
the basic idea.

engine = sqlalchemy.create_engine('mssql://connectionString')
engine.execute(myStoredProcedure)

Where:
myStoredProcedure = """
PRINT 'generate mySP stored procedure'
IF object_id('mySP') IS NOT NULL
BEGIN
DROP PROCEDURE mySP
END
GO

CREATE PROCEDURE mySP
AS
DECLARE @aVar VARCHAR(48)
BEGIN
SELECT TOP 1
@aVar = aVar
FROM [dbo].[someTable] (UPDLOCK)
WHERE
priority > 0
ORDER BY
priority DESC

SELECT
@aVar AS aVar
END
GO
"""

The problem is that I'm getting an error complaining about invalid
syntax "ProgrammingError: (ProgrammingError) ('42000', "[42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'CREATE'"

I know the stored procedure call works since I can run it against the
database. What do I need to do to execute my script from SA?

Thanks.

[Michael Bayer]

seems like a limitation of the DBAPI in use. have you tried creating
a test script using just raw (pyodbc | adodbapi | pymssql) ?

[Clovis Fabricio]

2009/5/19 Daniel <daniel....@gmail.com>:

> Hello,
> I have a stored procedure for SQL Server and I would like to be able
> to execute the code to create the stored procedure using SA.  Here's
> the basic idea.

> The problem is that I'm getting an error complaining about invalid
> syntax "ProgrammingError: (ProgrammingError) ('42000', "[42000]
> [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
> the keyword 'CREATE'"
> I know the stored procedure call works since I can run it against the
> database.  What do I need to do to execute my script from SA?

Execute each command separately. I've never seen a DBAPI driver that
allows execution of more than one full command in the same query.

myStoredProcedure = ["""

IF object_id('mySP') IS NOT NULL
BEGIN
DROP PROCEDURE mySP

END""",

"""
CREATE PROCEDURE mySP
AS
DECLARE @aVar VARCHAR(48)
BEGIN
SELECT TOP 1
@aVar = aVar
FROM [dbo].[someTable] (UPDLOCK)
WHERE
priority > 0
ORDER BY
priority DESC

SELECT
@aVar AS aVar
END

"""]

for command in myStoredProcedure: engine.execute(command)

[Michael Trier]

Hello,

You will need to split this into two separate execute calls. Also this stored procedure should be simplified.  There's no reason for the local variable.  It can be simplified as:

CREATE PROCEDURE mySP

AS

    SELECT TOP 1 aVar

    FROM [dbo].[someTable] (UPDLOCK)

    WHERE 

        priority > 0

    ORDER BY priority DESC

GO

 

--
Michael Trier
http://michaeltrier.com/

[Daniel]

Thanks. This works great.

On May 20, 4:54 pm, Clovis Fabricio <nos...@gmail.com> wrote:
> 2009/5/19 Daniel <daniel.watr...@gmail.com>: