problem with storeprocedure creation

Prasanth I D

i am working in C#.net (windows application) and sql server 2005.

In my project i have to create multiple storeprocedures. So that i am keeping the query in a single text file. In between every storeprocedures i put a 'go' statement. But while running, it shows an error...

here is my code,

SqlCommand sqlEmp = new SqlCommand();

sqlEmp.Connection = CPublic.Comm_con;

sqlEmp.CommandType = CommandType.Text;

string s = "";

using (StreamReader sr = new StreamReader(@"D:\sp.txt"))

{s = sr.ReadToEnd();}

sqlEmp.CommandText = s;

sqlEmp.ExecuteNonQuery();

can u help me ?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prasanth I D17y ago
it is not working......

still the same error...
0
Jan Montano17y ago
try to include the BEGIN - END key

create procedure sp_org_insert1

(@name varchar(60)='',
@add1 varchar(50)='',
@add2 varchar(50)='',
@add3 varchar(50)='',
@add4 varchar(50)='',
@email varchar(150)='',
@website varchar(150)='',
@phone1 varchar(30)='',
@phone2 varchar(30)='',
@finstart varchar(20)=null ,
@finend varchar(20)=null ,
@findiviions tinyint=0)
as
BEGIN
declare @idno bigint

select @idno=max(idno)+1 from org_det

insert into org_det
(idno,name,add1,add2,add3,add4,email,website,
phone1,phone2,finstart,finend,findivisions)
values
(@idno,@name,@add1,@add2,@add3,@add4,@email,
@website,@phone1,@phone2,@finstart,@finend,@findiviions)
END
--------------------------------------------------------
GO

----------------------------------------
CREATE PROCEDURE [dbo].[sp_TM_First1]

AS
Select Top 1 TripNo
from TripSheet order by TripNo
GO
0
Prasanth I D17y ago
i am getting error message,

Incorrect syntax near 'GO'.

i am trying to create two storeprocedures using file sp.txt

create procedure sp_org_insert1

(@name varchar(60)='',
@add1 varchar(50)='',
@add2 varchar(50)='',
@add3 varchar(50)='',
@add4 varchar(50)='',
@email varchar(150)='',
@website varchar(150)='',
@phone1 varchar(30)='',
@phone2 varchar(30)='',
@finstart varchar(20)=null ,
@finend varchar(20)=null ,
@findiviions tinyint=0)
as
declare @idno bigint

select @idno=max(idno)+1 from org_det

insert into org_det
(idno,name,add1,add2,add3,add4,email,website,
phone1,phone2,finstart,finend,findivisions)
values
(@idno,@name,@add1,@add2,@add3,@add4,@email,
@website,@phone1,@phone2,@finstart,@finend,@findiviions)
--------------------------------------------------------
GO

----------------------------------------
CREATE PROCEDURE [dbo].[sp_TM_First1]

AS
Select Top 1 TripNo
from TripSheet order by TripNo

for running multiple queries in sql we use 'go' statement in between them.

but it is not working in here.

regards,

Prasanth I D
0
Jan Montano17y ago
What specific error are you encountering?
could you also post the content of your sql file?

Thanks.
0