当前位置：首页 > 范文大全 > 实用文>创建作业的通用存储过程数据库教程

创建作业的通用存储过程数据库教程

2025-02-18 08:09:59 收藏本文下载本文

“YANG洋$~”通过精心收集，向本站投稿了9篇创建作业的通用存储过程数据库教程，下面小编为大家带来整理后的创建作业的通用存储过程数据库教程，希望能帮助大家!

创建作业的通用存储过程数据库教程

篇1：创建作业的通用存储过程数据库教程

创建|存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_JobSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_JobSet]GO

/*--定时调用存储过程

创建一个在指定时间,调用指定存储过程的作业作业执行完成后会自动删除

--邹建 .07--*/

/*--调用示例

exec p_JobSet 'master.dbo.xp_cmdshell','2004-1-1 10:30'--*/create proc p_JobSet@prorcname sysname, --要调用定时调用的存储过程名,如果不在当前库中,则用:库名.所有者名.存储过程名@job_date datetime --存储过程的执行时间(包括时间信息)asdeclare @dbname sysname,@jobname sysname ,@date int,@time int

select @jobname='定时发送作业_'+cast(newid as varchar(36)) ,@date=convert(varchar,@job_date,112) ,@time=replace(convert(varchar,@job_date,108),':','')

if exists(select 1 from msdb..sysjobs where name=@jobname) exec msdb..sp_delete_job @job_name=@jobname

--创建作业exec msdb..sp_add_job @job_name=@jobname,@delete_level=1

--创建作业步骤declare @sql varchar(800)select @sql='exec '+@prorcname ,@dbname=db_name()

exec msdb..sp_add_jobstep @job_name=@jobname, @step_name = '发送处理步骤', @subsystem = 'TSQL', @database_name=@dbname, @command = @sql, @retry_attempts = 5, --重试次数 @retry_interval = 5 --重试间隔

--创建调度EXEC msdb..sp_add_jobschedule @job_name = @jobname, @name = '时间安排', @enabled = 1, @freq_type = 1, @active_start_date = @date, @active_start_time = @time

-- 添加目标服务器EXEC msdb.dbo.sp_add_jobserver @job_name = @jobname , @server_name = N'(local)' go

篇2：动态创建SQL Server数据库、表、存储过程数据库教程

server|创建|存储过程|动态|数据|数据库

下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法，所要增加的控件如下：

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Inherits System.Windows.Forms.Form

Private ConnectionString As String = “Data Source=.;Initial Catalog=;User Id=sa;Password=;”

Private reader As SqlDataReader = Nothing

Private conn As SqlConnection = Nothing

Private cmd As SqlCommand = Nothing

Private AlterTableBtn As System.Windows.Forms.Button

Private sql As String = Nothing

Private CreateOthersBtn As System.Windows.Forms.Button

#Region “ Windows 窗体设计器生成的代码 ”

'窗体重写处置以清理组件列表。

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

MyBase.Dispose(disposing)

End Sub

Public Sub New()

MyBase.New()

InitializeComponent()

End Sub

Private components As System.ComponentModel.IContainer

Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

Friend WithEvents CreateDBBtn As System.Windows.Forms.Button

Friend WithEvents CreateTableBtn As System.Windows.Forms.Button

Friend WithEvents CreateSPBtn As System.Windows.Forms.Button

Friend WithEvents CreateViewBtn As System.Windows.Forms.Button

Friend WithEvents btnAlterTable As System.Windows.Forms.Button

Friend WithEvents btnCreateOthers As System.Windows.Forms.Button

Friend WithEvents btnDropTable As System.Windows.Forms.Button

Friend WithEvents btnViewData As System.Windows.Forms.Button

Friend WithEvents btnViewSP As System.Windows.Forms.Button

Friend WithEvents btnViewView As System.Windows.Forms.Button

Private Sub InitializeComponent()

Me.CreateDBBtn = New System.Windows.Forms.Button()

Me.CreateTableBtn = New System.Windows.Forms.Button()

Me.CreateSPBtn = New System.Windows.Forms.Button()

Me.CreateViewBtn = New System.Windows.Forms.Button()

Me.btnAlterTable = New System.Windows.Forms.Button()

Me.btnCreateOthers = New System.Windows.Forms.Button()

Me.btnDropTable = New System.Windows.Forms.Button()

Me.btnViewData = New System.Windows.Forms.Button()

Me.btnViewSP = New System.Windows.Forms.Button()

Me.btnViewView = New System.Windows.Forms.Button()

Me.DataGrid1 = New System.Windows.Forms.DataGrid()

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'CreateDBBtn

Me.CreateDBBtn.Location = New System.Drawing.Point(19, 9)

Me.CreateDBBtn.Name = “CreateDBBtn”

Me.CreateDBBtn.Size = New System.Drawing.Size(104, 23)

Me.CreateDBBtn.TabIndex = 0

Me.CreateDBBtn.Text = “创建数据库”

'CreateTableBtn

Me.CreateTableBtn.Location = New System.Drawing.Point(139, 9)

Me.CreateTableBtn.Name = “CreateTableBtn”

Me.CreateTableBtn.TabIndex = 1

Me.CreateTableBtn.Text = “创建表”

'CreateSPBtn

Me.CreateSPBtn.Location = New System.Drawing.Point(230, 9)

Me.CreateSPBtn.Name = “CreateSPBtn”

Me.CreateSPBtn.Size = New System.Drawing.Size(104, 23)

Me.CreateSPBtn.TabIndex = 2

Me.CreateSPBtn.Text = “创建存储过程”

'CreateViewBtn

Me.CreateViewBtn.Location = New System.Drawing.Point(350, 9)

Me.CreateViewBtn.Name = “CreateViewBtn”

Me.CreateViewBtn.TabIndex = 3

Me.CreateViewBtn.Text = “创建视图”

'btnAlterTable

Me.btnAlterTable.Location = New System.Drawing.Point(441, 9)

Me.btnAlterTable.Name = “btnAlterTable”

Me.btnAlterTable.TabIndex = 4

Me.btnAlterTable.Text = “修改表”

'btnCreateOthers

Me.btnCreateOthers.Location = New System.Drawing.Point(17, 43)

Me.btnCreateOthers.Name = “btnCreateOthers”

Me.btnCreateOthers.Size = New System.Drawing.Size(104, 23)

Me.btnCreateOthers.TabIndex = 5

Me.btnCreateOthers.Text = “创建规则和索引”

'btnDropTable

Me.btnDropTable.Location = New System.Drawing.Point(138, 43)

Me.btnDropTable.Name = “btnDropTable”

Me.btnDropTable.TabIndex = 6

Me.btnDropTable.Text = “删除表”

'btnViewData

Me.btnViewData.Location = New System.Drawing.Point(351, 43)

Me.btnViewData.Name = “btnViewData”

Me.btnViewData.TabIndex = 7

Me.btnViewData.Text = “查看数据”

'btnViewSP

Me.btnViewSP.Location = New System.Drawing.Point(230, 43)

Me.btnViewSP.Name = “btnViewSP”

Me.btnViewSP.Size = New System.Drawing.Size(104, 23)

Me.btnViewSP.TabIndex = 8

Me.btnViewSP.Text = “查看存储过程”

'btnViewView

Me.btnViewView.Location = New System.Drawing.Point(443, 43)

Me.btnViewView.Name = “btnViewView”

Me.btnViewView.TabIndex = 9

Me.btnViewView.Text = “查看视图”

'DataGrid1

Me.DataGrid1.DataMember = “”

Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText

Me.DataGrid1.Location = New System.Drawing.Point(20, 76)

Me.DataGrid1.Name = “DataGrid1”

Me.DataGrid1.Size = New System.Drawing.Size(500, 183)

Me.DataGrid1.TabIndex = 10

'Form1

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(538, 281)

Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.btnViewView, _

Me.btnViewSP, Me.btnViewData, Me.btnDropTable, Me.btnCreateOthers, Me.btnAlterTable, _

Me.CreateViewBtn, Me.CreateSPBtn, Me.CreateTableBtn, Me.CreateDBBtn})

Me.Name = “Form1”

Me.Text = “动态创建SQL Server数据库、表、存储过程等架构信息”

CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()

Me.ResumeLayout(False)

End Sub

#End Region

' 创建数据库

Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateDBBtn.Click

conn = New SqlConnection(ConnectionString)

' 打开连接

If conn.State ConnectionState.Open Then

conn.Open()

End If

'MyDataBase为数据库名称

Dim sql As String = “CREATE DATABASE MyDataBase ON PRIMARY (Name=MyDataBase_data, filename = ” + _

“'D:\MyDataBase.mdf', size=3,” + “maxsize=5, filegrowth=10%) log on” + “(name=MyDataBase_log, ” + _

“filename='D:\MyDataBase.ldf',size=3,” + “maxsize=20,filegrowth=1)”

cmd = New SqlCommand(sql, conn)

Try

cmd.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message.ToString())

End Try

End Sub

'创建表

Private Sub CreateTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateTableBtn.Click

conn = New SqlConnection(ConnectionString)

' 打开连接

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

sql = “CREATE TABLE myTable” + “(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY,” + _

“myName CHAR(50) NOT Null, myAddress CHAR(255), myValues FLOAT)”

cmd = New SqlCommand(sql, conn)

Try

cmd.ExecuteNonQuery()

' 添加纪录

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1001, _'【孟宪会之精彩世界】之一', 'xml.sz.luohuedu.net/', 100 ) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1002, '【孟宪会之精彩世界】之二', 'www.erp800.com/net_lover/', 99) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1003, '【孟宪会之精彩世界】之三', 'xml.sz.luohuedu.net/', 99) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = “INSERT INTO myTable(myId, myName, myAddress, myValues) ” + _

“VALUES (1004, '【孟宪会之精彩世界】之四', 'www.erp800.com/net_lover/', 100) ”

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message.ToString())

End Try

End Sub

'创建存储过程

Private Sub CreateSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateSPBtn.Click

sql = “CREATE PROCEDURE myProc AS” + “ SELECT myName, myAddress FROM myTable GO”

ExecuteSQLStmt(sql)

End Sub

'创建视图

Private Sub CreateViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles CreateViewBtn.Click

sql = “CREATE VIEW myView AS SELECT myName FROM myTable”

ExecuteSQLStmt(sql)

End Sub

'修改表

Private Sub btnAlterTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnAlterTable.Click

sql = “ALTER TABLE MyTable ADD newCol datetime NOT NULL DEFAULT (getdate())”

ExecuteSQLStmt(sql)

End Sub

'创建规则和索引

Private Sub btnCreateOthers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnCreateOthers.Click

sql = “CREATE UNIQUE INDEX ” + “myIdx ON myTable(myName)”

ExecuteSQLStmt(sql)

sql = “CREATE RULE myRule ” + “AS @myValues >= 90 AND @myValues < 9999”

ExecuteSQLStmt(sql)

End Sub

'删除表

Private Sub btnDropTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnDropTable.Click

Dim sql As String = “DROP TABLE MyTable”

ExecuteSQLStmt(sql)

End Sub

'浏览表数据

Private Sub btnViewData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnViewData.Click

conn = New SqlConnection(ConnectionString)

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

Dim da As New SqlDataAdapter(“SELECT * FROM myTable”, conn)

Dim ds As New DataSet(“myTable”)

da.Fill(ds, “myTable”)

DataGrid1.DataSource = ds.Tables(“myTable”).DefaultView

End Sub

'浏览存储过程

Private Sub btnViewSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnViewSP.Click

conn = New SqlConnection(ConnectionString)

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

Dim da As New SqlDataAdapter(“myProc”, conn)

Dim ds As New DataSet(“SP”)

da.Fill(ds, “SP”)

DataGrid1.DataSource = ds.DefaultViewManager

End Sub

'浏览视图

Private Sub btnViewView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles btnViewView.Click

conn = New SqlConnection(ConnectionString)

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

Dim da As New SqlDataAdapter(“SELECT * FROM myView”, conn)

Dim ds As New DataSet()

da.Fill(ds)

DataGrid1.DataSource = ds.DefaultViewManager

End Sub

Private Sub ExecuteSQLStmt(ByVal sql As String)

conn = New SqlConnection(ConnectionString)

' 打开连接

If conn.State = ConnectionState.Open Then

conn.Close()

End If

ConnectionString = “Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;”

conn.ConnectionString = ConnectionString

conn.Open()

cmd = New SqlCommand(sql, conn)

Try

cmd.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message.ToString())

End Try

End Sub

End Class

篇3：管理存储过程数据库教程

12.3.1 查看存储过程

存储过程被创建以后，它的名字存储在系统表sysobjects 中；它的源代码存放在系统表syscomments 中，可以通过MS SQL Server 提供的系统存储过程来查看关于用户创建的存储过程信息。

1通过Enterprise Manager 管理工具同样可以查看存储过程的源代码

其操作如下：

（1）启动Enterprise Manager，登录到要使用的服务器。

（2）选择要创建存储过程的数据库，在左窗格中单击Stored Procedure 文件夹，此时在右窗格中显示该数据库的所有存储过程。

（3）在右窗格中，右击要查看源代码的存储过程，在弹出的菜单中选择Properties选项，此时便可看到存储过程的源代码。如图12-4 所示：

2 使用sp_helptext 存储过程查看存储过程的源代码

其语法格式如下：

sp_helptext 存储过程名称

例如要查看数据库pubs 是否是存储过程reptq1 的源代码，则执行sp_helptext reptq1。

注意：如果在创建存储过程时使用了WITH ENCRYPTION选项，那么无论是使用Enterprise Manager还是系统存储过程sp_helptext都无法查看到存储过程的源代码。

12.3.2 重新命名存储过程

修改存储过程的名字使用系统存储过程sp_rename。其命令格式为：

sp_rename 原存储过程名, 新存储过程名

例12-4 ：将存储过程reptq1 修改为newproc 其语句为：

sp_rename reptq1, newproc

另外，通过Enterprise Manager 也可修改存储过程的名字，其操作过程与WINDOWS 下修改文件名字的操作类似。即首先选中需修改名字的存储过程，然后右击鼠标，在弹出菜单中选取rename 选项，最后输入新存储过程的名字。

12.3.3 删除存储过程

删除存储过程使用drop 命令，drop 命令可将一个或多个存储过程或者存储过程组从

当前数据库中删除。其语法规则为：

DROP PROCEDURE {procedure}} [,…n]

例12-5：如将存储过程reptq1 从数据库中删除，则执行：

drop procedure reptq1

12.3.4 执行存储过程

执行已创建的存储过程使用EXECUTE 命令，其语法如下：

[EXECUTE]

{[@return_statur=]

{procedure_name[;number] | @procedure_name_var}

[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT] [,…n]

[WITH RECOMPILE]

各参数的含义如下：

@return_status

是可选的整型变量，用来存储存储过程向调用者返回的值，

@procedure_name_var

是一变量名用来代表存储过程的名字。

其它参数据和保留字的含义与CREATE PROCEDURE 中介绍的一样。

例12-6：该存储过程被用来将两个字符串连接成一个字符串，并将结果返回。创建存储过程：

12.3.5 修改存储过程

修改以前用CREATE PROCEDURE 命令创建的存储过程，并且不改变权限的授予情况以及不影响任何其它的独立的存储过程或触发器常使用ALTER PROCEDURE 命令。其语法规则是：

ALTER PROC[EDURE] procedure_name [;number]

[ {@parameter data_type } [VARYING] [= default] [OUTPUT]] [,...n]

[WITH

{RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION}]

[FOR REPLICATION]

sql_statement [...n]

其中各参数和保留字的具体含义请参看CREATE PROCEDURE 命令。

下面将举一个例子使读者对该命令有更为深刻的理解。

将该段代码输入到SQL Server Query Analyzer 窗口中运行后结果为：

注意：由于在ALERT PROCEDURE中使用了WITH ENCTYPTION保留字，所以在查看修改后的存储过程源代码时看到是一些代码。

篇4：通用存储过程的编写数据库教程

存储过程

通用存储过程的编写

对数据库的操作基本上就四种：Insert、Update、Delete和Select，而Update和Insert两种操作又可以作简单的合并，这样下来，基本上一个数据表对应三个存储过程便可以完成绝大多数的数据库操作，存储过程命名规则：Operate_TableName。比如表Order_Info对应三个存储过程：AddEdit_Order_Info、Delete_Order_Info、Search_Order_Info，下面先列出相关代码，然后作总体分析。

一、AddEdit_Order_Info

/*************************************************************

** Name : AddEdit_Order_Info

** Creater : PPCoder Designed By PPCode Studio(PPTech.Net)

** Create Date : -9-6 8:30:17

** Modifer : Rexsp

** Modify Date : 2004-9-6 8:30:17

** Description : AddEdit information for Order_Info

**************************************************************/

ALTER PROCEDURE dbo.AddEdit_Order_Info

(

@OrderStateID Int = -1,

@OrderStateID_Min Int = -1,

@OrderStateID_Max Int = -1,

@OrderUserID Int = -1,

@OrderUserID_Min Int = -1,

@OrderUserID_Max Int = -1,

@OrderID Int = -1,

@OrderID_Min Int = -1,

@OrderID_Max Int = -1,

@ProductID Int = -1,

@ProductID_Min Int = -1,

@ProductID_Max Int = -1,

@CustomizeID Int = -1,

@CustomizeID_Min Int = -1,

@CustomizeID_Max Int = -1,

@OutID INT = 0 OUTPUT

)

IF @OrderID=-1

BEGIN

INSERT INTO [Order_Info] (

[OrderStateID],

[OrderUserID],

[ProductID],

[CustomizeID]

)

VALUES(

@OrderStateID,

@OrderUserID,

@ProductID,

@CustomizeID

)

Set @OutID = @@IDENTITY

END

ELSE

BEGIN

DECLARE @strSQL NVARCHAR(1000)

SET @strSQL = 'UPDATE [Order_Info] SET @tmpOrderID = @tmpOrderID'

IF @OrderStateID -1

BEGIN

SET @strSQL = @strSQL + ', [OrderStateID] = @tmpOrderStateID'

END

IF @OrderUserID -1

BEGIN

SET @strSQL = @strSQL + ', [OrderUserID] = @tmpOrderUserID'

END

IF @ProductID -1

BEGIN

SET @strSQL = @strSQL + ', [ProductID] = @tmpProductID'

END

IF @CustomizeID -1

BEGIN

SET @strSQL = @strSQL + ', [CustomizeID] = @tmpCustomizeID'

END

SET @strSQL = @strSQL + ' WHERE [OrderID] = @tmpOrderID'

BEGIN TRAN

EXECUTE sp_executesql @strSQL, N'

@tmpOrderStateID INT,

@tmpOrderUserID INT,

@tmpOrderID INT,

@tmpProductID INT,

@tmpCustomizeID INT',

@tmpOrderStateID=@OrderStateID,

@tmpOrderUserID=@OrderUserID,

@tmpOrderID=@OrderID,

@tmpProductID=@ProductID,

@tmpCustomizeID=@CustomizeID

Set @OutID = @OrderID

IF @@error!=0

BEGIN

ROLLBACK

END

ELSE

BEGIN

COMMIT

END

RETURN

二、Delete_Order_Info

/*************************************************************

** Name : Delete_Order_Info

** Creater : PPCoder Designed By PPCode Studio(PPTech.Net)

** Create Date : 2004-9-6 8:30:17

** Modifer : Rexsp

** Modify Date : 2004-9-6 8:30:17

** Description : Delete information for Order_Info

**************************************************************/

ALTER PROCEDURE dbo.Delete_Order_Info

(

@OrderStateID Int = -1,

@OrderStateID_Min Int = -1,

@OrderStateID_Max Int = -1,

@OrderUserID Int = -1,

@OrderUserID_Min Int = -1,

@OrderUserID_Max Int = -1,

@OrderID Int = -1,

@OrderID_Min Int = -1,

@OrderID_Max Int = -1,

@ProductID Int = -1,

@ProductID_Min Int = -1,

@ProductID_Max Int = -1,

@CustomizeID Int = -1,

@CustomizeID_Min Int = -1,

@CustomizeID_Max Int = -1,

@OutID INT = 0 OUTPUT

)

DECLARE @strSQL NVARCHAR(1000)

SET @strSQL = 'DELETE FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '

IF @OrderStateID-1

BEGIN

SET @strSQL = @strSQL + ' AND rderStateID = @tmpOrderStateID'

END

IF @OrderStateID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderStateID_Min = @tmpOrderStateID_Min'

END

IF @OrderStateID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderStateID_Max = @tmpOrderStateID_Max'

END

IF @OrderUserID-1

BEGIN

SET @strSQL = @strSQL + ' AND rderUserID = @tmpOrderUserID'

END

IF @OrderUserID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderUserID_Min = @tmpOrderUserID_Min'

END

IF @OrderUserID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderUserID_Max = @tmpOrderUserID_Max'

END

IF @OrderID-1

BEGIN

SET @strSQL = @strSQL + ' AND rderID = @tmpOrderID'

END

IF @OrderID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderID_Min = @tmpOrderID_Min'

END

IF @OrderID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderID_Max = @tmpOrderID_Max'

END

IF @ProductID-1

BEGIN

SET @strSQL = @strSQL + ' AND ProductID = @tmpProductID'

END

IF @ProductID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND ProductID_Min = @tmpProductID_Min'

END

IF @ProductID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND ProductID_Max = @tmpProductID_Max'

END

IF @CustomizeID-1

BEGIN

SET @strSQL = @strSQL + ' AND CustomizeID = @tmpCustomizeID'

END

IF @CustomizeID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND CustomizeID_Min = @tmpCustomizeID_Min'

END

IF @CustomizeID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND CustomizeID_Max = @tmpCustomizeID_Max'

END

BEGIN TRAN

EXECUTE sp_executesql @strSQL, N'

@tmpOrderStateID INT,

@tmpOrderUserID INT,

@tmpOrderID INT,

@tmpProductID INT,

@tmpCustomizeID INT',

@tmpOrderStateID=@OrderStateID,

@tmpOrderUserID=@OrderUserID,

@tmpOrderID=@OrderID,

@tmpProductID=@ProductID,

@tmpCustomizeID=@CustomizeID

Set @OutID = @OrderID

IF @@error!=0

BEGIN

ROLLBACK

END

ELSE

BEGIN

COMMIT

END

RETURN

三、Search_Order_Info

/*************************************************************

** Name : Search_Order_Info

** Creater : PPCoder Designed By PPCode Studio(PPTech.Net)

** Create Date : 2004-9-6 8:30:17

** Modifer : Rexsp

** Modify Date : 2004-9-6 8:30:17

** Description : Search information for Order_Info

**************************************************************/

ALTER PROCEDURE dbo.Search_Order_Info

(

@OrderStateID Int = -1,

@OrderStateID_Min Int = -1,

@OrderStateID_Max Int = -1,

@OrderUserID Int = -1,

@OrderUserID_Min Int = -1,

@OrderUserID_Max Int = -1,

@OrderID Int = -1,

@OrderID_Min Int = -1,

@OrderID_Max Int = -1,

@ProductID Int = -1,

@ProductID_Min Int = -1,

@ProductID_Max Int = -1,

@CustomizeID Int = -1,

@CustomizeID_Min Int = -1,

@CustomizeID_Max Int = -1,

@ReturnCount INT=-1,

@OutID INT = 0 OUTPUT

)

DECLARE @strSQL NVARCHAR(1000)

IF @ReturnCount-1

BEGIN

SET @strSQL = 'SELECT TOP '+@ReturnCount+' * FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '

END

ELSE

BEGIN

SET @strSQL = 'SELECT * FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '

END

IF @OrderStateID-1

BEGIN

SET @strSQL = @strSQL + ' AND rderStateID = @tmpOrderStateID'

END

IF @OrderStateID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderStateID_Min = @tmpOrderStateID_Min'

END

IF @OrderStateID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderStateID_Max = @tmpOrderStateID_Max'

END

IF @OrderUserID-1

BEGIN

SET @strSQL = @strSQL + ' AND rderUserID = @tmpOrderUserID'

END

IF @OrderUserID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderUserID_Min = @tmpOrderUserID_Min'

END

IF @OrderUserID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderUserID_Max = @tmpOrderUserID_Max'

END

IF @OrderID-1

BEGIN

SET @strSQL = @strSQL + ' AND rderID = @tmpOrderID'

END

IF @OrderID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderID_Min = @tmpOrderID_Min'

END

IF @OrderID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND OrderID_Max = @tmpOrderID_Max'

END

IF @ProductID-1

BEGIN

SET @strSQL = @strSQL + ' AND ProductID = @tmpProductID'

END

IF @ProductID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND ProductID_Min = @tmpProductID_Min'

END

IF @ProductID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND ProductID_Max = @tmpProductID_Max'

END

IF @CustomizeID-1

BEGIN

SET @strSQL = @strSQL + ' AND CustomizeID = @tmpCustomizeID'

END

IF @CustomizeID_Min-1

BEGIN

SET @strSQL = @strSQL + ' AND CustomizeID_Min = @tmpCustomizeID_Min'

END

IF @CustomizeID_Max-1

BEGIN

SET @strSQL = @strSQL + ' AND CustomizeID_Max = @tmpCustomizeID_Max'

END

BEGIN TRAN

EXECUTE sp_executesql @strSQL, N'

@tmpOrderStateID INT,

@tmpOrderUserID INT,

@tmpOrderID INT,

@tmpProductID INT,

@tmpCustomizeID INT',

@tmpOrderStateID=@OrderStateID,

@tmpOrderUserID=@OrderUserID,

@tmpOrderID=@OrderID,

@tmpProductID=@ProductID,

@tmpCustomizeID=@CustomizeID

Set @OutID = @OrderID

IF @@error!=0

BEGIN

ROLLBACK

END

ELSE

BEGIN

COMMIT

END

分析：

1、三个存储过程的入参基本上相同，只有Search_Order_Info多了一个＠ReturnCount用来控制搜索信息的条数的，

入参很有特点：与数据表字段的扩展对应。扩展方式有三种：数字型和日期型扩展出“极小”和“极大”两个属性，例如数字型的OrderStateID对应的参数有三个@OrderStateID、@OrderStateID_Min 、@OrderStateID_Max ，时间型的AddTime对应@AddTime、@AddTime_Rof、@AddTime_Eof ；如果是字符型的，则会扩展出一个用来进行模糊搜索的属性，例如Title对应@Title、@Title_Like。之所以这样设计，是为了组合出更具适应性的条件语句。三个存储过程都有一个出参，就是表的唯一标识ID。这个主要在“添加和更新”操作中使用。当然搜索的时候也可以当唯一键返回。这个唯一标识ID也是来判断是Insert或Update的标识。

2、入参都有赋初值，然后动态构建Sql语句的时候，会判断各入参是否等于初值，如果不等于表示是外面传进来的传，便参与Sql语句的构建。这种灵活性是程序适应性的保证。这样，我们就可以在程序员通过控制是否给入参传值来判断是否要进行某一栏位进行更新或是否要把某一栏位的信息参与条件语句的构成。

3、用系统存储过程sp_executesql来执行Sql语句，完全数据库操作。用系统存储过程来执行sql语句有一个好处，就是可以实现特殊字符的自动转义。

4、三个存储过程都有统一的构建规律，所以可以使用自动化工具依据表结构直接生成。

篇5：一问一答：存储过程经典问题数据库教程

存储过程|问题

只涉及到一个表：xkb_treeNode

表结构是这样：

node_id int //节点id

parentNode_id int //父节点id

node_text varchar //节点内容

isModule bit //是否叶子节点

现在保存的数据有：

node_id parentNode_id node_text isModule

1 -1 语言与文学 0

2 -1 数学 0

3 -1 技术 0

4 1 语文 0

5 1 外语 0

6 5 英语 0

7 6 初中英语 0

8 7 特斯塔 1

9 4 测定是2 1

10 2 测试3 1

现在问题是：

能否通过做一个存储过程，

根据表中的isModule字段的取值（取值为1的表示最终叶子结点），

比如“特斯塔”为叶子节点，层层向上递进找到”特斯塔“的祖先节点：

特斯塔-〉初中英语-〉英语-〉外语-〉语言与文学

即通过”特斯塔“找到”语言与文学“来

最终返回的形态为：

叶子节点id 父节点id 节点名称祖先节点名称祖先节点id

8 7 特斯塔语言与文学 1

9 4 测定是2 语言与文学 1

10 2 测试3 数学 2

/////////////////////////////////////////////////////////////////////////

正确答案：

--生成测试数据

create table xkb_treeNode(

node_id int,

parentNode_id int,

node_textvarchar(10),

isModulebit)

insert into xkb_treeNode select 1 ,-1,'语言与文学',0

insert into xkb_treeNode select 2 ,-1,'数学',0

insert into xkb_treeNode select 3 ,-1,'技术',0

insert into xkb_treeNode select 4 , 1,'语文',0

insert into xkb_treeNode select 5 , 1,'外语',0

insert into xkb_treeNode select 6 , 5,'英语',0

insert into xkb_treeNode select 7 , 6,'初中英语',0

insert into xkb_treeNode select 8 , 7,'特斯塔' ,1

insert into xkb_treeNode select 9 , 4,'测定是2',1

insert into xkb_treeNode select 10 , 2,'测试3',1

--创建存储过程

create procedure sp_test

begin

select

a.node_id,

a.parentNode_id,

a.node_text,

b.node_id as ancestor_id ,

b.node_text as ancestor_text

into

from

xkb_treeNode a,xkb_treeNode b

where

a.parentNode_id = b.node_id and a.isModule = 1

while(exists(select 1 from xkb_treeNode a,#t b where a.node_id=ancestor_id and a.parentNode_id != -1))

begin

update #t

set

ancestor_id = b.p_id,

ancestor_text = b.p_text

from

#t a,

(select

c.node_id,

d.node_id as p_id,

d.node_text as p_text

from

xkb_treeNode c,xkb_treeNode d

where

c.parentNode_id = d.node_id) b

where

a.ancestor_id = b.node_id

end

select * from #t order by node_id

end

--执行存储过程，结果楼主自己看

exec sp_test

篇6：解密SQL的存储过程数据库教程

存储过程|解密

内容来自互联网，作者不明

drop procedure sp_decrypt

create PROCEDURE sp_decrypt (@objectName varchar(50))

begin

begin transaction --add by playyuer

declare @objectname1 varchar(100)

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 , @t bigint

declare @m int,@n int,@q int

set @m=(SELECT max(colid) FROM syscomments WHERE id = object_id(@objectName))

set @n=1

--get encrypted data

create table #temp(colid int,ctext varbinary(8000))

insert #temp SELECT colid,ctext FROM syscomments WHERE id = object_id(@objectName)

set @sql1='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '

--set @sql1='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '

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)

while @n<=@m

begin

SET @OrigSpText1=(SELECT ctext FROM #temp WHERE colid=@n)

set @objectname1=@objectName+'_t'

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n)

if @n=1

begin

SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '--

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

--reverse encryption (XOR original+bogus+bogus encrypted)

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

--drop original SP

--EXECUTE ('drop PROCEDURE '+ @objectName)

--remove encryption

--preserve case

SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')

SET @resultsp=REPLACE((@resultsp),'With Encryption', '')

SET @resultsp=REPLACE((@resultsp),'with encryption', '')

IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0

SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')

--replace Stored procedure without enryption

print @resultsp

--execute( @resultsp)

set @n=@n+1

end

drop table #temp

end

rollback transaction --add by playyuer

篇7：关于未能找到存储过程 master..xpjdbcopen数据库教程

存储过程

将安装的 Microsoft SQL Server Driver for JDBC 目录下的文件 sqljdbc.dll 从目录 C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC QLServer JTA\ 拷贝到 SQL Server 的 “binn” 目录下 (看起来像这样 C:\Program Files\Microsoft SQL Server\MSSQL\Binn). 然后用查询分析器打开 instjdbc.sql 脚本，这个脚本在目录 C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC QLServer JTA\. 运行这个脚本将安装一些扩展的存储过程，这样我们就可以在WebLogic中引用文件 sqljdbc.dll了.

篇8：购物车的存储过程学习数据库教程

存储过程|购物车

从来没有接触过存储过程，为了这个项目写了几个存储过程，就当是学习了，呵呵，这个购物车的存储过程中使用了Table(表)，Cursor(游标）变量。

要求如下：

一个商品表[Ware]，里面包含有的字段有ID,MarketPrice,MemberPrice,VipPrice等等，还一个会员购物车[MemberCar]数据表。包括的有ID,MemberID,WareID,WareNum等等。现要求是能取得购物车表里的所有数据和MarketPrice*WareNum（所有商品和）的总价钱，即有N件商品就获取这N件商品的总价钱而不是单一的价钱。本来在客户端里直接根据购物车[MemberCar]表的数据读取商品表[Ware]里的数据，再用变量累加即可的。但问题却是，购物车里的所有商品显示使用了分页显示。所以累加时就出问题，如果为了累加这些价钱而在分页循环显示后再加一个循环所有记录来累加价钱数据，这样不是不行，但效率就可想而知了！！！

现一个解决方法就是，重新构造一个购物车表的镜像，但却多了商品的几个价钱总数字段。即MarketPrice*WareNum(单一商品)。从而读取购物车时不是读取原来购物车数据表，而是读取这个镜像数据表，并在读取时累加这些总价钱。

存储过程代码如下：

----#####################################################################################

----# 过程作用：返回用户购物车里的所有数据，并包含统计了商品的价钱 (价钱=商品表的价格*购物车表里的数量)

----# 作者：死在水中的鱼

----# 日期：10月15日

----#####################################################################################

CREATE PROCEDURE MemberCar_Info @MemberID INT AS

--使用此句才能保持客户端的数据

SET NOCOUNT ON

DECLARE @Car_ID int,@Car_WareID int,@Car_WareNum smallINt

DECLARE @Car_TotalMemberPrice Numeric(8,2),@Car_TotalMarketPrice Numeric(8,2),@Car_TotalVipPrice Numeric(8,2)

--定义一个查询MemberCar表的游标变量，

获取数据

DECLARE Car_Cursor Cursor For

Select ID,WareID,WareNum From [MemberCar] Where MemberID=@MemberID And IsPay=0

--建立一个临时表变量

DECLARE @Car_Table Table (ID INT,

WareID INT,WareNum SmallINT,MarketPrice Numeric(8,2),MemberPrice Numeric(8,2),VipPrice Numeric(8,2))

--打开MemberCar表提取数据到临时变量

OPEN Car_Cursor

FETCH NEXT FROM Car_Cursor INTO @Car_ID,@Car_WareID,@Car_WareNum

WHILE @@FETCH_STATUS = 0

BEGIN

--取得商品的总市场价、总会员价和总VIP价

Select @Car_TotalMarketPrice=MarketPrice*@Car_WareNum,@Car_TotalMemberPrice=MemberPrice*@Car_WareNum,@Car_TotalVipPrice=VipPrice*@Car_WareNum From [Ware] Where ID=@Car_WareID

--添加进临时变量表

INSERT INTO @Car_Table (ID,WareID,WareNum,MarketPrice,MemberPrice,VipPrice) Values (@Car_ID,@Car_WareID,@Car_WareNum,@Car_TotalMarketPrice,@Car_TotalMemberPrice,@Car_TotalVipPrice)

-- 没有到表尾，继续提取数据到临时变量

FETCH NEXT FROM Car_Cursor INTO @Car_ID,@Car_WareID,@Car_WareNum

END

--关闭MemberCar数据库的游标

CLOSE Car_Cursor

DEALLOCATE Car_Cursor

--返回最终数据表

SELECT * FROM @Car_Table

要求表达不是很清楚，各位原谅...