December 29, 2016 at 7:09 am
Folks:
Good Day!
Can someone help me with DYNAMIC SQL. You may insert your code inside the WHILE LOOP below where I have a "PRINT" statement.
What I need is to get that value of @C , check if ti si > 0 and then print it ( if it is greater than zero )
TRUST ME! The SQL statement that is generated inside the loop works fine.
Declare @C Bigint
Declare @i int
Declare @STR NVARCHAR(1000)
Select @i=1
While( @i <= 24 )
Begin
Select @STR =
'Select @C=COUNT(*) FROM dbo.xxxTmpClaims '
+'where'
+ '('
+ '(ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + ','''') <> '''' AND ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + 'date,'''') = '''' )'
+ ' OR '
+ '(ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + ','''') = '''' AND ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + 'date,'''') <> '''' ) '
+ ')'
--Help me get the value of @C and I need to print it if the value is > 0
PRINT @C
Select @i=@i+1;
End
December 29, 2016 at 7:14 am
Would a simple IF...ELSE (Transact-SQL) statement not achieve this?
Note that you can't interact with variables inside D-SQL. So you would need to do any handling and declarations with them inside, not outside.
Edit: Also, what are you trying to achieve here? Looping is incredibly inefficient in SQL Server. Perhaps it would be better for us to perhaps look at your end goal and provide a better solution that doesn't require D-SQL and a looping structure.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 29, 2016 at 7:17 am
THOM A: I wanted you to modify the code for me.. (Please )
Never mind!
Done!
It works!
No Help needed.
Declare @C Bigint
Declare @i int
Declare @STR NVARCHAR(1000)
Select @i=1
While( @i <= 24 )
Begin
Select @STR =
'Select @C=COUNT(*) FROM dbo.xxxTmpClaims '
+'where'
+ '('
+ '(ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + ','''') <> '''' AND ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + 'date,'''') = '''' )'
+ ' OR '
+ '(ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + ','''') = '''' AND ISNULL(otherprocedurecode' + CAST(@i as VARCHAR(2) ) + 'date,'''') <> '''' ) '
+ ')'
EXECUTE sp_executesql @STR,N'@i int,@c Bigint OUTPUT', @i = @i, @C=@c OUTPUT
If( @C > 0 )
Begin
PRINT @C
End
--Help me get the value of @C and I need to print it if the value is > 0
Select @i=@i+1;
End
December 29, 2016 at 7:43 am
i was wondering if you could do the same as a set based operation, without the loop
does this produce your expected results?
SELECT
SUM(CASE WHEN (ISNULL(otherprocedurecode1,'') <> '' AND ISNULL(otherprocedurecode1date,'') = '' ) OR (ISNULL(otherprocedurecode1,'') = '' AND (ISNULL(otherprocedurecode1date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count1],
SUM(CASE WHEN (ISNULL(otherprocedurecode2,'') <> '' AND ISNULL(otherprocedurecode2date,'') = '' ) OR (ISNULL(otherprocedurecode2,'') = '' AND (ISNULL(otherprocedurecode2date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count2],
SUM(CASE WHEN (ISNULL(otherprocedurecode3,'') <> '' AND ISNULL(otherprocedurecode3date,'') = '' ) OR (ISNULL(otherprocedurecode3,'') = '' AND (ISNULL(otherprocedurecode3date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count3],
SUM(CASE WHEN (ISNULL(otherprocedurecode4,'') <> '' AND ISNULL(otherprocedurecode4date,'') = '' ) OR (ISNULL(otherprocedurecode4,'') = '' AND (ISNULL(otherprocedurecode4date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count4],
SUM(CASE WHEN (ISNULL(otherprocedurecode5,'') <> '' AND ISNULL(otherprocedurecode5date,'') = '' ) OR (ISNULL(otherprocedurecode5,'') = '' AND (ISNULL(otherprocedurecode5date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count5],
SUM(CASE WHEN (ISNULL(otherprocedurecode6,'') <> '' AND ISNULL(otherprocedurecode6date,'') = '' ) OR (ISNULL(otherprocedurecode6,'') = '' AND (ISNULL(otherprocedurecode6date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count6],
SUM(CASE WHEN (ISNULL(otherprocedurecode7,'') <> '' AND ISNULL(otherprocedurecode7date,'') = '' ) OR (ISNULL(otherprocedurecode7,'') = '' AND (ISNULL(otherprocedurecode7date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count7],
SUM(CASE WHEN (ISNULL(otherprocedurecode8,'') <> '' AND ISNULL(otherprocedurecode8date,'') = '' ) OR (ISNULL(otherprocedurecode8,'') = '' AND (ISNULL(otherprocedurecode8date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count8],
SUM(CASE WHEN (ISNULL(otherprocedurecode9,'') <> '' AND ISNULL(otherprocedurecode9date,'') = '' ) OR (ISNULL(otherprocedurecode9,'') = '' AND (ISNULL(otherprocedurecode9date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count9],
SUM(CASE WHEN (ISNULL(otherprocedurecode10,'') <> '' AND ISNULL(otherprocedurecode10date,'') = '' ) OR (ISNULL(otherprocedurecode10,'') = '' AND (ISNULL(otherprocedurecode10date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count10],
SUM(CASE WHEN( ISNULL(otherprocedurecode11,'') <> '' AND ISNULL(otherprocedurecode11date,'') = '' ) OR (ISNULL(otherprocedurecode11,'') = '' AND (ISNULL(otherprocedurecode11date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count11],
SUM(CASE WHEN (ISNULL(otherprocedurecode12,'') <> '' AND ISNULL(otherprocedurecode12date,'') = '' ) OR (ISNULL(otherprocedurecode12,'') = '' AND (ISNULL(otherprocedurecode12date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count12],
SUM(CASE WHEN (ISNULL(otherprocedurecode13,'') <> '' AND ISNULL(otherprocedurecode13date,'') = '' ) OR (ISNULL(otherprocedurecode13,'') = '' AND (ISNULL(otherprocedurecode13date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count13],
SUM(CASE WHEN (ISNULL(otherprocedurecode14,'') <> '' AND ISNULL(otherprocedurecode14date,'') = '' ) OR (ISNULL(otherprocedurecode14,'') = '' AND (ISNULL(otherprocedurecode14date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count14],
SUM(CASE WHEN (ISNULL(otherprocedurecode15,'') <> '' AND ISNULL(otherprocedurecode15date,'') = '' ) OR (ISNULL(otherprocedurecode15,'') = '' AND (ISNULL(otherprocedurecode15date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count15],
SUM(CASE WHEN (ISNULL(otherprocedurecode16,'') <> '' AND ISNULL(otherprocedurecode16date,'') = '' ) OR (ISNULL(otherprocedurecode16,'') = '' AND (ISNULL(otherprocedurecode16date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count16],
SUM(CASE WHEN (ISNULL(otherprocedurecode17,'') <> '' AND ISNULL(otherprocedurecode17date,'') = '' ) OR (ISNULL(otherprocedurecode17,'') = '' AND (ISNULL(otherprocedurecode17date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count17],
SUM(CASE WHEN (ISNULL(otherprocedurecode18,'') <> '' AND ISNULL(otherprocedurecode18date,'') = '' ) OR (ISNULL(otherprocedurecode18,'') = '' AND (ISNULL(otherprocedurecode18date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count18],
SUM(CASE WHEN (ISNULL(otherprocedurecode19,'') <> '' AND ISNULL(otherprocedurecode19date,'') = '' ) OR (ISNULL(otherprocedurecode19,'') = '' AND (ISNULL(otherprocedurecode19date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count19],
SUM(CASE WHEN (ISNULL(otherprocedurecode20,'') <> '' AND ISNULL(otherprocedurecode20date,'') = '' ) OR (ISNULL(otherprocedurecode20,'') = '' AND (ISNULL(otherprocedurecode20date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count20],
SUM(CASE WHEN (ISNULL(otherprocedurecode21,'') <> '' AND ISNULL(otherprocedurecode21date,'') = '' ) OR (ISNULL(otherprocedurecode21,'') = '' AND (ISNULL(otherprocedurecode21date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count21],
SUM(CASE WHEN (ISNULL(otherprocedurecode22,'') <> '' AND ISNULL(otherprocedurecode22date,'') = '' ) OR (ISNULL(otherprocedurecode22,'') = '' AND (ISNULL(otherprocedurecode22date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count22],
SUM(CASE WHEN (ISNULL(otherprocedurecode23,'') <> '' AND ISNULL(otherprocedurecode23date,'') = '' ) OR (ISNULL(otherprocedurecode23,'') = '' AND (ISNULL(otherprocedurecode23date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count23],
SUM(CASE WHEN (ISNULL(otherprocedurecode24,'') <> '' AND ISNULL(otherprocedurecode24date,'') = '' ) OR (ISNULL(otherprocedurecode24,'') = '' AND (ISNULL(otherprocedurecode24date,'') <> '' ) ) THEN 1 ELSE 0 END) AS [Count24]
FROM dbo.xxxTmpClaims
Lowell
December 29, 2016 at 8:03 am
mw112009 (12/29/2016)
THOM A: I wanted you to modify the code for me.. (Please )Never mind!
Done!
It works!
No Help needed.
Sure, it works. But, do you believe it's efficient? You're reading the table 24 times. Why not do it in a single read?
If you think that writing this code is a lot of work (it's mostly copy paste), you could find the way to make it dynamic.
CREATE TABLE xxxTmpClaims(
otherprocedurecode1 varchar(10),
otherprocedurecode1date datetime,
otherprocedurecode2 varchar(10),
otherprocedurecode2date datetime,
otherprocedurecode3 varchar(10),
otherprocedurecode3date datetime,
otherprocedurecode4 varchar(10),
otherprocedurecode4date datetime,
otherprocedurecode5 varchar(10),
otherprocedurecode5date datetime,
otherprocedurecode6 varchar(10),
otherprocedurecode6date datetime,
otherprocedurecode7 varchar(10),
otherprocedurecode7date datetime,
otherprocedurecode8 varchar(10),
otherprocedurecode8date datetime,
otherprocedurecode9 varchar(10),
otherprocedurecode9date datetime,
otherprocedurecode10 varchar(10),
otherprocedurecode10date datetime,
otherprocedurecode11 varchar(10),
otherprocedurecode11date datetime,
otherprocedurecode12 varchar(10),
otherprocedurecode12date datetime,
otherprocedurecode13 varchar(10),
otherprocedurecode13date datetime,
otherprocedurecode14 varchar(10),
otherprocedurecode14date datetime,
otherprocedurecode15 varchar(10),
otherprocedurecode15date datetime,
otherprocedurecode16 varchar(10),
otherprocedurecode16date datetime,
otherprocedurecode17 varchar(10),
otherprocedurecode17date datetime,
otherprocedurecode18 varchar(10),
otherprocedurecode18date datetime,
otherprocedurecode19 varchar(10),
otherprocedurecode19date datetime,
otherprocedurecode20 varchar(10),
otherprocedurecode20date datetime,
otherprocedurecode21 varchar(10),
otherprocedurecode21date datetime,
otherprocedurecode22 varchar(10),
otherprocedurecode22date datetime,
otherprocedurecode23 varchar(10),
otherprocedurecode23date datetime,
otherprocedurecode24 varchar(10),
otherprocedurecode24date datetime
);
INSERT INTO xxxTmpClaims
VALUES
('1',GETDATE(),'2',GETDATE(),'3',GETDATE(),'4',GETDATE(),NULL,NULL,'6',GETDATE(),'7',GETDATE(),'8',GETDATE(),'9',GETDATE(),'10',GETDATE(),
'11',GETDATE(),'12',GETDATE(),'13',GETDATE(),'14',GETDATE(),'15',GETDATE(),'16',GETDATE(),'17',GETDATE(),'18',GETDATE(),'19',GETDATE(),'20',GETDATE(),
'21',GETDATE(),'22',GETDATE(),'23',GETDATE(),'24',GETDATE()),
('1',GETDATE(),NULL,NULL,NULL,GETDATE(),'4',NULL,NULL,NULL,'6',GETDATE(),'7',GETDATE(),'8',GETDATE(),'9',GETDATE(),'10',GETDATE(),
'11',GETDATE(),'12',GETDATE(),'13',GETDATE(),'14',GETDATE(),'15',GETDATE(),'16',GETDATE(),'17',GETDATE(),'18',GETDATE(),'19',GETDATE(),'20',GETDATE(),
'21',GETDATE(),'22',GETDATE(),'23',GETDATE(),'24',GETDATE());
SELECT CodeColumn,CodeCount
FROM(
Select
COUNT(ISNULL(otherprocedurecode1, otherprocedurecode1date)) AS otherprocedurecode1Count,
COUNT(ISNULL(otherprocedurecode2, otherprocedurecode2date)) AS otherprocedurecode2Count,
COUNT(ISNULL(otherprocedurecode3, otherprocedurecode3date)) AS otherprocedurecode3Count,
COUNT(ISNULL(otherprocedurecode4, otherprocedurecode4date)) AS otherprocedurecode4Count,
COUNT(ISNULL(otherprocedurecode5, otherprocedurecode5date)) AS otherprocedurecode5Count,
COUNT(ISNULL(otherprocedurecode6, otherprocedurecode6date)) AS otherprocedurecode6Count,
COUNT(ISNULL(otherprocedurecode7, otherprocedurecode7date)) AS otherprocedurecode7Count,
COUNT(ISNULL(otherprocedurecode8, otherprocedurecode8date)) AS otherprocedurecode8Count,
COUNT(ISNULL(otherprocedurecode9, otherprocedurecode9date)) AS otherprocedurecode9Count,
COUNT(ISNULL(otherprocedurecode10, otherprocedurecode10date)) AS otherprocedurecode10Count,
COUNT(ISNULL(otherprocedurecode11, otherprocedurecode11date)) AS otherprocedurecode11Count,
COUNT(ISNULL(otherprocedurecode12, otherprocedurecode12date)) AS otherprocedurecode12Count,
COUNT(ISNULL(otherprocedurecode13, otherprocedurecode13date)) AS otherprocedurecode13Count,
COUNT(ISNULL(otherprocedurecode14, otherprocedurecode14date)) AS otherprocedurecode14Count,
COUNT(ISNULL(otherprocedurecode15, otherprocedurecode15date)) AS otherprocedurecode15Count,
COUNT(ISNULL(otherprocedurecode16, otherprocedurecode16date)) AS otherprocedurecode16Count,
COUNT(ISNULL(otherprocedurecode17, otherprocedurecode17date)) AS otherprocedurecode17Count,
COUNT(ISNULL(otherprocedurecode18, otherprocedurecode18date)) AS otherprocedurecode18Count,
COUNT(ISNULL(otherprocedurecode19, otherprocedurecode19date)) AS otherprocedurecode19Count,
COUNT(ISNULL(otherprocedurecode20, otherprocedurecode20date)) AS otherprocedurecode20Count,
COUNT(ISNULL(otherprocedurecode21, otherprocedurecode21date)) AS otherprocedurecode21Count,
COUNT(ISNULL(otherprocedurecode22, otherprocedurecode22date)) AS otherprocedurecode22Count,
COUNT(ISNULL(otherprocedurecode23, otherprocedurecode23date)) AS otherprocedurecode23Count,
COUNT(ISNULL(otherprocedurecode24, otherprocedurecode24date)) AS otherprocedurecode24Count
FROM dbo.xxxTmpClaims)x
CROSS APPLY(VALUES ( 'other procedure code 1 Count ', otherprocedurecode1Count ),
( 'other procedure code 2 Count ', otherprocedurecode2Count ),
( 'other procedure code 3 Count ', otherprocedurecode3Count ),
( 'other procedure code 4 Count ', otherprocedurecode4Count ),
( 'other procedure code 5 Count ', otherprocedurecode5Count ),
( 'other procedure code 6 Count ', otherprocedurecode6Count ),
( 'other procedure code 7 Count ', otherprocedurecode7Count ),
( 'other procedure code 8 Count ', otherprocedurecode8Count ),
( 'other procedure code 9 Count ', otherprocedurecode9Count ),
( 'other procedure code 10 Count', otherprocedurecode10Count),
( 'other procedure code 11 Count', otherprocedurecode11Count),
( 'other procedure code 12 Count', otherprocedurecode12Count),
( 'other procedure code 13 Count', otherprocedurecode13Count),
( 'other procedure code 14 Count', otherprocedurecode14Count),
( 'other procedure code 15 Count', otherprocedurecode15Count),
( 'other procedure code 16 Count', otherprocedurecode16Count),
( 'other procedure code 17 Count', otherprocedurecode17Count),
( 'other procedure code 18 Count', otherprocedurecode18Count),
( 'other procedure code 19 Count', otherprocedurecode19Count),
( 'other procedure code 20 Count', otherprocedurecode20Count),
( 'other procedure code 21 Count', otherprocedurecode21Count),
( 'other procedure code 22 Count', otherprocedurecode22Count),
( 'other procedure code 23 Count', otherprocedurecode23Count),
( 'other procedure code 24 Count', otherprocedurecode24Count))u(CodeColumn,CodeCount)
WHERE CodeCount > 0;
GO
DROP TABLE xxxTmpClaims;
December 29, 2016 at 8:22 am
Lowell, Luis Thanks
Your solutions work.
I am using the code for a one time test.. So it doesn't mater LOOP or OTHER .
Lowells solution is great ( in one SQl I am able to see all the results ) COOL !
THANKS FOR YOUR RESPONSES... LETS CLOSE HERE.. GOOD JOB!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply