updation with store procedure

Question

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 11-Jun-13 0:22am

dibyaaryan007

Add a Solution

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..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gvprabu · Answer 1 · 2013-06-11T00:51:00

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

dibyaaryan007 · Answer 2 · 2013-06-11T01:11:00

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

updation with store procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0