Input是输入参数. Output是输出参数. 例如: 触发器中: CREATE TRIGGER Example1_2_UPDATE ON dbo.Example1_2 for update,update as IF UPDATE(A) OR UPDATE(B) declare @id int,@aa int,@bb int,@wzbm_A varchar(50),@@bmcbf varchar(50) set @wzbm_A='wzbm_A' set @bmcbf = 'Example1_2_BF' select @id = LS_D.ID FROM Example1_2 AS MX, INSERTED AS LS_I, DELETED AS LS_D WHERE MX.ID=LS_D.ID exec DMSCBFB @id,@wzbm_A,@bmcbf,@bb input--这就是调用的地方 ================================ 存储过程中: CREATE procedure DMSCBFB @idh int, @wxw_bmlxa varchar(50), @bmcbf varchar(50), @aa1 int output -- 这就是输出的地方 as select @parentobj2=id from sysobjects where name=@bmc and xtype='U' declare sc_cursorw1 scroll cursor for select distinct name,colid From syscolumns Where id=@parentobj2 ORDER BY colid asc --and name<>'id' open sc_cursorw1 fetch next from sc_cursorw1 into @zdmc1,@zfcd while @@FETCH_STATUS=0 begin if @zdmc1<>'' or @zdmc1<>null Exec ALTER_DROP_table @bmcbf,@zdsl,@zdsl2,@table_tem,@table_tem2,@pp output --判断增减字段or修改字段类型长度等 end Exec delete_table--删除临时表 GO
Output是输出型参数!由于存储过程除了返回查询结果以外可以返回一些值,这些值就是由这些标示为output的参数传递出去的。默认的都是input类型,就不多说了!
@EmailAddress varchar(255),
@Password binary(20),
@Address varchar(80),
@HomePhone varchar(14),
@UserName varchar(50),
@UserID int output
AS
INSERT INTO Accounts_Users(EmailAddress, Password,Address,HomePhone, UserName)
VALUES(@EmailAddress, @Password, @Address, @HomePhone, @UserName)
SET @UserID = @@IDENTITY RETURN 1
上面的存储过程创建一个新的账户,同时返回用户的编号给客户!如果你要是用Select则可以在Where里面使用参数!但是一般这种情况下不需要output因为它会返回一个结果集给客户的,我这里说的客户可以是所有调用存储过程的程序。下面则是这样的一个例子:
CREATE PROCEDURE sp_Accounts_GetUserDetailsByName
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
/* SET NOCOUNT ON */
@UserName varchar(40)
AS
SELECT * FROM Accounts_Users WHERE UserName = @UserName
使用前先要声明
如果INPUT型的用在WHERE后面作为条件,而前面SELECT 用 @参数1=参数1是什么意思?
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
/* SET NOCOUNT ON */
希望没有影响你阅读,这是使用VS.NET创建存储过程的时候自动生成的!在.NET开发环境里面这样使用这个存储过程:
public int Create(string userName,byte[] password,string emailAddress,string homePhone,string address){
try{
SqlParameter[] parameters = new SqlParameter[6];
parameters[0] = new SqlParameter("@EmailAddress",emailAddress);
parameters[1] = new SqlParameter("@Password",password);
parameters[2] = new SqlParameter("@Address",address);
parameters[3] = new SqlParameter("@HomePhone",homePhone);
parameters[4] = new SqlParameter("@UserName",userName);
parameters[5] = new SqlParameter("@UserID",SqlDbType.Int);
parameters[5].Direction = ParameterDirection.Output;
// 下面的方法请参考SqlHelper可以在微软的网站上的到相关信息!
SqlHelper.ExecuteNonQuery("sp_Accounts_CreateUser",parameters);
return (int)parameters[5].Value;
}
catch ( SqlException e ) {
if (e.Number == 2601) {
return (int)SkySword.WebModules.Accounts.ProcResultCodes.AccountAlreadyOnFile;
}
else {
throw new AppException("在执行sp_Accounts_CreateUser存储过程的时候出错!",e );
}
}
}
是给@参数1付值,一般参数的名字和字段的名字一样所以会有上面这种情况!Select @a = a from test
@a是参数!a是test里面的一个字段!
ALTER PROCEDURE GetPortalSettings
(
@PortalAlias nvarchar(50),
@TabID int,
@PortalID int OUTPUT,
@PortalName nvarchar(128) OUTPUT,
@AlwaysShowEditButton bit OUTPUT,
@TabName nvarchar (50) OUTPUT,
@TabOrder int OUTPUT,
@MobileTabName nvarchar (50) OUTPUT,
@AuthRoles nvarchar (256) OUTPUT,
@ShowMobile bit OUTPUT
)
AS/* First, get Out Params */
IF @TabID = 0
SELECT TOP 1
@PortalID = Portals.PortalID,
@PortalName = Portals.PortalName,
@AlwaysShowEditButton = Portals.AlwaysShowEditButton,
@TabID = Tabs.TabID,
@TabOrder = Tabs.TabOrder,
@TabName = Tabs.TabName,
@MobileTabName = Tabs.MobileTabName,
@AuthRoles = Tabs.AuthorizedRoles,
@ShowMobile = Tabs.ShowMobile FROM
Tabs
INNER JOIN
Portals ON Tabs.PortalID = Portals.PortalID
WHERE
PortalAlias=@PortalAlias
ORDER BY
TabOrderELSE
SELECT
@PortalID = Portals.PortalID,
@PortalName = Portals.PortalName,
@AlwaysShowEditButton = Portals.AlwaysShowEditButton,
@TabName = Tabs.TabName,
@TabOrder = Tabs.TabOrder,
@MobileTabName = Tabs.MobileTabName,
@AuthRoles = Tabs.AuthorizedRoles,
@ShowMobile = Tabs.ShowMobile FROM
Tabs
INNER JOIN
Portals ON Tabs.PortalID = Portals.PortalID
WHERE
TabID=@TabID/* Get Tabs list */
SELECT
TabName,
AuthorizedRoles,
TabID,
TabOrder
FROM
Tabs
WHERE
PortalID = @PortalID
ORDER BY
TabOrder
执行这个以后,那个输出型的参数就已经有值了!
parameters[5].Value就是你要的值了!!
Output是输出参数.
例如:
触发器中:
CREATE TRIGGER Example1_2_UPDATE
ON dbo.Example1_2
for update,update
as
IF UPDATE(A) OR UPDATE(B)
declare @id int,@aa int,@bb int,@wzbm_A varchar(50),@@bmcbf varchar(50)
set @wzbm_A='wzbm_A'
set @bmcbf = 'Example1_2_BF'
select @id = LS_D.ID FROM Example1_2 AS MX,
INSERTED AS LS_I,
DELETED AS LS_D
WHERE MX.ID=LS_D.ID
exec DMSCBFB @id,@wzbm_A,@bmcbf,@bb input--这就是调用的地方
================================
存储过程中:
CREATE procedure DMSCBFB
@idh int,
@wxw_bmlxa varchar(50),
@bmcbf varchar(50),
@aa1 int output -- 这就是输出的地方
as
select @parentobj2=id from sysobjects where name=@bmc and xtype='U'
declare sc_cursorw1 scroll cursor for select distinct name,colid From syscolumns Where id=@parentobj2 ORDER BY colid asc --and name<>'id'
open sc_cursorw1
fetch next from sc_cursorw1 into @zdmc1,@zfcd
while @@FETCH_STATUS=0
begin
if @zdmc1<>'' or @zdmc1<>null
Exec ALTER_DROP_table @bmcbf,@zdsl,@zdsl2,@table_tem,@table_tem2,@pp output --判断增减字段or修改字段类型长度等
end
Exec delete_table--删除临时表
GO