使用存储过程进行更新

问题描述：

你好..

i我试图通过商店程序更新表..

i有一个log_in表..

现在我想要检索最长登录时间。

给出以前的SP。

hello..
i am trying to update table through store procedure..
i have a log_in table..
now i want to retrieve max login time.

giving previous SP.

Create PROCEDURE [dbo].[USP_GetUserDetails] 
(	
	@UserName NVARCHAR(10),
	@UserPassword NVARCHAR(100),
	@LoginStatus BIT,
	@IsActive NCHAR(1)
)
	
AS
BEGIN
	SELECT USERID,UserName,UserPassword,LoginStatus,IsActive,DeptID
		FROM dbo.UserLogin 
		WHERE UserName=@UserName AND UserPassword=@UserPassword AND LoginStatus =1 AND IsActive='Y'
ENDEND

现在我想在此过程中添加以下代码..

登录成功后，下面的代码执行ll在上一个。程序。

Now I want to add below codes with in this procedure..
after login success the below code ll execute with in the prev. procedure.

CREATE PROCEDURE [dbo].[retrive]

AS
BEGIN
declare @value integer

    SET @value = (SELECT m.VisitCount FROM UserLogin m WHERE m.UserID = '1')
    update UserLogin set VisitCount=@value+1 where UserID='1'
END

帮帮我..

问候

Help Me..
Regards

答

试试这个...

Hi, try this...

Create PROCEDURE [dbo].[USP_GetUserDetails] 
(	
	@UserName NVARCHAR(10),
	@UserPassword NVARCHAR(100),
	@LoginStatus BIT,
	@IsActive NCHAR(1)
)
	
AS
BEGIN
	DECLARE @USERID INT

	SELECT USERID,UserName,UserPassword,LoginStatus,IsActive,DeptID
		FROM dbo.UserLogin 
		WHERE UserName=@UserName AND UserPassword=@UserPassword AND LoginStatus =@LoginStatus  AND IsActive=@IsActive 

	-- Get User ID 
	SELECT @USERID=USERID  
	FROM dbo.UserLogin 
	WHERE UserName=@UserName AND UserPassword=@UserPassword AND LoginStatus =@LoginStatus  AND IsActive=@IsActive 
 
	-- update the visitCount
        -- @value is same as current values from UserLogin, so no need to new variable and get the values from table  
	UPDATE UserLogin set VisitCount=ISNULL(VisitCount,0)+1 where UserID=@USERID 
 
END

GVPRabu

这个怎么样？

CREATE PROCEDURE [dbo]。[USP_GetUserDetails]

（

@UserName NVARCHAR（10），

@UserPassword NVARCHAR（100），

@LoginStatus BIT，

@IsActive NCHAR（1）

）

AS

BEGIN

if（（从dbo.UserLogin选择COUNT（*）WHERE UserName = @ UserName AND UserPassword = @ UserPassword AND LoginStatus = 1 AND IsActive ='Y'）> 0）

SELECT USERID，UserName ，UserPassword，LoginStatus，IsActive，DeptID

来自dbo.UserLogin

WHERE UserName = @ UserName AND UserPassword = @ UserPassword AND LoginStatus = 1 AND IsActive ='Y'

声明@value整数

SET @value =（SELECT m.VisitCount FROM UserLogin m WHERE m.UserID ='1'）

更新UserLogin设置VisitCount = @ value + 1其中UserID ='1'

END

What About This One ?

CREATE PROCEDURE [dbo].[USP_GetUserDetails]
(
@UserName NVARCHAR(10),
@UserPassword NVARCHAR(100),
@LoginStatus BIT,
@IsActive NCHAR(1)
)

AS
BEGIN
if((Select COUNT(*) from dbo.UserLogin WHERE UserName=@UserName AND UserPassword=@UserPassword AND LoginStatus =1 AND IsActive='Y')>0)
SELECT USERID,UserName,UserPassword,LoginStatus,IsActive,DeptID
FROM dbo.UserLogin
WHERE UserName=@UserName AND UserPassword=@UserPassword AND LoginStatus =1 AND IsActive='Y'

declare @value integer

SET @value = (SELECT m.VisitCount FROM UserLogin m WHERE m.UserID = '1')
update UserLogin set VisitCount=@value+1 where UserID='1'
END

使用存储过程进行更新

相关推荐