SQL Server Stored Procedure Statement)

SQL ServerSyntax

For ease of instruction, the database uses the example database of SQL Server, northwind and pubs. If SQL server does not exist, you can install it as follows:

1. Download sql2000sampledb. MSI, which is:

Http://www.microsoft.com/downloads/details.aspx? Familyid = 06616212-0356-46a0-8da2-eebc53a68034 & displaylang = en

2. After installation, go to the default directory c: \ SQL Server 2000 sample databases and have two files: instnwnd. SQL and instpubs. SQL.

3. Run these two SQL statements on SQL Server to create your northwind and pubs databases.

Start learning the T-SQL syntax below

I. Notes

-- Single-line comment, from this to the end of the line as a comment, similar to C ++, C //

/*... */Multi-line comments, similar to C ++, C # In /*... */

Ii. variables (INT, smallint, tinyint, decimal, float, real, money, smallmoney, text, image, Char, varchar ......)

Syntax:

Declare

{

{@ Local_variable data_type}

} [,... N]

For example:

Declare @ ID int -- declare a variable named @ ID. The type is int type.

3. Print the variable value in the SQL Server window

Syntax:

Print 'any ASCII text' | @ local_variable | @ function | string_expr

Iv. Variable assignment

For example:

-- Extract the ID of the first row of data from the data table, assign the value to the variable @ ID, and print it out.

Declare @ ID int

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

Print @ ID

In SQL, we cannot directly assign values to variables like code, for example, @ ID = 1. To achieve this function, you can write as follows:

Declare @ ID int

Set @ ID = (select 1) -- similar to @ ID = 1

Select @ ID = 1 -- similar to @ ID = 1

Print @ ID

5. Variable operations (+ ,-,*,/,......)

The variable declaration should be omitted if necessary

Set @ ID = (select 1 + 5) -- similar to @ ID = 1 + 5

Set @ ID = (select 1-@ ID) -- similar to @ ID = 1-@ ID

6. Comparison Operators

? > (Greater ).

? <(Less ).

? = (Equals ).

? <= (Less than or equal ).

? > = (Greater than or equal ).

? ! = (Not equal ).

? <> (Not equal ).

? ! <(Not less ).

? !> (Not greater ).

Nothing to say

VII. Statement block: Begin... End

Use multiple statements as a block, similar to {} in C ++ and C {}

For example:

Begin

Set @ id1 = (select 1)

Set @ Id2 = (select 2)

End

8. If, if... Else...

Syntax:

If boolean_expression

{SQL _statement | statement_block}

[Else

{SQL _statement | statement_block}]

For example:

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

The preceding example uses the comparison operators, statement blocks, and if syntax.

9. Execute other stored procedures Exec

For example

Exec DBO. [sales by year] @ beginning_date = '2017/90', @ ending_date = '2017/08'

10. Transactions

Syntax:

Begin Tran [saction] [transaction_name | @ tran_name_variable]

For example

Begin tran

-- Perform some operations, such as insert...

If @ error <> 0

Begin

Rollback tran

End

Else

Begin

Commit tran

End

11. cursor

We can use the SELECT statement in the stored procedure to retrieve each row of data for operations, which requires a cursor.

Syntax:

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]

For example:

Declare @ au_id varchar (11), @ au_fname varchar (20)-declare the variable

-- Declare a cursor

Declare authors_cursor cursor

Select au_id, au_fname from authors

-- Open the cursor

Open authors_cursor

-- Retrieve Value

Fetch next from authors_cursor into @ au_id, @ au_fname

-- Cyclically retrieve the cursor Value

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-close the cursor

Deallocate authors_cursor -- release cursor

I think the above are some of the frequently used stored procedures. For more details and help, please refer to the help documentation of SQL Server.

Example:

I made one myself. No problem. You can take a look.

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

Continued 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.exe cute ();

System. Out. println (stmt. getstring (3 ));

Stmt. Close ();

Stmt = NULL;

Conn. Close ();

}

Catch (classnotfoundexception E)

{

E. printstacktrace ();

}

Catch (sqlexception E)

{

E. printstacktrace ();

}

There is no problem with the above example. I wrote another one for you, which should solve your current problem-creating a table

Create Table tmporders (orderid int, customerid nchar (5) -- insert all orderid columns in orders, so that the relationship between orders and tmporders is and the relationship between 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 is absent, @ newcustomerid end close # cur_orders close # cur_tmporders deallocate # cur_orders deallocate # cur_tmporders set @ code = 'OK' return end

 

The program is as follows: 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.exe cuteupdate (); system. out. println (stmt. getstring (3); stmt. close (); stmt = NULL; Conn. close (); Conn = NULL;} catch (classnotfoundexception e) {e. printstacktrace ();} catch (sqlexception e) {e. printstacktrace ();}