Entire SQL query as variable in stored procedure

Question

I have several queries to be run on a customer table. Each of these queries identifies possible data quality issues in the customer table. For example, one query is to find if a customer's age is < 18, which cannot / should not be the case.

I have a cursor that fetches each of these queries one by one into a variable.

I am struggling to run each of these queries and aggregate the results.

DECLARE c1 CURSOR FOR SELECT [ruleQuery] FROM [dbo].[SQL_DataQuality_Rules]
OPEN rules_cursor    
FETCH NEXT FROM rules_cursor INTO @rulequery
WHILE @@FETCH_STATUS = 0
BEGIN
    --Help needed here to execute @rulequery which would be something like
    --SELECT sum(case when age < 18 then 1 else 0 end) as 'Fail' FROM...
FETCH NEXT FROM rules_cursor INTO @rulequery
END
CLOSE c1
DEALLOCATE c1

Since I would be calling this procedure from excel, I would like to have all the fail counts in one table in excel.

Is this what you're looking for? sqlusa.com/bestpractices/dynamicsql — Bart Enkelaar
– Bart Enkelaar, Commented Apr 8, 2015 at 12:29
Wouldn't it be better to put contraints on the table so that these conditions cannot be put into the tables? — HLGEM
– HLGEM, Commented Apr 8, 2015 at 15:24

TT. · Accepted Answer · 2015-04-08 15:25:36Z

Suppose each of your queries has as a result a table with two columns: title, fail. Your queries only have one column (fail) but you wouldn't know what is failing from that output. Change to have them return a title column as well. What you do:

Create a table variable or a temporary table with those same columns
Insert the output from executing the SQL Queries into the table variable or temporary table
After the loop, select the contents of the table variable or temporary table as the result of your Stored Procedure.

Example:

DECLARE @res TABLE(title VARCHAR(256), fail INT);

DECLARE c1 CURSOR FOR SELECT [ruleQuery] FROM [dbo].[SQL_DataQuality_Rules];
OPEN rules_cursor;
DECLARE @rulequery VARCHAR(MAX);
FETCH NEXT FROM rules_cursor INTO @rulequery;
WHILE @@FETCH_STATUS = 0
BEGIN
  --Query like: SELECT 'Age check' AS title, sum(case when age < 18 then 1 else 0 end) as 'Fail' FROM...
  INSERT INTO @res(title,fail) EXEC (@rulequery);
  FETCH NEXT FROM rules_cursor INTO @rulequery;
END
CLOSE c1;
DEALLOCATE c1;

SELECT*FROM @res;

Pankaj Sharma · Accepted Answer · 2015-04-08 13:20:46Z

0

Following code inside the cursor will do your job. but dont forget to declare @rulequery variable

IF LEN(LTRIM(RTRIM(@rulequery)))>0
EXEC(@rulequery)

answered Apr 8, 2015 at 13:20

Pankaj Sharma

513 bronze badges

Collectives™ on Stack Overflow

Entire SQL query as variable in stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related