mssql存儲過程實例
create table mytb
(tid int,
subject varchar(100),
content varchar(100))
eg：測試數據：
20 計算機應用 應用軟體開發
21 計算機操作系統 操作系統詳解
22 c# c#開發實戰
=======================================
說明一點：tid編號是連續的，如（1，2，3，4···）
存儲過程如下，測試通過：
create proc my_search
@beg_id int ,
@end_id int
as
declare @names varchar(200),
@bcp varchar(8000)
if(@beg_id<@end_id)
begin
while @beg_id<=@end_id
begin
select @names=subject from mytb where tid=@beg_id
set @bcp = 'bcp "SELECT content FROM 資料庫名.dbo.mytb where tid = ' + rtrim(@beg_id) + '" queryout "E:\11\' + rtrim(@names) + '.txt" -c -U"sa" -P"sa"'
EXEC master..xp_cmdshell @bcp
set @beg_id=@beg_id+1
end
end
==================加路徑後過程==============
--exec my_search 20,21,'E:\11'
ALTER proc my_search
@beg_id int ,
@end_id int ,
@path varchar(200)
as
if(right(@path,1)='\')
begin
set @path=left(@path,len(@path)-1)
end
declare @names varchar(200),
@bcp varchar(8000)
if(@beg_id<@end_id)
begin
while @beg_id<=@end_id
begin
select @names=subject from mytb where tid=@beg_id
set @bcp = 'bcp "SELECT content FROM cdma.dbo.mytb where tid = ' + rtrim(@beg_id) + '" queryout "'+@path+'\' + rtrim(@names) + '.txt" -c -U"sa" -P"sa"'
EXEC master..xp_cmdshell @bcp
set @beg_id=@beg_id+1
end
end
=======
呵呵，很抱歉啊 樓主，沒能實現你的要求，
MSSQL我不熟，我想語法大概也是差不多吧，
⑵ 在SQL Server資料庫中 存儲過程是什麼,希望給點例題
存儲過程不僅sqlserver里有、其他dbms也有、只是語法有小出入。
存儲過程是一段存儲在系統中可隨時使用的可帶參數的SQL執行語句或程序段。
使用存儲過程可以避免重復的填寫SQL語句、可以快速的得到符合條件的某段SQL語句的執行結果。
例子(MSSQL)：比如想實現一個表a(aid int,atime datetime)輸入aid的值得到對應的atime的值
創建：
create proc proc_test //創建過程
@aid int //定義一個參數(默認為in參數)
as
declare @atime datetime //定義一個變數用於接收返回值(游標在這里定義)
begin
select @atime=atime from a where aid=@aid //select @xxx=xxx from是給變數賦值、也是sql語句的符合條件值
print @atime //print方法輸出顯示變數
end
執行：
exec proc_test a表中aid的任意一個值
以後想實現通過輸入aid(或者其他列)的值得到atime(或者其他列)的值、就不用一遍一遍的寫sql語句
這只是一個簡單的應用。如果SQL語句是復雜語句、不是就省事多了？
這個例子只能返回一條記錄、如果想返回多條、需要用游標。
這里暫時只給你簡單應用、你先理解下。
⑶ c# 執行sql存儲過程
一、方法/步驟1:開啟資料庫連接服務
using System.Data.SqlClient;
SqlConnection conn = new SqlConnection();
string connectionString="server=.;database=Sql;uid=sa; pwd=123456";
conn.ConnectionString = connectionString;
conn.open();
到這一步後，可以通過命令查看下資料庫的連接狀態：
Console.Write(conn.State.ToString());
執行該條命令後，如果存在錯誤，可能是資料庫的版本問題，這時候可以打開SQL Server配置管理器查看，正常的資料庫應該是MSSQLSERVER。
二、方法/步驟2:在開發工具中通過用戶名和口令與資料庫進行關聯。
三、方法/步驟3:執行SQL語句
string sqlStr = "SELECT * FROM table2";
SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;
cmd.CommandText = sqlStr; cmd.CommandType = CommandType.Text;
int i = Convert.ToInt32(cmd.ExecuteNonQuery());
Console.Write("總共有" + i.ToString() + "條數據");
四、方法/步驟4:關閉相關連接和服務。所有的一切做完後，要記得關閉資料庫連接
conn.Close();
⑷ MSSQL中遇到存儲過程加密出錯不能查看
存儲過程困耐模解密(破解函數,過程,觸汪緩發器,視圖.僅限於SQLSERVER2000)
--*/
/*--調用示例:
--解密指定存儲過程
exec sp_decrypt '存儲過程名'
--*/
create PROCEDURE sp_decrypt(@objectName varchar(50))
AS
begin
begin tran
declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
declare @sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000),@sql5 nvarchar(4000),@sql6 nvarchar(4000),@sql7 nvarchar(4000),@sql8 nvarchar(4000),@sql9 nvarchar(4000),@sql10 nvarchar(4000)
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare @i int,@status int,@type varchar(10),@parentid int
declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)
create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)
select @number=max(number) from #temp
set @k=0
while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
if @type='P'畝段
set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
end)
if @type='TR'
set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
if @type='FN' or @type='TF' or @type='IF'
set @sql1=(case @type when 'TF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)
if @type='V'
set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
set @q=len(@sql1)
set @sql1=@sql1+REPLICATE('-',4000-@q)
select @sql2=REPLICATE('-',4000),@sql3=REPLICATE('-',4000),@sql4=REPLICATE('-',4000),@sql5=REPLICATE('-',4000),@sql6=REPLICATE('-',4000),@sql7=REPLICATE('-',4000),@sql8=REPLICATE('-',4000),@sql9=REPLICATE('-',4000),@sql10=REPLICATE('-',4000)
exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql10)
end
set @k=@k+1
end
set @k=0
while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
select @colid=max(colid) from #temp where number=@k
set @n=1
while @n<=@colid
begin
select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k
SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
if @n=1
begin
if @type='P'
SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
end)
if @type='FN' or @type='TF' or @type='IF'--剛才有錯改一下
SET @OrigSpText2=(case @type when 'TF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)
if @type='TR'
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
if @type='V'
set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 '
set @q=4000-len(@OrigSpText2)
set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET @OrigSpText2=REPLICATE('-', 4000)
end
--start counter
SET @i=1
--fill temporary variable
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
--loop
WHILE @i<=datalength(@OrigSpText1)/2
BEGIN
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
set @resultsp=(case when @encrypted=1
then @resultsp
else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
end)
print @resultsp
--execute( @resultsp)
set @n=@n+1
end
end
set @k=@k+1
end
drop table #temp
rollback tran
end
GO
⑸ 怎樣SQL存儲過程中執行動態SQL語句
存儲過程中執行動態Sql語句
MSSQL為我們提供了兩種動態執行SQL語句的命令，分別是EXEC和sp_executesql;通常,sp_executesql則更具有優勢，它提供了輸入輸出介面，而EXEC沒有。還有一個最大的好處就是利用sp_executesql，能夠重用執行計劃，這就大大提供了執行性能，還可以編寫更安全的代碼。EXEC在某些情況下會更靈活。除非您有令人信服的理由使用EXEC，否側盡量使用sp_executesql.
1.EXEC的使用
EXEC命令有兩種用法，一種是執行一個存儲過程，另一種是執行一個動態的批處理。以下所講的都是第二種用法。
下面先使用EXEC演示一個例子,代碼1
代碼
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR (MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql =
'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+
CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
EXEC(@sql);
註：這里的EXEC括弧中只允許包含一個字元串變數，但是可以串聯多個變數，如果我們這樣寫EXEC：
EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+
QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');
SQL編譯器就會報錯，編譯不通過，而如果我們這樣：
EXEC(@sql+@sql2+@sql3);
編譯器就會通過；
所以最佳的做法是把代碼構造到一個變數中，然後再把該變數作為EXEC命令的輸入參數，這樣就不會受限制了。
EXEC的缺點是不提供介面，這里的介面是指，它不能執行一個包含一個帶變數符的批處理，如下
代碼
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
EXEC(@sql);
關鍵就在SET @sql這一句話中，如果我們運行這個批處理，編譯器就會產生一下錯誤
Msg 137, Level 15, State 2, Line 1
必須聲明標量變數 "@OrderID"。
使用EXEC時，如果您想訪問變數，必須把變數內容串聯到動態構建的代碼字元串中，如：
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + 'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
串聯變數的內容也存在性能方面的弊端。SQL Server為每一個的查詢字元串創建新的執行計劃，即使查詢模式相同也是這樣。為演示這一點，先清空緩存中的執行計劃
DBCC FREEPROCCACHE (這個不是本文所涉及的內容，您可以查看MS的MSDN)
將代碼1運行3次，分別對@OrderID 賦予下面3個值，10251，10252，10253。然後使用下面的代碼查詢
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
點擊F5運行，我們可以看到，每執行一次都要產生一次的編譯，執行計劃沒有得到充分重用。
EXEC除了不支持動態批處理中的輸入參數外，他也不支持輸出參數。默認情況下，EXEC把查詢的輸出返回給調用者。例如下面代碼返回Orders表中所有的記錄數
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
EXEC(@sql);
然而，如果你要把輸出返回給調用批處理中的變數，事情就沒有那麼簡單了。為此，你必須使用INSERT EXEC語法把輸出插入到一個目標表中，然後從這表中獲取值後賦給該變數，就像這樣：
代碼
DECLARE @sql NVARCHAR(MAX),@RecordCount INT
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
CREATE TABLE #T(TID INT);
INSERT INTO #T EXEC(@sql);
SET @RecordCount = (SELECT TID FROM #T)
SELECT @RecordCount
DROP TABLE #T2
2.sp_executesql的使用
sp_executesql命令在SQL Server中引入的比EXEC命令晚一些，它主要為重用執行計劃提供更好的支持。
為了和EXEC作一個鮮明的對比,我們看看如果用代碼1的代碼，把EXEC換成sp_executesql,看看是否得到我們所期望的結果
代碼
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT ,@sql2 NVARCHAR(MAX);
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
EXEC sp_executesql @sql
注意最後一行；事實證明可以運行；
sp_executesql提供介面
sp_executesql命令比EXEC命令更靈活，因為它提供一個介面，該介面及支持輸入參數也支持輸出參數。這功能使你可以創建帶參數的查詢字元串，這樣就可以比EXEC更好的重用執行計劃,sp_executesql的構成與存儲過程非常相似，不同之處在於你是動態構建代碼。它的構成包括：代碼快，參數聲明部分，參數賦值部分。說了這么多，還是看看它的語法：
EXEC sp_executesql
@stmt= <statement>,--類似存儲過程主體
@params = <params>, --類似存儲過程參數部分,聲明參數類型
<params assignment> --類似存儲過程調用，為參數賦值,參數值要和參數順序要一一對應，也可以通過為參數指明參數值的方式為其賦值
@stmt參數是輸入的動態批處理，它可以引入輸入參數或輸出參數，和存儲過程的主體語句一樣，只不過它是動態的，而存儲過程是靜態的，不過你也可以在存儲過程中使用sp_executesql;
@params參數與定義輸入/輸出參數的存儲過程頭類似，實際上和存儲過程頭的語法完全一樣；
@<params assignment> 與調用存儲過程的EXEC部分類似。
其實@stmt,@params可以省略，那麼exec sp_executesql的語法就可以簡寫成如下格式：
EXEC sp_executesql
<statement>,
<params>,
<params assignment>
為了說明sp_executesql對執行計劃的管理優於EXEC,我將使用前面討論EXEC時用到的代碼。
代碼
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
--注意當要對動態sql語句的表名實行參數化時，不可以如下表示：
--set @sql='select * from @TableName where OrderID=@OID ORDER BY Orderid desc',
--如果這樣會提示必須聲明標量變數@TableName,只可以如上面所寫的一樣，將表名@TableName作為變數名進行拼接
EXEC sp_executesql
@sql,
N'@OID int ',
@OID = @OrderID
下面我們看看exec sp_executesql的執行效率，在調用該代碼和檢查它生成的執行計劃前，先清空緩存中的執行計劃；
DBCC FREEPROCCACHE
將上面的動態代碼執行3次，每次執行都賦予@OrderID 不同的值，然後查詢sys.syscacheobjects表，並注意它的輸出，優化器只創建了一個備用計劃，而且該計劃被重用的3次
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
點擊F5運行。
sq_executesql的另一個與其介面有關的強大功能是，你可以使用輸出參數為調用批處理中的變數返回值。利用該功能可以避免用臨時表返回數據，從而得到更高效的代碼和更少的重新編譯。定義和使用輸出參數的語法與存儲過程類似。也就是說，你需要在聲明參數時指定OUTPUT子句。例如，下面的靜態代碼簡單的演示了如何從動態批處理中利用輸出參數@p把值返回到外部批處理中的變數@i.
DECLARE @sql AS NVARCHAR(12),@i AS INT;
SET @sql = N' SET @p = 10';
EXEC sp_executesql
@sql,
N'@p AS INT OUTPUT',
@p = @i OUTPUT
SELECT @i --該代碼返回輸出10
以字母 N 為前綴標識 Unicode 字元串常量
總結以下幾點：
一.使用exce sp_executesql效率比exec要高，同一類型的語句，只需編譯一次即可，而exec執行幾次就需要編譯幾次。
二.構造動態sql的where子句，也就是條件子句時，exec無法使用變數來進行站位，需要將變數轉換成字元串，然後和動態sql進行拼接，這就可能引起Sql注入問題，如下：
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
而若使用exec sp_executesql則可以使用變數來進行站位，以後再給這個參數傳值的放式構造動態sql，就避免的Sql注入的問題，如下：
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
三.無論是Exec還是Exec sp_executesql，如果想要將表名和列名進行動態參數化，不可以使用表名參數和列名參數來進行站位，而且表名參數和列名參數需要使用存儲過程的參數.對 於exec sp_executesql來說，不可以將表名參數和列名參數在指定為在exec sp_executesql參數聲明部分聲明的參數，如：
代碼
create PROCEDURE GetData
@tbName nvarchar(10),
@colName nvarchar(10),
@Name nvarchar(10)
AS
BEGIN
declare @sql nvarchar(50);
set @sql='select '+ @colName+' from ' +@tbName+ ' where name=@whereName';
--注意此句不可以寫成如下:
-- set @sql='select @colName from @tbName where name=@whereName';
exec sp_executesql
@sql,
N'@whereName nvarchar(10)',
@Name
END
⑹ mssql 存儲過程中循環如何寫，在循環中用什麼語句跳出循環呢，在線等
給你個例子:
for loop是這樣的:
createprocere pro
begin
declare aintdefault1;
label1: loop
if a<6then
selecta;
seta=a+1;
iterate label1;
endif;
leave label1;跳出循環
endloop label1;
end
while是這樣的:
createprocere pro
begin
declare aintdefault4;
while a<10 do
selecta;
seta=a+1;
endwhile;
end
⑺ MSSQL資料庫中定時存儲的存儲過程
您好！要實現你要的功能；操作如下：
一、可以先寫好存儲過程的內容；放在目標資料庫中
二、在MSSQL的SQL server代理——〉作業——〉新建一個作業 在[常規]選項卡中 設置 名稱AUTOZY——〉在[步驟]選項卡中 點擊[新建作業]
三、在彈出的作業步驟窗口中；填寫步聚名稱（自己取一個）－－運行身份選擇SA或者WINDOWS運行都可以－－－在資料庫處選擇 目標資料庫（這步很關鍵）－－－
在命令 處 寫 exec 存儲過程的名稱（這個不要寫錯）－－－點擊[確定] 返回上級窗口
四、選擇[計劃]選項卡 －－在計劃窗口中可以具體設置怎麼樣定時運行的方案
五、設置好後 [確定]返回上級窗口－－－再點擊[確定]保存了這個作業
如有不明之處；歡迎CALL 我