About sql server stored procedure and use

2011-10-12  来源:本站原创  分类:Database  人气:55 

Syntax:

DECLARE

{

{@ Local_variable data_type}

} [,... N]

For example:

declare @ ID int - declare a variable named @ ID, type int-type

Three in the SQL Server window, print out the value of the variable

Syntax:

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

IV. Variable Assignment

For example:

- Removed from the data in the first row of the table ID, as assigned to the variable @ id, and then print out

Declare @ ID int

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

Print @ ID

In SQL, we can not as directly as the code assigned to variables, such as @ id = 1, if you want to achieve this function, you can write:

Declare @ ID int

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

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

Print @ ID

V. variable computing (+,-,*,/, ...)

Omitted variables, when necessary, declare the following

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

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

VI. Comparison Operators

?> (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).

Nothing to say

VII. Block: Begin ... end

The multiple statements as a block, similar to C + +, C # in {}

For example:

Begin

Set @ ID1 = (select 1)

Set @ ID2 = (select 2)

End

Eight. 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 above example uses a comparison operator, block, and IF syntax.

IX. Perform other stored procedure EXEC

Such as

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

X. Affairs

Syntax:

BEGIN TRAN [SACTION] [transaction_name | @ tran_name_variable]

Such as

BEGIN TRAN

- Do some operations, such as Insert into ...

if @ @ error <> 0

BEGIN

ROLLBACK TRAN

END

else

BEGIN

COMMIT TRAN

END

XI. Cursors

We can remove the stored procedure using the Select statement for each row of data operations, which need to use 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 variables

- Declare a cursor

DECLARE authors_cursor CURSOR FOR

SELECT au_id, au_fname FROM authors

- Open the cursor

OPEN authors_cursor

- Remove the value

FETCH NEXT FROM authors_cursor INTO @ au_id, @ au_fname

- Remove the cursor's value cycle

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 cursor

DEALLOCATE authors_cursor - release the cursor

I think the above stored procedure is commonly used in some of the stuff, if you want a better understanding, more detailed help, refer to the help documentation for SQL Server

Example:

I own one, no problem, you can look at
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.execute ();
System.out.println (stmt.getString (3));
stmt.close ();
stmt = null;
conn.close ();
} Catch (ClassNotFoundException e) {
e.printStackTrace ();
} Catch (SQLException e) {
e.printStackTrace ();
}

The above example there is no problem for your situation, I wrote one, you should be able to solve problems

- Create a table
Create table tmpOrders (
OrderID int,
CustomerID nchar (5)
)

- To all Orders where OrderID column insert, such Orders and tmpOrders is 1:1 relationship between the
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; - no line
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

Procedures are 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.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 ();
}

相关文章
  • Java call SQL Server stored procedure parameters and return result sets at the same time 2010-03-31

    Transfer from: http://blog.csdn.net/kirbylynx/archive/2008/12/09/3483449.aspx Such as a SQL Server stored procedure: create procedure proc_test @ Q_type int, @ Value int, @ Count int output as begin update mytable set value = @ value where type = @ q

  • SQL Server stored procedure return values 2010-02-23

    SQL Server stored procedure return value in three ways, see the following code: 1, the definition of the table table1 create table table1 ( name varchar (30), age integer, primary key (name, age) ) 2, the definition of the stored procedure create pro

  • SQL SERVER stored procedure with large amounts of data into 2010-04-08

    SQL SERVER stored procedure to bulk insert data in database tables so the database system development, especially the need for database operations and optimization of the performance test, we need to test tables in the database to insert large amount

  • SQL Server stored procedure 2010-06-21

    Assist a VC + + project, with the database is SQLServer, the definition of a lot of stored procedures. After corresponding to modify the database structure, the natural need to modify the stored procedure, we first find out. Sql Server stored procedu

  • Note that little bit of sql server stored procedure 2010-11-10

    sql server stored procedure is provided by the use of Transact-SQL programs written language. It follows the following principles: 1), variable declaration 2), ANSI-compliant SQL commands (Select, Update ....) 3), the general flow control commands (i

  • Learning SQL SERVER stored procedure - one of the knowledge stored procedure syntax 2010-12-04

    CREATE PROCEDURE Create a stored procedure, stored procedure is saved that can accept and return user-supplied parameters of Transact-SQL statement collection. You can create a process for permanent use, or for temporary use in a session (local tempo

  • Detailed SQL SERVER stored procedure 2010-12-10

    Transfer from: http://tieba.baidu.com/f?kz=100695261 Detailed SQL SERVER stored procedure MS SQL Server is now increasingly becoming the most important WindowNT above the operating system of a database management system, with the introduction of MS S

  • Optimization of SQL Server stored procedure seven methods [reprint] 2010-12-13

    Optimization of SQL Server stored procedure seven methods Optimized stored procedures there are many ways, the most commonly used are described below 7. 1. Using SET NOCOUNT ON option We use the SELECT statement, in addition to returns the correspond

  • SQL Server stored procedure or function call Com Custom Components 2010-09-21

    Although the MS SQl stored procedure comes with a encryption method, but outside there are software solutions available off. The solution is to write a DLL or OLE program, and then call on the line in a database, SQL, many extended stored procedure i

  • sql server stored procedure, little attention 2010-11-10

    sql server stored procedure is to use it provides Transact-SQL language, the written procedures. It followed several principles: 1), variables description 2), ANSI-compliant SQL commands (Select, Update ....) 3), the general flow control commands (if

  • SQL SERVER stored procedure, study notes 2011-04-22

    SQL SERVER stored procedure, study notes The common or complex work, written in advance by the SQL statement using a specified name and stored, then later to call the database to provide well-defined stored procedure functions the same service, simpl

  • Java call SQL Server stored procedure example 2010-03-22

    Recently made a small Java program (the first to write Java project oh), to the online search for a long time to find a better point of calling a stored procedure example, and are commonly used online setXXX ((int parameterIndex, XXX x) form. This fo

  • SQL SERVER stored procedure database application developed 2010-07-27

    SQL SERVER database application developed by the stored procedure definition: Stored procedure is acceptable to save up and return the user supplied parameters of Transact-SQL statement collection. Can create a process for permanent use, or for tempo

  • Detailed two SQL Server stored procedure: sp_MSforeachtable / sp_MSforeachdb 2010-10-18

    1. Introduction: As a DBA will often need to check all of the database or user table, for example: Check the capacity of all the database; to see all the users to specify the database table capacity, number of records in all tables ..., we generally

  • jdbc sql server stored procedure is called when "the statement does not return a result set" solutions 2010-12-30

    Call in the JDBC stored procedures in SQL Server when the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement does not return a result set . at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLSer

  • Detailed two of SQL Server stored procedure: sp_MSforeachtable / sp_MSforeachdb 2010-10-18

    1 Introduction: As a DBA will often need to check all of the database or the user table, for example: check the capacity of all the database; see all user tables in the specified database size, number of records ... all the tables, we generally deal

  • Learning SQL SERVER stored procedure - one of the stored procedure syntax knowledge 2010-12-04

    CREATE PROCEDURE Create stored procedures, stored procedure is saved can accept and return user-supplied parameters of Transact-SQL statement collection. You can create a process for permanent use, or for temporary use in a session (local temporary p

  • jdbc sql server stored procedure is called when "the statement does not return a result set" solution 2010-12-30

    In JDBC call stored procedure in SQL Server when the following exceptions: com.microsoft.sqlserver.jdbc.SQLServerException: The statement does not return a result set . at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerE

  • About sql server stored procedure and use 2011-10-12

    Syntax: DECLARE { {@ Local_variable data_type} } [,... N] For example: declare @ ID int - declare a variable named @ ID, type int-type Three in the SQL Server window, print out the value of the variable Syntax: PRINT 'any ASCII text' | @ local_variab

  • sql server stored procedure example 2011-01-11

    create procedure index_proc @postAmount int output, @replyAmount int output, @todayAmount int output, @userAmount int output, @newUser varchar(20) output as begin declare @todayPost varchar(10); declare @todayReply varchar(10); select * from module;