Conditional select in T-SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Conditional select in T-SQL query

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2010-10-22 : 13:22:23

Hello All,

I have this query below which needs to work like this -

A. If @Control table has no records for ID 270 and no DescValue then flags need to be set to ZERO.

B. IF @Control table has records FFC range and or EEC and or BBC (which matches to the @StandardControls where ControlID IN (1,2,3) and or other control ranges then I need the ranges other than FFC in the @ControlRanges table to do further coding.

C. IF @Control table has record/records ONLY for FFC range (which matches to the @StandardControls where ControlID = 1)
Then I need one record (since all the ranges are same FFC 0 to 65) in the @ControlRanges to do further coding.

DECLARE @ControlFlag BIT, @MinControl INT, @MaxControl INT
DECLARE @ControlRanges TABLE (ControlValue VARCHAR(1000))

DECLARE @StandardControls TABLE (ID INT, ControlID INT, ControlDescription VARCHAR(1000))
INSERT INTO @StandardControls VALUES (316, 1, 'FFC : 0% to 65%')
INSERT INTO @StandardControls VALUES (316, 2, 'EEC : 0% to 40%')
INSERT INTO @StandardControls VALUES (316, 3, 'BBC : 0% to 30%')

DECLARE @Control TABLE (ID INT, DescValue VARCHAR(1000))
INSERT INTO @Control VALUES (270, '')
INSERT INTO @Control VALUES (270, 'FFC : 0% to 60%')
INSERT INTO @Control VALUES (270, '5% - 35%')
INSERT INTO @Control VALUES (270, '0% - 35%')
INSERT INTO @Control VALUES (210, '5% - 35%')
INSERT INTO @Control VALUES (270, 'EEC : 0% to 40%')
select * from @Control

IF (SELECT COUNT(*) FROM @Control WHERE ID = 270 AND ISNULL(DescValue,'') = '') = 0
BEGIN
print '1'
--Function to do other logic
END
ELSE
IF (SELECT COUNT(*) FROM @Control ctrl
INNER JOIN @StandardControls SC
ON ctrl.DescValue = SC.ControlDescription
AND SC.ID = 316
AND SC.ControlID <> 1
WHERE ctrl.ID = 270
) > 0
BEGIN

print '2'

INSERT INTO @ControlRanges
SELECT ctrl.DescValue
FROM @Control ctrl
INNER JOIN @StandardControls SC
ON ctrl.DescValue = SC.ControlDescription
AND SC.ID = 316
AND SC.ControlID <> 1
WHERE ctrl.ID = 270
select * from @ControlRanges
--Function passing @ControlRanges values
END
ELSE
IF (SELECT COUNT(*) FROM @Control ctrl
INNER JOIN @StandardControls SC
ON ctrl.DescValue = SC.ControlDescription
AND SC.ID = 316
AND SC.ControlID = 1
WHERE ctrl.ID = 270

) > 0
BEGIN

print '3'

INSERT INTO @ControlRanges
SELECT ctrl.DescValue
FROM @Control ctrl
INNER JOIN @StandardControls SC
ON ctrl.DescValue = SC.ControlDescription
AND SC.ID = 316
AND SC.ControlID = 1
WHERE ctrl.ID = 270
--Function passing @ControlRanges values
select * from @ControlRanges

END

Given example falls under B. and will have these results -
('5% - 35%')
('0% - 35%')
('EEC : 0% to 40%')

My code below is not working according these conditions above.

Can anyone help me with simplifying and corrected this code? I think it can be wriiten in short SQL using 2005/2008 functionalities.

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources