Create procedure 存儲名 as declare @sql....varchar.... select @sql ... from ......group by .... exec(@sql).............. go太激動了,Go錯地方了!呵呵~~~~
你把SQL腳本轉成字串,是不能直接執行的. 比如 create proc A as select 1 GO create proc B as select2 在SQL中編譯通不過, 而用 create proc A as select 1 GO create proc B as select 2 可以執行.(好像是換行回車的問題,具體偶也不懂)//個人覺得可以使用變通的方法,比如你的sql腳本改成如下樣子, 即在每個proc結束後,go前加空格,使用--注釋,後加分號;create proc A as select 1 GO --; create proc B as select 2 GO --; create proc C as select 3 GO --; create proc D as select 4 GO//在.net程式中,讀成string,使用split方式分隔,循環執行,比如在C#中:SqlConnection sc=new SqlConnection("...."); SqlDataAdapter da=new SqlDataAdapter();string sql="create proc A as select 1 GO --;create proc B as select 2 GO --;create proc C as select 3 GO --;create proc D as select 4 GO";string[] arr = sql.Split(new char[]{';'}); sc.Open() for (int i=0;i<arr.Length;i++) { da.SelectCommand=new SqlCommand(arr[i].ToString(),sc); da.SelectCommand.ExecuteNonQuery(); } sc.Close();
h_lj(贝塔) :用;就更不行了,用;脚本都会有问题。
as
declare @sql....varchar....
select @sql ...
from ......group by ....
exec(@sql)..............
go太激動了,Go錯地方了!呵呵~~~~
比如
create proc A as select 1 GO create proc B as select2 在SQL中編譯通不過,
而用
create proc A as select 1
GO
create proc B as select 2 可以執行.(好像是換行回車的問題,具體偶也不懂)//個人覺得可以使用變通的方法,比如你的sql腳本改成如下樣子,
即在每個proc結束後,go前加空格,使用--注釋,後加分號;create proc A as select 1
GO
--;
create proc B as select 2
GO
--;
create proc C as select 3
GO
--;
create proc D as select 4
GO//在.net程式中,讀成string,使用split方式分隔,循環執行,比如在C#中:SqlConnection sc=new SqlConnection("....");
SqlDataAdapter da=new SqlDataAdapter();string sql="create proc A as select 1 GO --;create proc B as select 2 GO --;create proc C as select 3 GO --;create proc D as select 4 GO";string[] arr = sql.Split(new char[]{';'});
sc.Open()
for (int i=0;i<arr.Length;i++)
{
da.SelectCommand=new SqlCommand(arr[i].ToString(),sc);
da.SelectCommand.ExecuteNonQuery();
}
sc.Close();
运行时把资源提取出来存成一个临时文件,然后调用isql执行,但这须要运行的机器上有sql客户端.