我正在尝试使用ASP的sql Server存储过程：
ALTER PROCEDURE [dbo].[user_insert]
@firstName NVARCHAR(50),@lastName NVARCHAR(50),@cityid INT,@email NVARCHAR(100),@password NVARCHAR(12),@Affiliate INT
AS
BEGIN
DECLARE @index1 INT
SET @index1 = 0
IF ( NOT EXISTS ( SELECT * FROM dbo.tbl_users
WHERE user_email = @email ) )
BEGIN
INSERT INTO dbo.tbl_users
( user_first_name,user_last_name,city_id,user_email,user_password,Affiliate_id
)
VALUES ( @firstName,@lastName,@cityid,@email,@password,@Affiliate
)
SET @index1 = ( SELECT @@IDENTITY
END
RETURN @index1
END
ASP是
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = conn
oCmd.CommandText = "user_insert"
oCmd.CommandType = 4
oCmd.Parameters.Append oCmd.CreateParameter("firstName",203,1,100,"1")
oCmd.Parameters.Append oCmd.CreateParameter("lastName","2" )
oCmd.Parameters.Append oCmd.CreateParameter("cityid",3,2,1)
oCmd.Parameters.Append oCmd.CreateParameter("email",200,txtmail)
oCmd.Parameters.Append oCmd.CreateParameter("password",12,"2" )
oCmd.Parameters.Append oCmd.CreateParameter("Affiliate",1)
oCmd.Parameters.Append oCmd.CreateParameter("@index1",2)
Set rs = oCmd.Execute
response.write oCmd.Parameters("@index1").Value &"<br>"
Set oCmd = Nothing
如果我在sql Server终端中运行SP它正在工作,但是当我使用asp代码时,如果它是“新用户”,我没有得到任何返回“index1”的值
我能做什么？
解决方法
如其他答案所述,更改为adParamReturnValue.
您还需要使用SET NOCOUNT ON来抑制第一个结果集.此外,您应该停止使用@@ IDENTITY,而是使用SCOPE_IDENTITY.
ALTER PROCEDURE [dbo].[user_insert]
@firstName nvarchar(50),@lastName nvarchar(50),@cityid int,@email nvarchar(100),@password nvarchar(12),@Affiliate int
AS BEGIN
SET NOCOUNT ON
IF (not EXISTS (SELECT * FROM dbo.tbl_users WHERE user_email=@email))
begin
INSERT INTO dbo.tbl_users(user_first_name,Affiliate_id)
VALUES(@firstName,@Affiliate)
--set
RETURN SCOPE_IDENTITY()
end
RETURN 0
end
注意：您甚至可以省略末尾的RETURN 0,因为0是默认的返回结果.
