July 27, 2011 at 2:41 am
Hi
Is it possible to have a computed column on one table pointing to a value in another table?
e.g: I have a table called nl_SecUsers and that table has a value called EmailAddress, I would like the email address to pull from a table called tbl_wWebUsers. I tried the query below but it was giving an error
Query
select EmailAddress from tbl_wWebUsers where tbl_wWebUsers.SecUserID = nl_SecUsers.SecUserID
Error Meessage box content
---------------------------
Microsoft SQL Server Management Studio
---------------------------
Error validating the formula for column 'EmailAddress'.
Do you want to cancel your changes ?
---------------------------
Yes No Help
---------------------------
Please help, not sure if this is possible
The Fastest Methods aren't always the Quickest Methods
July 27, 2011 at 6:02 am
not directly like that...you could use a scalar function instead...but before i demo that...lets review what you are trying to do...there might be a better way.
if the email address is in another table, and it's reference-able via that foreign key, WHY do you need it in the other table? it's duplicating existing data. you could create a view that combines them together for you whenever you need it, or join the table when required.
anyway, I would not do it this way, but for a scalar function it could be something like this:
CREATE FUNCTION dbo.GetEmail(@SecUserID int)
RETURNS varchar(255)
AS
BEGIN
declare @Results varchar(255)
SELECT @Results = EmailAddress
from tbl_wWebUsers
where tbl_wWebUsers.SecUserID = @SecUserID
return @Results
END --FUNCTION
GO
ALTER TABLE CONTACTS ADD AS dbo.GetEmail(SecUserID) PERSISTED
GO
Lowell
July 27, 2011 at 6:11 am
Why the heck was that column put in another table than the users table??
Sounds like the real solution unless you can give me a solid business case for the data design you currently have.
July 27, 2011 at 9:36 am
We're intergrating our security in another companies existing application so would like to change our Email Address feild to point to the email address in there table as a lot of there code relies on Email Address being there and they update the email address and we just need to use the email address to login into the site. so it would be fine to point to their email address feild. Our .net assemblies are already built so from either side removing one of the feilds will be a mission.
The Fastest Methods aren't always the Quickest Methods
July 27, 2011 at 9:49 am
Why not do a single statement once?
AFAIK you can use a function in the calculated field. I'd be slow but it might work.
July 27, 2011 at 10:19 am
can't have a single statement as there system has all there scripts and our security system has its scripts that access each of there own Email Address feilds. I know it will be slower but because we only need it for the login and then the rest of the system will use there code, we are able to have the negative performance from this operation.
Thanks for the help everyone 😀
The Fastest Methods aren't always the Quickest Methods
July 27, 2011 at 10:32 am
BinaryDigit (7/27/2011)
can't have a single statement as there system has all there scripts and our security system has its scripts that access each of there own Email Address feilds. I know it will be slower but because we only need it for the login and then the rest of the system will use there code, we are able to have the negative performance from this operation.Thanks for the help everyone 😀
Here's a sample. I haven't tested cross-db or linked server but I don't see why it wouldn't work.
SET IMPLICIT_TRANSACTIONS ON
GO
USE tempdb
GO
SELECT sid, loginname INTO dbo.SSC_X FROM sys.syslogins
--SELECT sid, loginname FROM dbo.SSC_X
GO
CREATE FUNCTION dbo.fn_SSC_X(@sid VARBINARY(85))
RETURNS sysname
AS
BEGIN
RETURN (SELECT loginname FROM sys.syslogins WHERE sid = @sid)
END
GO
ALTER TABLE dbo.SSC_X
ADD calc_login_name AS dbo.fn_SSC_X(sid)
GO
SELECT sid, loginname, calc_login_name FROM dbo.SSC_X
ROLLBACK
July 27, 2011 at 5:15 pm
PERSISTED was a nice idea but it can't be done due to the data access the UDF is trying to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 6:36 pm
Wow, how did I miss that post from lowell :w00t:.
Bedtime!
July 28, 2011 at 12:30 am
Thank you, this has helped a lot 😀
The Fastest Methods aren't always the Quickest Methods
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply