Problems creating stored procedure using command text

  • Thread starter Thread starter Paul Say
  • Start date Start date
P

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
 
U¿ytkownik "Paul Say said:
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 "
Click to expand...
(...)

Try user ' instead of "
Set @SQL = 'Select i.*, c.Vendor '
etc.
Grzegorz
 
U¿ytkownik "Paul Say said:
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 "
Click to expand...
(...)

Try use ' instead of "
Set @SQL = 'Select i.*, c.Vendor '
etc.
Grzegorz
 
U¿ytkownik "Paul Say said:
have tried with single quotes bu still get the same problem.

(...)
Click to expand...

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
 
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.
 
U¿ytkownik "Paul Say said:
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.
Click to expand...

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
 
Back
Top