sqlserver存储过程入门例子加讲解.doc

例1.--创建一个返回结果集的存储过程prSearchcontentshow --执行prSearchcontentshow execute prSearchcontentshow 例2.--创建一个要求输入一个输入参数的存储过程seachConShow --执行seachConShow存储过程 execute seachconshow 1 例3.--创建一个要求输入两个输入参数的存储过程searchConShow --运行带参数的存储过程 execute searchConShow 1,divid execute searchconshow 2,divid1 例4.--创建有返回值的存储过程 --执行getid这个带返回值的存储过程 Declare @topmenu int --声明一个变量用来接收执行过存储过程后的返回值execute getid 1,divid,@topmenu output select @topmenu as topmenuid--as 后是给返回的列值起的一个别名 例4.1.--修改已经创建过的存储过程 例4.2.--修改已经创建过的存储过程 select * from contentshow --执行getdivname存储过程declare @id int execute @id = getdivname 1,divid select @id as id --存储过程输入参数添加默认值 create procedure prGetUsers @id varchar(10 = %, @username varchar(10 = % AS Select * from users where id = @id and Username = @username --修改prGetUsers存储过程 alter procedure prGetUsers @id int = 2, @username varchar(10 = % AS Select * from users where id = @id and Username = @username execute prGetUsers 2,admin execute prGetUsers 1,admin --创建 create procedure prGetUser @id int = %, @username varchar(10 = % AS Select * from users where id like @id and Username like @username --执行 execute prGetUser 2,a% --修改 alter procedure prGetUser @id int = 2, @username varchar(10 = % AS Select * from users where id like @id and Username like @username execute prGetUser select * from users where username like %a% select * from users where username like a% import java.sql.DriverManager; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.Connection; //调用存储过程要调用的包 import java.sql.CallableStatement; import java.sql.Types; import .xiangxinli.www.db.Db_DB; import .xiangxinli.www.db.Db_JDBC; /** *用JAVA调用存储过程代码 * *@author Administrator * */ public class procedure { /** *得到数据库连接 *@return Connection */ public Connection getConn( { Connection conn = null; Db_JDBC jdbc = new Db_JDBC(; try { Class.forName(jdbc.sDBDriver; try { conn = DriverManager.getConnection(jdbc.connStr,jdbc.username,jdbc.password ; } catch (SQLException e { // TODO Auto-generated cat