Problems creating stored procedure using command text

[Paul Say]

I am trying to write a program to create a database by executing the T-SQL
statments for the databases Tables, Views Stored Procedures etc. I have a
file that contains all the T-Sql Script. The program I have written will
create the tables ok, the views and even some of the stored procedures
however any stored procedures like the one listed will not execute as the
quotes seem to cause a problem. The error I get for example says invalid
Column name 'Select l.x, c.vendor', i ge this for each quoted line.

Stored Procedure

/****** Object: Stored Procedure dbo.RPF_CommitmentItem_GetAll Script Date:
8/10/2004 4:17:04 PM ******/
CREATE PROCEDURE RPF_CommitmentItem_GetAll
@Coid int,
@CommitmentId nvarchar(12),
@Filter nvarchar(500) = '1=1'
AS
Declare @SQL nvarchar(500)
SET @SQL = "Select i.*, c.Vendor "
SET @SQL = @SQL + "From RPF_CommitmentItem i "
SET @SQL = @SQL + " Inner Join (select Vendor, COID as C_Coid, Commitment as
C_Commitment from RPF_Commitment) c "
SET @SQL = @SQL + " ON i.Commitment = c.C_Commitment AND i.COID = c.C_COID "
SET @SQL = @SQL + " WHERE CoID= " + CONVERT(char(2), @CoID) + " AND
Commitment='" + @CommitmentId + "'"

IF @Filter <> '1=1'
SET @SQL = @SQL + " and ( " + @Filter + ")"
exec(@SQL)


Code to execute string containing the above

Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
Dim Command As New OleDb.OleDbCommand(Sql, Me.OleDbConnection1)
' Dim Command As New SqlClient.SqlCommand(Sql, SqlConnection1)
Command.CommandType = CommandType.Text
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)
Try
Command.ExecuteNonQuery()
Finally

Command.Connection.Close()
End Try
End Sub

 

[Grzegorz Danowski]

I am trying to write a program to create a database by executing the T-SQL
statments for the databases Tables, Views Stored Procedures etc. I have a
file that contains all the T-Sql Script. The program I have written will
create the tables ok, the views and even some of the stored procedures
however any stored procedures like the one listed will not execute as the
quotes seem to cause a problem. The error I get for example says invalid
Column name 'Select l.x, c.vendor', i ge this for each quoted line.

Stored Procedure

/****** Object: Stored Procedure dbo.RPF_CommitmentItem_GetAll Script
Date: 8/10/2004 4:17:04 PM ******/
CREATE PROCEDURE RPF_CommitmentItem_GetAll
@Coid int,
@CommitmentId nvarchar(12),
@Filter nvarchar(500) = '1=1'
AS
Declare @SQL nvarchar(500)
SET @SQL = "Select i.*, c.Vendor "

(...)

Try user ' instead of "
Set @SQL = 'Select i.*, c.Vendor '
etc.
Grzegorz

 

[Grzegorz Danowski]

I am trying to write a program to create a database by executing the T-SQL
statments for the databases Tables, Views Stored Procedures etc. I have a
file that contains all the T-Sql Script. The program I have written will
create the tables ok, the views and even some of the stored procedures
however any stored procedures like the one listed will not execute as the
quotes seem to cause a problem. The error I get for example says invalid
Column name 'Select l.x, c.vendor', i ge this for each quoted line.

Stored Procedure

/****** Object: Stored Procedure dbo.RPF_CommitmentItem_GetAll Script
Date: 8/10/2004 4:17:04 PM ******/
CREATE PROCEDURE RPF_CommitmentItem_GetAll
@Coid int,
@CommitmentId nvarchar(12),
@Filter nvarchar(500) = '1=1'
AS
Declare @SQL nvarchar(500)
SET @SQL = "Select i.*, c.Vendor "

(...)

Try use ' instead of "
Set @SQL = 'Select i.*, c.Vendor '
etc.
Grzegorz

 

[Paul Say]

have tried with single quotes bu still get the same problem.

 

[Grzegorz Danowski]

have tried with single quotes bu still get the same problem.

(...)

I have tried in QA this query:

CREATE PROCEDURE RPF_CommitmentItem_GetAll
@Coid int,
@CommitmentId nvarchar(12),
@Filter nvarchar(500) = '1=1'
AS
Declare @SQL nvarchar(500)
SET @SQL = 'Select i.*, c.Vendor '
SET @SQL = @SQL + 'From RPF_CommitmentItem i '
SET @SQL = @SQL + ' Inner Join (select Vendor, COID as C_Coid, Commitment as
C_Commitment from RPF_Commitment) c '
SET @SQL = @SQL + ' ON i.Commitment = c.C_Commitment AND i.COID = c.C_COID '
SET @SQL = @SQL + ' WHERE CoID= ' + CONVERT(char(2), @CoID) + ' AND
Commitment=''' + @CommitmentId + ''''

IF @Filter <> '1=1'
SET @SQL = @SQL + ' and ( ' + @Filter + ')'
exec(@SQL)

And it works (stored procedure is created).
Grzegorz

 

[Paul Say]

By QA do you mean Query analyser, if so you are right query analyser will
create the query, how ever when when executing through ado.net as command
text causes an error.

 

[Grzegorz Danowski]

By QA do you mean Query analyser, if so you are right query analyser will
create the query, how ever when when executing through ado.net as command
text causes an error.

I have made small snippet in c#:

using System;
using System.Data.SqlClient;

namespace CreateSpFromCsharp
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string mySql = @"
CREATE PROCEDURE RPF_CommitmentItem_GetAll
@Coid int,
@CommitmentId nvarchar(12),
@Filter nvarchar(500) = '1=1'
AS
Declare @SQL nvarchar(500)
SET @SQL = 'Select i.*, c.Vendor '
SET @SQL = @SQL + 'From RPF_CommitmentItem i '
SET @SQL = @SQL + ' Inner Join (select Vendor,
COID as C_Coid, Commitment as
C_Commitment from RPF_Commitment) c '
SET @SQL = @SQL + ' ON i.Commitment =
c.C_Commitment AND i.COID = c.C_COID '
SET @SQL = @SQL + ' WHERE CoID= ' +
CONVERT(char(2), @CoID) + ' AND
Commitment=''' + @CommitmentId + ''''";
SqlConnection myCon = new SqlConnection("Server=(local);" +
"Integrated Security=SSPI;" +
"Database=Testy");
SqlCommand myCom = new SqlCommand(mySql, myCon);
myCon.Open();
myCom.ExecuteNonQuery();
myCon.Close();
}
}
}

And it works...
Regards,
Grzegorz