给你个存储过程语法范例看看: USE Northwind GO DROP PROCEDURE OrderSummary GO CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS -- SELECT to return a result set summarizing -- employee sales. SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity) FROM Orders AS Ord JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID) GROUP BY Ord.EmployeeID ORDER BY Ord.EmployeeID-- SELECT to fill the output parameter with the -- maximum quantity from Order Details. SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]-- Return the number of all items ordered. RETURN (SELECT SUM(Quantity) FROM [Order Details]) GO-- Test the stored procedure.-- DECLARE variables to hold the return code -- and output parameter. DECLARE @OrderSum INT DECLARE @LargestOrder INT-- Execute the procedure, which returns -- the result set from the first SELECT. EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT-- Use the return code and output parameter. PRINT 'The size of the largest single order was: ' + CONVERT(CHAR(6), @LargestOrder) PRINT 'The sum of the quantities ordered was: ' + CONVERT(CHAR(6), @OrderSum) GO The output from running this sample is:EmployeeID SummSales ----------- -------------------------- 1 202,143.71 2 177,749.26 3 213,051.30 4 250,187.45 5 75,567.75 6 78,198.10 7 141,295.99 8 133,301.03 9 82,964.00 The size of the largest single order was: 130 The sum of the quantities ordered was: 51317
这里不是语法问题。GO是sqlserver查询分析器中的分批语句。即将GO之前的语句做为一个批来执行。 GO指令只能被查询分析器,OSQL,ISQL工具识别。而查询编译器是不认GO的。所以当生成的批语句在。NET执行时,将sqlcommand的命令字串发给查询编译器会报错。当去掉GO后,编译器认为所有语句是一个批次:create proc p as select 1 gocreate proc p1 as select 2 go当没有go之后,它认为是: crate proc p as select 1 create proc p2 as select 2即,创建一个存储过程p,在p的内部再创建p2. 这是不允许的,所以会报错。 建议你把导出的SQL语句存为文件,然后在.net中调用osql去执行。或者写成bat.如果用.net来执行纯代码,那么估计要用循环把GO拆出来,一段一段执行。除此之外,可能无解。
USE Northwind
GO
DROP PROCEDURE OrderSummary
GO
CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
-- SELECT to return a result set summarizing
-- employee sales.
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
GO-- Test the stored procedure.-- DECLARE variables to hold the return code
-- and output parameter.
DECLARE @OrderSum INT
DECLARE @LargestOrder INT-- Execute the procedure, which returns
-- the result set from the first SELECT.
EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT-- Use the return code and output parameter.
PRINT 'The size of the largest single order was: ' +
CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of the quantities ordered was: ' +
CONVERT(CHAR(6), @OrderSum)
GO
The output from running this sample is:EmployeeID SummSales
----------- --------------------------
1 202,143.71
2 177,749.26
3 213,051.30
4 250,187.45
5 75,567.75
6 78,198.10
7 141,295.99
8 133,301.03
9 82,964.00
The size of the largest single order was: 130
The sum of the quantities ordered was: 51317
GO指令只能被查询分析器,OSQL,ISQL工具识别。而查询编译器是不认GO的。所以当生成的批语句在。NET执行时,将sqlcommand的命令字串发给查询编译器会报错。当去掉GO后,编译器认为所有语句是一个批次:create proc p
as
select 1
gocreate proc p1
as
select 2
go当没有go之后,它认为是:
crate proc p
as select 1
create proc p2
as select 2即,创建一个存储过程p,在p的内部再创建p2. 这是不允许的,所以会报错。
建议你把导出的SQL语句存为文件,然后在.net中调用osql去执行。或者写成bat.如果用.net来执行纯代码,那么估计要用循环把GO拆出来,一段一段执行。除此之外,可能无解。