Creating multiple sql stored procedures from vb .net

[iramirez]

Hi everyone,

I'm creating a database installer where I have some code to create database, tables, stored procedures, etc.

I don't have problems when creating the database, tables and inserting required data. My problem is when my code gets to the stored procedures section.

Here is what I do:

I have different text files (database.txt, tables.txt, insertdata.txt, storeprocedures.txt) where my sql syntax is located.

I execute every process/text file using the following:

ExecuteSql("master", GetSql("database.txt")) ' Creating 1 database.
ExecuteSql("mynewdatabase", GetSql("tables.txt")) ' Creating 15 tables.
ExecuteSql("mynewdatabase", GetSql("insertdata.txt")) ' Inserting data to many tables.
ExecuteSql("mynewdatabase", GetSql("storeprocedures.txt")) ' Creating 6 stored procedures.

Here is part of the error:

In exception handler: Incorrect syntax near the keyword 'PROCEDURE'. "This is the second stored procedure because the variables I get below belong to the second one"
Must declare the scalar variable "@intVariable1".
Must declare the scalar variable "@intVariable1".
Must declare the scalar variable "@intVariable1".
Must declare the scalar variable "@intVariable1".

When trying to create 6 or even 2 stored procedures using the same file ‘storeprocedures.txt' is not possible. If I leave just 1 it works but I really want to keep them together.

Creating 15 tables using the same file 'tables.txt' works, inserting data in different tables using the same file 'insertdata.txt' works.

This is the general syntax I use:

Create Procedure sp_stored1
@intVariablex int
As
Begin
Code
End

Create Procedure sp_stored2
intVariabley int
As
Begin
Code
End
..........
..........
..........

Is is possible to create more than 1 stored procedure the way I'm trying to?

Do I need to end every stored procedure with specific sql syntax, other than the 'end'?

I appreciate your response!!!

Thanks,

Israel

[kvprajapati]

>Is is possible to create more than 1 stored procedure the way I'm trying to?

GO (Transact-SQL)

[iramirez]

>Is is possible to create more than 1 stored procedure the way I'm trying to?

GO (Transact-SQL)

This only works when using sql editor, not for what I'm trying to do from a VB .net application.

Last paragraph below is my situation.

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.

Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.

Thanks

[kvprajapati]

You could use semi-colon.