如何从存储的过程中获取返回值? [英] How to get the return value from a stored proc?

问题描述

Create Procedure sp_Login
(
@o_loginid AS CHAR(10) = '',
@o_password AS CHAR(10) = '',
@o_daysexpire AS NUMERIC = 0 OUTPUT,
@c_firstnme as char(10) = '' OUTPUT,
@c_lastnme as char(10) = '' OUTPUT,
@c_userac as char(1) = '' OUTPUT
)

AS

DECLARE @diffdate SMALLINT
DECLARE @date2 DATETIME

IF NOT exists(SELECT c_userid FROM tbl_Users WHERE c_userid = @o_loginid)
              RETURN 1 -- username doesnt exists
ELSE
   BEGIN
	   IF Not exists(SELECT c_password FROM tbl_Users WHERE c_password = @o_password AND c_userid = @o_loginid) 
   					 RETURN 2  -- incorrect password
       ELSE
			BEGIN

			IF (SELECT b_logintoggle FROM tbl_Users WHERE c_userid = @o_loginid) = 1
					  RETURN 5 -- already login
					  
            ELSE
                 BEGIN
 		   		 IF NOT (SELECT b_withexpiration FROM tbl_Users WHERE c_userid = @o_loginid) = 1 

				         BEGIN

   		                 SELECT @c_firstnme = c_firstname,
						        @c_lastnme = c_lastname,
								@c_userac = c_usergroup
						 FROM tbl_Users
						 WHERE c_userid = @o_loginid

						 UPDATE tbl_Users
                         SET b_logintoggle = 1
						 WHERE c_userid = @o_loginid
                    
        			     RETURN 0 -- success login

						 END
                 ELSE
				 	 BEGIN
					 					    
						SELECT	@date2 = d_expirationdate FROM tbl_Users  WHERE c_userid = @o_loginid							  
						SELECT @diffdate = DATEDIFF(dd,GETDATE(),@date2)
					--	print @diffdate
						IF (@diffdate > 0 AND @diffdate < 7)
               				BEGIN
			
								SET @o_daysexpire = @diffdate 
								RETURN 3 -- notice of expiration
                 
							END
						 		 
						ELSE
 		 					BEGIN
							   IF (@diffdate <= 0)	
								  RETURN 4 -- expired account
							   ELSE
								  SELECT @c_firstnme = c_firstname,
						                 @c_lastnme = c_lastname,
								         @c_userac = c_usergroup
								  FROM tbl_Users
						          WHERE c_userid = @o_loginid

								  	 UPDATE tbl_Users
									 SET b_logintoggle = 1
									 WHERE c_userid = @o_loginid

								  RETURN 0 -- success login
							END 
					 END 
            END  
           END         
		      
    END
	
	
	RETURN (0)

我有这个存储过程.如何使用sqlclient获取返回值?

谢谢.

I have this stored procedure. How can I get the return values using sqlclient?

Thanks.

推荐答案

请参见 ^ ]

定义命令对象以执行过程并捕获值.

用C#编写代码，但您会明白的

Define a command object to execute the procedure and capture the values.

Code in C#, but you''ll get the idea

// Your code should get the connection string from web.config
string connectionString =
  @"Server=ASQLSERVER; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.sp_Login"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@o_loginid", "USERNAME"));

        cmd.Parameters.Add(new SqlParameter("@o_password", "PASSWORD"));

        SqlParameter countParameter = new SqlParameter("@o_daysexpire", 0);
        countParameter.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(countParameter);

// TO DO
// Add in each of your output params here.....
// TO DO


        conn.Open();
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();

        // After executing your query, you can get at the parameter values and do something with them
        
        int count = Int32.Parse(cmd.Parameters["@o_daysexpire"].Value.ToString());
        
        conn.Close();
    }
}

全文在这里

http://www.sqlteam.com/article/stored-procedures-returning-data [ ^ ]

Full article here

http://www.sqlteam.com/article/stored-procedures-returning-data[^]

您好，
您会看到以下链接: http://www.codeproject.com/KB/database/albumviewer.aspx [ ^ ]

hi there
you see this link: http://www.codeproject.com/KB/database/albumviewer.aspx[^]

这篇关于如何从存储的过程中获取返回值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！