Click here to Skip to main content
15,922,015 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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


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


SQL
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
Posted
Comments
gvprabu 11-Jun-13 6:47am    
You need to combine the 2 SPs right... first u need to Update the Values , then u have to get the Login details right.
gvprabu 11-Jun-13 6:51am    
Update r Question, its not that much clear....
dibyaaryan007 11-Jun-13 7:08am    
Actually I want To Execute 2nd SP after Login Success using 1st SP.
Now How To Check Whether Login Successful Or Not..

Hi, try this...

SQL
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
 
Share this answer
 
v2
Comments
dibyaaryan007 11-Jun-13 7:05am    
But I need to update the VisitCount Column While Login Success.
gvprabu 11-Jun-13 7:08am    
then how u know log in is success
gvprabu 11-Jun-13 7:11am    
I updated the answer, Check now....
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900