I have a C# desktop app that calls various SQL Server stored procedures to perform various work of exporting and importing data to a SQL Server 2008 R2 database.
These all work fine, no problem. And my app calls them just fine with all parameters etc.
In order to "assist the user", I'm coding a button to add all the stored procedures to the configured database. To this end, I've created a script along the lines of:
USE [%DATABASENAME%]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMyProc1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spMyProc1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMyProc2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spMyProc2]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spMyProc3]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spMyProc3]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spMyProc1]
@VariousParams varchar(100),
@ResultText varchar(4000) OUTPUT
AS
BEGIN
-- Code removed for brevity
END
GO
--
CREATE PROCEDURE [dbo].[spMyProc2]
@VariousParams varchar(100),
@ResultText varchar(4000) OUTPUT
AS
BEGIN
-- Code removed for brevity
END
GO
--
CREATE PROCEDURE [dbo].[spMyProc3]
@VariousParams varchar(100),
@ResultText varchar(4000) OUTPUT
AS
BEGIN
-- Code removed for brevity
END
GO
When I run this in SQL Server Management Studio, it runs fine, no problems at all.
However in my C# app, an exception is thrown and I get a boat load of errors as follows:
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.
Incorrect syntax near 'GO'.
Must declare the scalar variable "@MessageText".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@SourceDataFolder".
Must declare the scalar variable "@SourceDataFolder".
Must declare the scalar variable "@SequenceNo".
Must declare the scalar variable "@UserID".
Must declare the scalar variable "@SequenceNo".
Must declare the scalar variable "@UserID".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@ListOfIDsToImport".
Must declare the scalar variable "@MessageText".
Must declare the scalar variable "@MessageText".
Must declare the scalar variable "@MessageText".
Incorrect syntax near 'GO'.
The variable name '@PS_DEFAULT' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PS_ERROR_MSG' has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name '@PS_ERROR_SEVERITY' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@SequenceNo".
Incorrect syntax near 'GO'.
(This is what's in the ex.Message as caught by the catch block in the code below).
My code is very straightforward as follows:
bool retVal = false;
string command = Properties.Resources.MyApp_StoredProcedures.ToString().Replace("%DATABASENAME%", Properties.Settings.Default.DBName);
try
{
sqlCmd = new SqlCommand(command, csSQLConnection._conn);
sqlCmd.ExecuteNonQuery();
retVal = true;
}
catch (Exception ex)
{
retVal = false;
}
finally
{
sqlCmd.Dispose();
}
(The replace above simply replaces the placeholder in the USE line at the top of the script and it works as I can see when I step through and over that line).
So basically, what am I doing wrong as the SQL itself seems fine?
Many thanks
The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements.
GO statement is used as a Batch separator in Sql Server. Batch is nothing but one or more Sql Server statements sent to the Sql Server engine as one set of statements. GO is not a Transact-SQL statement, instead it is a command recognized by the Sql Server Management Studio (i.e. SSMS), SQLCMD and OSQL utilities.
If both the stored procedure and the table have the same owner, SQL Server will form an ownership chain when a user tries to execute the stored procedure. In this case, as long as the user has EXECUTE permissions on the stored procedure, the stored procedure will be able to reference the table.
This should be easy...
get rid of GO, that's SSMS specific syntax, the SQL language doesn't require or support it, rather you should terminate your individual create scripts with ; . Let me know how that goes.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With