Help with writing a sproc

I am really new to SQL and I am hoping someone can give me some basic help
with writing a sproc.
I have 3 tables
Table 1
UserId - int
UserRole - int
Table 2
UserId - int
UserName - char
Table 3
RoleId - int
RoleName - char
I am looking for a sproc that will return a table like so
UserId UserName UserRole
1 Bob Admin
Sorry if this seems really simple, but I have really can't figure it out
Its hard to guess what you are looking for in this procedure. For instance
do you pass in a user name? UserID, RoleID?
Here is a proc that will return a list of all user ids, user names, and
their roles.
Create Proc sproc
as
select table1.userId, UserName, UserRole=RoleName
from table1, table2, table3
where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
Here is a proc which will accept a user id as a parameter and return a list
of all roles for that user id in the format you are looking for
Create Proc sproc (@.userID int)
as
select table1.userId, UserName, UserRole=RoleName
from table1, table2, table3
where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
and table1.userID=@.UserID
Here is a proc which will accept a role id as a parameter and return a list
of all roles for that user id in the format you are looking for
Create Proc sproc (@.roleID int)
as
select table1.userId, UserName, UserRole=RoleName
from table1, table2, table3
where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
and table1.userRole=@.roleID
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"NewGuy" <a@.a.com> wrote in message
news:eGT3T36hEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I am really new to SQL and I am hoping someone can give me some basic help
> with writing a sproc.
> I have 3 tables
> Table 1
> UserId - int
> UserRole - int
> Table 2
> UserId - int
> UserName - char
> Table 3
> RoleId - int
> RoleName - char
> I am looking for a sproc that will return a table like so
> UserId UserName UserRole
> --
> 1 Bob Admin
> Sorry if this seems really simple, but I have really can't figure it out
>
|||Thanks,
I think I am trying return the results from table 1
But with the values from table 2 and 3 replacing the int from table 1...
I'll play around with what you gave me and see if anything works
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eX5G4$7hEHA.3264@.tk2msftngp13.phx.gbl...
> Its hard to guess what you are looking for in this procedure. For instance
> do you pass in a user name? UserID, RoleID?
> Here is a proc that will return a list of all user ids, user names, and
> their roles.
> Create Proc sproc
> as
> select table1.userId, UserName, UserRole=RoleName
> from table1, table2, table3
> where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
>
> Here is a proc which will accept a user id as a parameter and return a
list
> of all roles for that user id in the format you are looking for
> Create Proc sproc (@.userID int)
> as
> select table1.userId, UserName, UserRole=RoleName
> from table1, table2, table3
> where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
> and table1.userID=@.UserID
> Here is a proc which will accept a role id as a parameter and return a
list[vbcol=seagreen]
> of all roles for that user id in the format you are looking for
> Create Proc sproc (@.roleID int)
> as
> select table1.userId, UserName, UserRole=RoleName
> from table1, table2, table3
> where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
> and table1.userRole=@.roleID
>
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "NewGuy" <a@.a.com> wrote in message
> news:eGT3T36hEHA.2340@.TK2MSFTNGP11.phx.gbl...
help
>