//插入应用
INSERT INTO EquipmentApply
(ApplyPlat, ApplyID1, ApplyPerson, ApplyManager, ApplyUseto,ApplyInOutTypeID,
ApplyRemark, ApplyDate, ApplyInputPerson)
SELECT 10 AS ApplyPlat, ApplyMyID AS ApplyID1, ApplyPerson ASApplyPerson,
ApplyPerson AS ApplyManager, 2 AS ApplyUseto, 2 ASApplyInOutTypeID,
ApplyReson AS ApplyRemark, ApplyDate, '33' ASApplyInputPerson
FROM Equipment_MyOfficeApply
WHERE (ApplyMyID = 2003)
使用存储过程的例子:
第一步>创建存储过程
--创建存储过程时,最好现在查询分析器中进行调试,调试好以后再创建存储过程
<wbr></wbr>
CREATE PROCEDURE [dbo].[ApplyOK] @MyapplyID int ,@InputPersonID int ,@RetIno Varchar(100) output AS
declare @NewApplyID int
declare @cgCount int
declare @appstate int
--检查状态
select @appstate =( SELECT ApplyState FROMEquipment_MyOfficeApply WHERE (ApplyMyID = @MyapplyID))
if @appstate<>8begin
if @appstate=-1 begin
select @RetIno= '该申请未曾通过审批,不符合办结条件'
return
end
else if @appstate=9 begin
select @RetIno= '该申请已经办结'
return
end
--- 其他情况
select @RetIno= '该申请不符合办结条件'
return
end
<wbr></wbr>
--检查是否有领取的物品量超过库存的情况
select @cgCount =(SELECT COUNT(*) AS cgCount FROMV_MyOfficeApplyDetail WHERE (MyApplyID = @myapplyid) AND (Remain< 0))
if @cgCount>0 begin--如果领取的物品量超过了库存量则不允许提交
select @RetIno= '领取的物品量超过了库存量则不允许办结申请'
end
else begin
--可以正常进行领取物品处理
--1>补充申请
begin transaction --开始事务
-- 执行登记操作
-- A> //插入登记申请记录
INSERT INTO EquipmentApply
(ApplyPlat, ApplyID1, ApplyPerson, ApplyManager,ApplyUseto, ApplyInOutTypeID,
ApplyRemark, ApplyDate, ApplyInputPerson)
SELECT 10 AS ApplyPlat, ApplyMyID AS ApplyID1,ApplyPerson AS ApplyPerson,
ApplyPerson AS ApplyManager, 2 AS ApplyUseto, 2 ASApplyInOutTypeID,
ApplyReson AS ApplyRemark, ApplyDate, '33' ASApplyInputPerson
FROM Equipment_MyOfficeApply
WHERE (ApplyMyID = @myapplyid)
--获得新申请的ID
SELECT @NewApplyID = @@IDENTITY
-- B> //插入申请明细记录
INSERT INTO EquitmentInOutReg
(ApplyID, EquipmentID, Number)
SELECT @NewApplyID AS ApplyID, EquipmentID,ApplyNumber AS Number
FROM EquipmentOffice_MyEquiplist
WHERE (MyApplyID = @myapplyid)
-- C> //数据进行领取物品后的数据计算
UPDATE EqupmentMain
SET Quantity = Quantity - t2.ApplyNumber
FROM EqupmentMain t1 , EquipmentOffice_MyEquiplistt2
WHERE (t2.MyApplyID = @myapplyid AND
t2.EquipmentID = t1.EquipmentID)
-- D> //更新申请的状态
UPDATE Equipment_MyOfficeApply
SET ApplyID = @NewApplyID, ApplySPUserID =@InputPersonID, ApplyState = 9, ApplyFinishDate =getdate()where
ApplyMyID = @myapplyid
<wbr></wbr>
--ROLLBACK TRANSACTION
commit TRANSACTION
select @RetIno=''
end
select @RetIno '返回值'
return
err_deal:
ROLLBACK TRANSACTION
--SELECT "发生错误" as retInfo
SELECT @RetIno = '发生错误'
GO
第二步>在查询分析器中测试创建的存储过程
declare @MyapplyID int
declare @InputPersonID int
declare @RetIno varchar(100)
set @myapplyid=2001
set @InputPersonID=33
exec ApplyOK @MyapplyID,@InputPersonID,@RetIno output
select @RetIno as '返回值'
第三步>创建调用存储过程的函数
'申请办结处理
Public Function fnApplyOK(ByVal MyapplyID AsString, ByVal InputPersonID As String) As String
Dim myConnection As SqlConnection =GetConnection()
Dim myCommand As New SqlCommand
Dim pa1 As New SqlClient.SqlParameter("@MyapplyID",SqlDbType.Int)
Dim pa2 As NewSqlClient.SqlParameter("@InputPersonID", SqlDbType.Int)
Dim pa3 As New SqlClient.SqlParameter("@RetIno",Data.SqlDbType.VarChar, 100)
pa1.Value = MyapplyID
pa2.Value = InputPersonID
pa3.Direction = ParameterDirection.Output
myCommand.CommandType =CommandType.StoredProcedure
myCommand.Connection = myConnection
myCommand.CommandText = "ApplyOK"
myCommand.Parameters.Add(pa1)
myCommand.Parameters.Add(pa2)
myCommand.Parameters.Add(pa3)
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
fnApplyOK =myCommand.Parameters("@RetIno").Value
Finally
myConnection.Close()
End Try
End Function
第四步>调用函数进行申请办结处理
'调用存储过程
Dim rtvalue As String
Dim userid As String
userid = loginvery() '验证登陆
rtvalue = fnApplyOK(lbApplyMyID.Text, userid)
If rtvalue = "" Then
'没有错误
Response.Redirect("Office_ApplyList.aspx?SP=1")
Else
'出错了,弹出出错信息
Common.fndispError(rtvalue)
End If
sqlserver 跨数据库查询
By Richard Tsuis, http://richardtsuis.cnblogs.com/ .This posting is provided "AS IS" with no warranties, and confers norights.
跨数据库服务器查询
方法1:
直接使用UNC网络地址名称指定访问路径(不推荐使用)
例如:
Where ID1=(select top 1 ID2 from table2 whereID2=@intUID)
问题:这样的查询可能使用相对指定的方式(NetBIOS或TCP/IP)去进行查询,可能不能体现出数据库引擎的传输优势。而且直接查询也可能是导致问题的原因,实际应该通过存储过程准备好数据集合后再进行查询。
方法2:
通过OPENROWSET函数完成数据库服务器联接和查询操作,准备好数据集合。
当然这种情况下所使用的数据集合是只读的,更新方面可以考虑另外的控制方法,在数据库里可以通过方法1进行,在程序里可以通过数据库连接区别操作:
(
recordID uniqueidentifier,
content nvarchar(255)
)
Insert Into @emptyContent (recordID, content)
Select local.recordID, local.content
FROMOPENROWSET('SQLOLEDB','remoteserver';'accountname';'password',
'SELECT recordID, content From database.dbo.table Where content ='''' ') AS local
Select gh.recordID, gh.content
From table gh
Where content = ''
And Not Exists(Select * From @emptyContent ec Where ec.recordID =gh.recordID )