关于存储过程返回值
现有存储过程如下
insert into TreesInformation(GUID,Username,Style,Babyhead,Wish,Wishdatetime,Wishto)
values(@guid,@userName,@style,@babyHead,@wish,@wishDatetime,@wishto)
select @id FROM TreesInformation
WHERE Username = @userName
怎样才asp.net中返回这个ID值？
[解决办法]
create proc GetUserId
declare @id int
insert into TreesInformation(GUID,Username,Style,Babyhead,Wish,Wishdatetime,Wishto)
values(@guid,@userName,@style,@babyHead,@wish,@wishDatetime,@wishto)
select @id=GUID FROM TreesInformation WHERE Username = @userName
return @id
in stored procedure,only return int type,if you want to return other types,
you'd better declare a output data type,such as,
create proc GetUserId
(
@userId varchar(200) output
)
....
[解决办法]
1、建立数据库test，新建一张表 users:
CREATE TABLE [dbo].[testSP] (
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[userName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userPassword] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
2、编写一个存储过程，如下：
CREATE PROCEDURE UserRegister
(
@UserName nvarchar(50),
@UserPassword nvarchar(50)
)
AS
insert into testSp(userName,userPassword) values(@userName,@userPassword)
return @@Identity
3、新建一个ASPX页面，放置一个BUTTON，在CLICK事件中，添加如下代码：
try
{
//将yourdb修改为你自己的数据库
SqlConnection conn = new SqlConnection("server=localhost;database=yourdb;integrated security=SSPI");
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "UserRegister"; //刚才建立的存储过程的名字
comm.CommandType =CommandType.StoredProcedure;
SqlParameter pName = new SqlParameter("@UserName",SqlDbType.NVarChar,50);
pName.Value ="yourName";//输入你的注册名称
comm.Parameters.Add(pName);
SqlParameter pPassword = new SqlParameter("@UserPassword",SqlDbType.NVarChar,50);
pPassword.Value ="yourPassword";//输入你的密码
comm.Parameters.Add(pPassword);
SqlParameter pRet = new SqlParameter("@ret",SqlDbType.Int,4); //定义返回值参数
pRet.Direction = ParameterDirection.ReturnValue;
comm.Parameters.Add(pRet);
conn.Open();
comm.ExecuteNonQuery();
//id就是你要取得id号
int id = Convert.ToInt32(pRet.Value);
conn.Close();
}
catch(SqlException ex)
{
//出错处理
}
