Login Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Author

Topic

Mpilo
Yak Posting Veteran

52 Posts

Posted - 2008-11-06 : 02:52:00

Hi guys i looking for stored procedure that will allow the user to log in to a position the user is assigned to like:I have the structures Province,District and Branch.A user is assigned to one of this structure.and then if u a assigned to province i want a user tu see her province only and district that fall to that province

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 02:56:16

pass user as a parameter and retrive only those Province or District or Branch for which he belongs from table. Show some sample data from table if you need more info.

Mpilo
Yak Posting Veteran

52 Posts

Posted - 2008-11-06 : 03:11:28

Here is My user table
Create Table Userr
(
UserrNo int identity(020,1)Not Null,
FirstName varchar(30),
Surname varchar(30),
IdNumber varchar(13),
Province varchar(30),
AssignTo varchar(30),
OfficeName varchar(30),
CellNumber int not null,
Email varchar(30),
R_Address varchar(100),
R_Code int not null,
UserName varchar(30),
Passwordd int not null,
Usertype varchar(30),
DateCreated Datetime default getdate(),
MembershipNo int Not Null,
Constraint UserType_Pk Primary key clustered(UserrNo)
);

My login Procedure
ALTER procedure [dbo].[Logginde]
@UserName varchar(100)='',
@Password int=0
As
SELECT UserName,Password,AssignTo,FirstName,IdNumber,R_Address,R_Code,CellNumber,E_mail,Province
FROM
(SELECT Username AS UserName,Passwordd AS Password,AssignTo,FirstName,IdNumber,R_Address,R_Code,CellNumber,E_mail,Province
FROM Userr
UNION ALL
SELECT SurName,MembershipNum,Languages,FirstName,IdNumber,R_Address,R_Code,CellNumber,E_mail,Title
FROM Member
)t
WHERE UserName=@UserName and Password=@Password

and Login procedure works like this:
the user and member can login,if the user Login it first check where the user is assigned(Province,District,Branch) to and call that form my problem now is ,if the user is login with province i want the user to see only that province details,also in district

here is my province table
Create table Province
(
ProvinceNo int identity(001,1) Not Null,
ProvinceName varchar (30),
CityName varchar (30),
LocationName varchar (30),
Addresss varchar (100),
TelephoneNo varchar (15),
E_mail varchar (30),
DateCreated Datetime default getdate(),
Constraint Province_Pk Primary key clustered (ProvinceNo)
);

------------------------------------------------------------------

Create table District
(
DistrictNo int identity(0001,1) Not Null,
DistrictName varchar(30),
CityName varchar (30),
LocationName varchar (30),
Addresss varchar (100),
TelephoneNo varchar (15),
E_mail varchar (30),
DateCreated Datetime default getdate(),
ProvinceNo int Not Null,
Constraint District_Pk Primary key clustered (DistrictNo)
);

Alter table District with nocheck add Constraint District_Fk Foreign key (ProvinceNo) references Province (ProvinceNo) on delete cascade

quote:
Originally posted by visakh16

pass user as a parameter and retrive only those Province or District or Branch for which he belongs from table. Show some sample data from table if you need more info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 04:03:58

[code]ALTER procedure [dbo].[Logginde]
@UserName varchar(100)='',
@Password int=0
As
SELECT UserName,Password,AssignTo,FirstName,IdNumber,R_Address,R_Code,CellNumber,E_mail,p.ProvinceName,d.DistrictName
FROM
(SELECT Username AS UserName,Passwordd AS Password,AssignTo,FirstName,IdNumber,R_Address,R_Code,CellNumber,E_mail,Province
FROM Userr
UNION ALL
SELECT SurName,MembershipNum,Languages,FirstName,IdNumber,R_Address,R_Code,CellNumber,E_mail,Title
FROM Member
)t
JOIN Province p
ON p.ProvinceName=t.Province
JOIN District d
ON d.ProvinceNo=p.ProvinceNo
WHERE UserName=@UserName and Password=@Password[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources