October 14, 2008 at 2:56 pm
Hello,
Apologies if this sounds basic but I am a little rusty with my SQL.
I have a database table which stores some user account details (bare in mind this is for an ASP.NET web app but the person who developed this web app didn't use ASP.NET roles). I have a seperate table which I have a relationship with, called Administrator. This stores IsAdministrator, which is a bit and so can be set to true/false, or a choice of 1/0. This account detail stores username, password, and some details about the company using the system (the client of this system is a company - same login for every person in the company). It doesn't make sense that I will have an IsAdministrator field in the table for a login shared by a company, but that's what I've been asked to do. BTW the normalisation is next to nothing in this schema. Account stores a bunch of company details, but if I am to add a new login for the same company (so the managing director has his own credentials), I will have to enter in again - and repeat - the path to the logo, and similar details. A few details could be unique though.
What I'm looking to do is to return the value of IsAdministrator that has been set in the table. So the stored procedure takes a UserName parameter (the username used to login), and and then runs a basic query which will join the Account (storing company details) and Administrator tables. I need to see the IsAdministrator field value of/for the joined row of Administrator/Account, where UserName (in account) = my parameter, @UserName. I will then need to return the value of Administrator (or store it in an output parameter?) so my calling web application, coded in VB.NET, can store the value in a programmatic field and I can use this value for conditional flow.
I hope this makes sense. How could I achieve this?
Also, when using uniqueidentifier, how can I automatically generate a unique guid every time I use "Open Table" to enter a record into the table. Is there a way I could use bigint to also automatically give me a unique value every time I enter a record into the table, via "Open Table"? I doubt it's wise to use ID as a Primary Key datatype.
Thanks,
October 14, 2008 at 3:43 pm
This may start you off in the right direction for the first part of your question:
create proc uspGetAdmins @User varchar(40), @IsAdmin varchar(1) OUTPUT
as
set @IsAdmin = (
select IsAdministrator
from tbl1 as t1
join tbl2 as t2
on t1.UserName = t2.UserName
where t1.UserName = @User
)
go
Use in your code something like:
exec uspGetAdmins 'JerryG', vbOutputVar
_________________________________
seth delconte
http://sqlkeys.com
October 15, 2008 at 6:20 am
Seth Delconte (10/14/2008)
This may start you off in the right direction for the first part of your question:create proc uspGetAdmins @User varchar(40), @IsAdmin varchar(1) OUTPUT
as
set @IsAdmin = (
select IsAdministrator
from tbl1 as t1
join tbl2 as t2
on t1.UserName = t2.UserName
where t1.UserName = @User
)
go
Use in your code something like:
exec uspGetAdmins 'JerryG', vbOutputVar
Thanks for that!
But one question: on the line above where, where you write "on t1...", that implies I need a UserName field in the admin table.
Is that right?
October 15, 2008 at 7:12 am
To implement a join, you need fields of like data types with potentially matching data to act as the join fields. They DO NOT, however, have to be named the same. For example, your join would work if the fields were like this:
select IsAdministrator
from Admin as t1
join Account as t2
on t1.UN = t2.UserName
where t1.UN = @User
In other words, the field containing the username data could be named 'UserName' in one table, and 'UN' in the other. 🙂
_________________________________
seth delconte
http://sqlkeys.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply