sqlserver預存程序語句)

SQL Server版文法

為了方便說明，資料庫使用SQL Server的樣本資料庫，Northwind和pubs，如果SQL Server中沒有的話，可以按下面的方法安裝

1，下載SQL2000SampleDb.msi,是：

http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

2，安裝後，到預設目錄C:\SQL Server 2000 Sample Databases 有instnwnd.sql ，instpubs.sql兩個檔案

3，在sql server中運行這兩個sql 就可以建立你Northwind和pubs資料庫。

下面開始學T-SQL的文法

一.注釋

-- 單行注釋，從這到本行結束為注釋,類似C++,c#中//

/* … */ 多行注釋，類似C++，C#中/* … */

二.變數（int, smallint, tinyint, decimal,float,real, money ,smallmoney, text ,image, char, varchar。。。。。。）

文法：

DECLARE

{

{@local_variable data_type}

} [,...n]

例如：

declare @ID int --申明一個名為@ID的變數，類型為int型

三.在SQL Server視窗中列印出變數的值

文法：

PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr

四.變數賦值

例如：

--從資料表中取出第一行資料的ID,賦值給變數@id，然後列印出來

Declare @ID int

Set @ID = (select top(1) categoryID from categories)

Print @ID

在SQL中，我們不能像代碼那樣直接給變數賦值，例如@id = 1，如果要達到這樣的功能，可以這樣寫：

Declare @ID int

Set @ID = (select 1) -- 類似 @ID=1

Select @id=1 -- 類似 @ID=1

Print @ID

五.變數運算(+,-,*,/,……)

以下必要時候省略變數申明

Set @ID = (select 1+5) --類似 @ID=1+5

Set @ID=(select 1-@ID) --類似 @ID=1-@ID

六.比較操作符

? > (greater than).

? < (less than).

? = (equals).

? <= (less than or equal to).

? >= (greater than or equal to).

? != (not equal to).

? <> (not equal to).

? ! < (not less than).

? !> (not greater than).

沒什麼說的

七.語句塊：Begin … end

將多條語句作為一個塊，類似與C++，C#中的{ }

例如：

Begin

Set @ID1 = (select 1)

Set @ID2 = (select 2)

End

八.If， if…else…

文法：

IF Boolean_expression

{sql_statement | statement_block}

[ELSE

{sql_statement | statement_block}]

例如：

If @id is not null

Print ‘@id is not null

if @ID = 1

begin

Set @ID = (select 1 + 1)

end

else

begin

set @ID=(select 1+2)

end

上面的例子用到了比較操作符，語句塊，和IF的文法。

九.執行其他預存程序 EXEC

例如

EXEC dbo.[Sales by Year] @Beginning_Date=’1/01/90’, @Ending_Date=’1/01/08’

十.事務

文法：

BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]

例如

BEGIN TRAN

-- 做某些操作，例如Insert into …

if @@error <> 0

BEGIN

ROLLBACK TRAN

END

else

BEGIN

COMMIT TRAN

END

十一.遊標

我們可以在預存程序中用Select語句取出每一行資料進行操作，這就需要用到遊標。

文法：

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

[FOR UPDATE [OF column_name [,...n]]]

例如：

DECLARE @au_id varchar(11), @au_fname varchar(20) –申明變數

--申明一個遊標

DECLARE authors_cursor CURSOR FOR

SELECT au_id, au_fname FROM authors

--開啟遊標

OPEN authors_cursor

--取出值

FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname

--迴圈取出遊標的值

WHILE @@FETCH_STATUS = 0

BEGIN

Print @au_id

Print @au_fname

Print ‘ ’

FETCH NEXT FROM authors_cursor

INTO @au_id, @au_fname

END

CLOSE authors_cursor –關閉遊標

DEALLOCATE authors_cursor --釋放遊標

我覺得上面的是預存程序常用的一些東東，如果要更深入的瞭解，更詳細的協助，請參考SQL Server的協助文檔

例子：

我自己做了一個,沒有問題,你可以看一下    

use   Northwind    

go    

create   proc   test        

@StartOrderID   int,        

@EndOrderID   int,        

@Code   varchar(1000)   Out    

As        

Begin            

Declare   @tmp   int                    

Set   @Code=''                            

Declare   #cur_orders   cursor     for     Select   OrderID   From   Orders                              

where   OrderID>=@startOrderID   and   OrderID<=@EndOrderID                            

for   read   only                    

Open   #cur_Orders                    

fetch   next   from   #cur_orders   into   @tmp                      

while   @@fetch_Status=0                        

Begin                                

Set   @Code=@Code+'-'+convert(varchar(8),@tmp)                                  

fetch   next   from   #cur_orders   into   @tmp                        

End                    

close   #cur_Orders                    

Deallocate   #cur_Orders                    

return                    

End    

go  

續2          

String   ret=null;            

try{                

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");                

String   url     ="jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";                

String   user="sa";                

String   password="";                

Connection   conn=   DriverManager.getConnection(url,user,password);                

CallableStatement   stmt=conn.prepareCall("exec   test   ?,?,?");                

stmt.setInt(1,10248);                

stmt.setInt(2,10284);                

stmt.registerOutParameter(3,Types.VARCHAR);                

stmt.setString(3,ret);                

stmt.execute();                

System.out.println(stmt.getString(3));                

stmt.close();                

stmt=null;                

conn.close();            

}

catch(ClassNotFoundException   e)

{                

e.printStackTrace();            

}

catch(SQLException   e)

{                

e.printStackTrace();            

}  

上面的例子沒有問題,針對你的情況,我又寫了一個,應該可以解決你現在的問題         --   建立一個表        

Create   table   tmpOrders   (               OrderID   int,               CustomerID   nchar(5)       )         --把Orders   裡的OrderID列全部插入,這樣Orders與tmpOrders之間就是1:1關係了     insert   into   tmpOrders     Select   distinct   orderID,'tmp'   from   Orders         create   proc   test         @StartOrderID   int,         @EndOrderID   int,         @Code   varchar(1000)   Out     As         Begin             Declare   @newOrderID   int             Declare   @newCustomerID   nchar(5)             Declare   @DummyInt   int             Declare   @DummyChar   nchar(5)                     Set   @Code=''                         /*                                                                                                                                     1:1                               temp   table/formal   table   is   synchronized   tmpOrders     <--->   Orders                             fetch   from   Orders,   update   tmpOrders                     */                         --   for   temp   table                     Declare   #cur_tmpOrders     Cursor   for   select   OrderID,CustomerID                                     From   tmpOrders                                           where   OrderID>=@startOrderID                                           and   OrderID<=@EndOrderID                                                                           for     update                         --for   formal   table                     Declare   #cur_orders   cursor     for     Select   OrderID,CustomerID                                 From   Orders                               where   OrderID>=@startOrderID                               and   OrderID<=@EndOrderID                               for   read   only                     Open   #cur_Orders                     Open   #cur_tmpOrders                         fetch   next   from   #cur_tmpOrders   into   @DummyInt,@dummyChar   --Important!!!                     fetch   next   from   #cur_orders   into   @NewOrderID,@NewCustomerID                       while   @@fetch_Status=0                         Begin                                 --Set   @Code=@Code+'-'+convert(varchar(8),@NewOrderID)                                 --update   tempOrders   use   corresponding   Orders'   data                                   Update   tmpOrders   set   customerID=@newCustomerID                                               where   current   of   #cur_tmpOrders                                     --pay   attention   to   sequence   of   cursor   fetch   action!                                   fetch   next   from   #cur_tmpOrders   into   @DummyInt,@dummyChar                                   if   @@fetch_Status<>0     break;   --   沒有行了                                   fetch   next   from   #cur_orders   into   @newOrderID,@NewCustomerID                         End                         close   #cur_Orders                     close   #cur_tmpOrders                         Deallocate   #cur_Orders                     Deallocate   #cur_tmpOrders                     Set   @Code='Ok'                     return                     End  

 

程式如下             try{                 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");                 String   url=     "jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";                 String   user="sa";                 String   password="";                 Connection   conn=   DriverManager.getConnection(url,user,password);                 CallableStatement   stmt=conn.prepareCall("exec   test     ?,?,?");                 stmt.setInt(1,10248);                 stmt.setInt(2,10284);                 stmt.registerOutParameter(3,Types.VARCHAR,1000);                 stmt.setString(3,ret);                 stmt.executeUpdate();                 System.out.println(stmt.getString(3));                 stmt.close();                 stmt=null;                 conn.close();                 conn=null;             }catch(ClassNotFoundException   e){                 e.printStackTrace();             }catch(SQLException   e){                 e.printStackTrace();             }