August 25, 2008 at 9:55 am
Hey!
I have the following situation in a SP:
Table X contains
ID A B
and temporary table Y is filled during the SP's execution and contains all values that X should contain for A and B with ID something. So, I want to make sure that all values in Y (A,B) are present in X (ID,A,B) for a given ID - basically, making sure all values of X with some ID are identical to Y, no more and no less, or I trigger an error.
Any thoughts on how I can achieve this? Thanks for your time!
B
August 25, 2008 at 10:06 am
Could you post some sample data so I can really understand what you are asking?
Based on what I understand your question to be I think you could something like this:
IF Exists (Select Id from X Where Not Exists (Select A from Y where X.A = Y.A and X.B = Y.B)
BEGIN
-- Error Code here
Return
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 10:11 am
Sample data:
table X
ID Name Value
AXN E1 N1
AXN E2 N2
BZS E1 N1
AXN E3 N3
table Y for ID AXN
Name Value
E1 N1
E2 N2
E3 N3
In this case, there is no reason for error, but if table Y missed one of the pairs or had one more it should trigger it.
August 25, 2008 at 12:51 pm
I think this will work:
[font="Courier New"]DECLARE @x TABLE (id CHAR(3), name CHAR(2), value CHAR(2))
DECLARE @y TABLE (name CHAR(2), value CHAR(2))
INSERT INTO @x
SELECT
'AXN', 'E1', 'N1'
UNION ALL
SELECT
'AXN', 'E2', 'N2'
UNION ALL
SELECT
'BZS', 'E1', 'N1'
UNION ALL
SELECT
'AXN', 'E3', 'N3'
/* commenting out any of the 1st 3 rows will cause error
uncommenting the last row will cause error*/
INSERT INTO @y
SELECT
'E1', 'N1'
UNION ALL
SELECT
'E2', 'N2'
UNION ALL
SELECT
'E3', 'N3'/*
Union All
Select
'E4', 'N3'*/
IF EXISTS (SELECT Id FROM @X X WHERE X.id = 'AXN' AND
NOT EXISTS (SELECT name FROM @Y Y WHERE X.name = Y.name
AND X.value = Y.value)) OR
EXISTS(SELECT name FROM @Y Y WHERE NOT EXISTS
(SELECT id FROM @X X WHERE X.name = Y.name AND X.value = Y.value
AND X.id = 'AXN'))
BEGIN
-- Error Code here
PRINT 'Error'
RETURN
END
PRINT 'No Error'
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2008 at 1:06 pm
Thanks for the help - I thought the solution would be simpler... :S
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply