June 16, 2015 at 4:44 am
Hello,
I'm no expert in SQL, I dabble here and there.
I need to create a function which takes two bigint parameters and returns true or false depending on it a condition is met.
So I've opened up my database expanded the functions folder and I have three options:
Table-valued functions
Scalar-values functions
Aggregate functions
What one do I choose?
Basically what my function will do is take two parameters. i.e
@UserId1 bigint, @UserId2 bigint
It will compare the values @UserId2 has in his/hers profile and if they match @UserId1 profile it will return true, if it fails on anything it will return false.
June 16, 2015 at 5:15 am
.Netter (6/16/2015)
Hello,I'm no expert in SQL, I dabble here and there.
I need to create a function which takes two bigint parameters and returns true or false depending on it a condition is met.
So I've opened up my database expanded the functions folder and I have three options:
Table-valued functions
Scalar-values functions
Aggregate functions
What one do I choose?
Basically what my function will do is take two parameters. i.e
@UserId1 bigint, @UserId2 bigint
It will compare the values @UserId2 has in his/hers profile and if they match @UserId1 profile it will return true, if it fails on anything it will return false.
Can you show the code you intend to use for the comparison?
In any case, with few exceptions you should choose inline table-valued functions in preference to multi-statement table-valued functions or scalar functions as they typically perform better.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2015 at 5:46 am
Hey,
I can't provide any code, as I haven't got that far yet.
I have built my UserSettings table where by I will be comparing the values between this table and the UserProfile Table.
So I'll be selecting everything from UserSettings that are linked to @UserId1
Then I will compare all of the 6 columns values (which come from UserSettings) with the values that are located inside UserProfile which are linked to @UserId2
If @UserId2 values are the same as @UserId1 then I will return true otherwise I will return false.
Thanks for the information regarding the function.
June 16, 2015 at 9:21 am
.Netter (6/16/2015)
Hey,I can't provide any code, as I haven't got that far yet.
I have built my UserSettings table where by I will be comparing the values between this table and the UserProfile Table.
So I'll be selecting everything from UserSettings that are linked to @UserId1
Then I will compare all of the 6 columns values (which come from UserSettings) with the values that are located inside UserProfile which are linked to @UserId2
If @UserId2 values are the same as @UserId1 then I will return true otherwise I will return false.
Thanks for the information regarding the function.
You might not need a function at all. If I understand correctly, you're needing something like this:
SELECT some other fields,
CASE WHEN US2.UserId IS NOT NULL THEN true value ELSE false value END AS TRUE_FALSE
FROM sometable AS ST
LEFT OUTER JOIN UserSettings AS US1
ON ST.UserId1 = US1.UserId
LEFT OUTER JOIN UserSettings AS US2
ON ST.UserId2 = US2.UserId
AND US1.Setting1 = US2.Setting1
AND US1.Setting2 = US2.Setting2
AND US1.Setting3 = US2.Setting3
AND US1.Setting4 = US2.Setting4
AND US1.Setting5 = US2.Setting5
AND US1.Setting6 = US2.Setting6
You'll have to adapt the code to your particular situation, but let me know if this helps.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 16, 2015 at 1:52 pm
.Netter (6/16/2015)
Hey,I can't provide any code, as I haven't got that far yet.
I have built my UserSettings table where by I will be comparing the values between this table and the UserProfile Table.
So I'll be selecting everything from UserSettings that are linked to @UserId1
Then I will compare all of the 6 columns values (which come from UserSettings) with the values that are located inside UserProfile which are linked to @UserId2
If @UserId2 values are the same as @UserId1 then I will return true otherwise I will return false.
Thanks for the information regarding the function.
Two important questions:
1. Is there more than one entry in dbo.UserSettings per user?
2. Will it be possible for the function to receive userIDs that don't exist in UserSettings?
If the answer to both questions is no then... using this sample data (I limited the number of settings to three for brevity):
CREATE TABLE dbo.UserProfile(UserID bigint primary key);
CREATE TABLE dbo.UserSettings
(
SettingID int identity primary key,
UserID bigint foreign key REFERENCES UserProfile(UserID),
Setting1 int NOT NULL,
Setting2 int NOT NULL,
Setting3 int NOT NULL
);
GO
INSERT dbo.UserProfile VALUES (1),(2),(3);
INSERT dbo.UserSettings (UserID, Setting1, Setting2, Setting3)
VALUES (1,5,5,5), (2,5,5,5), (3,5,5,9);
GO
We could create this function:
CREATE FUNCTION dbo.CompareSettings (@user1 bigint, @user2 bigint)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT TRUE_FALSE =
CASE
WHEN MAX(Setting1) = MIN(Setting1) AND
MAX(Setting2) = MIN(Setting2) AND
MAX(Setting3) = MIN(Setting3)
THEN 'True'
ELSE 'False'
END
FROM dbo.UserSettings
WHERE UserID IN (@user1, @user2)
GO
You could use the function like this:
-- examples:
SELECT * FROM dbo.CompareSettings(1,2) -- returns True
SELECT * FROM dbo.CompareSettings(1,3) -- returns False
... and if you wanted to get a list of everybody and how they compare to everyone else you could do this:
SELECT up1.userID, up2.UserID, TRUE_FALSE
FROM dbo.UserProfile up1
CROSS APPLY dbo.UserProfile up2
CROSS APPLY dbo.CompareSettings(up1.UserID, up2.UserID)
WHERE up1.UserID < up2.UserID
Which returns:
userID UserID TRUE_FALSE
-------------------- -------------------- ----------
1 2 True
1 3 False
2 3 False
If the answer to either of the above questions was yes then more information will be required to get you the best solution.
-- Itzik Ben-Gan 2001
June 16, 2015 at 3:55 pm
I'd stick with a straightforward scalar function for that. For any column that can't have NULL values, naturally you can remove the IS NULL conditions.
CREATE FUNCTION dbo.Compare_User_Settings_To_Profile
(
@UserId1 int,
@UserId2 int
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT CASE WHEN up.UserId IS NULL THEN 0 ELSE 1 END AS does_data_match
FROM dbo.UserSettings us
LEFT OUTER JOIN dbo.UserProfile up ON
us.UserId = @UserId1 AND
up.UserId = @UserId2 AND
(up.value1 = us.value1 OR (up.value1 IS NULL AND us.value1 IS NULL)) AND
(up.value2 = us.value2 OR (up.value2 IS NULL AND us.value2 IS NULL)) AND
(up.value3 = us.value3 OR (up.value3 IS NULL AND us.value3 IS NULL)) AND
(up.value4 = us.value4 OR (up.value4 IS NULL AND us.value4 IS NULL)) AND
(up.value5 = us.value5 OR (up.value5 IS NULL AND us.value5 IS NULL)) AND
(up.value6 = us.value6 OR (up.value6 IS NULL AND us.value6 IS NULL))
)
END --function
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply