CODEWITHSUNDEEP
sql-serversql-server-2008
gurpreet singh
gurpreet singh

Reputation: 47

Stored Procedure to construct sql query - SQL

I have 2 tables "Tbl1" which contains 3 rows as shown in table below

  ColNames
  *********
   Name
   SurName
   DOB

My second table "tbl2" Contains the info related to Name, SurName and DOB as shown below:

Name        SurName      DOB
***********************************
SAM         billing      22/11/2000
NULL        petres       22/11/2000
BILL        Jones        NULL
Dave        NULL         22/11/2000

Hypothetically, if Name and DOB are chosen from tbl1, I would like to extract the NOT NULL values from the second table.

JOINS is NOT an option here, so please dont suggest that. sorry....

What I have done thus far is built a stored procedure that has saved Name and DOB as rows from tbl1 into a #temp_table.

I want to now build some sort of stored procedure that would build a query for me that will select "NOT NULL" values from tbl2.

ALTER PROCEDURE spShowRuleValues
AS
BEGIN
SELECT 'SELECT * FROM ' + 'tbl2' + ' WHERE '+
    stuff((SELECT ', ' + Header FROM #temp_table
    FOR Xml path('')
    ),1,2,'') + ' IS NOT NULL ';
END

execute spShowRuleValues

But, upon executing this stored procedure, I am only able to develop a query which is:

`SELECT * FROM tbl2 WHERE Name, DOB IS NOT NULL `

How can I build the query in a way that it builds up as:

SELECT * FROM tbl2 WHERE Name IS NOT NULL AND
DOB IS NOT NULL

and then to execute this query to get the results from tbl1 excluding the NOT NULL values.

Please feel free to suggest another approach that i can use to construct desired query. Thank you for your time and consideration in advance.

Upvotes: 2

Views: 66

Answers (1)

EzLo
EzLo

Reputation: 14209

Try changing your SELECT ... FOR XML PATH a little bit.

ALTER PROCEDURE spShowRuleValues
AS
BEGIN

    DECLARE @WhereCondition VARCHAR(MAX) = STUFF(
        (
            SELECT 'AND T.' + QUOTENAME(Header) + ' IS NOT NULL' FROM #temp_table FOR Xml path('')
        ),1,4,'');

    SELECT 'SELECT * FROM tbl2 AS T WHERE '+ @WhereCondition

END

Upvotes: 1

Related Questions