Return a Result from Dynamic SQL SELECT

Question

I have a CASE statement in which I first need to evaluate the value of @RequiredLocal. If that = 1, THEN I need to set the value of @SQLString = 'SELECT CASE WHEN DATALENGTH([' + @SourceColumnNameLocal + ']) > 0 ' + cast(@MinLengthLocal as varchar(100)) + ' THEN ''PASS'' ELSE ''FAIL'' END'

I need to capture 'PASS' or 'FAIL' when the @SQLString is executed. Here is what I have attempted:

        SELECT CASE WHEN @RequiredLocal = 1 THEN
            @SQLString = 'SELECT CASE WHEN DATALENGTH([' + @SourceColumnNameLocal + ']) > 0 ' + cast(@MinLengthLocal as varchar(100)) + ' THEN ''PASS'' ELSE ''FAIL'' END'
        ELSE @SQLString
        END
        SELECT @RequiredResult = EXECUTE sp_executesql @SQLString

Here is another way that I am trying this:

       If @RequiredLocal = 1
        begin
         set @SQLString =  'SELECT CASE WHEN DATALENGTH([' + @SourceColumnNameLocal + ']) > 0 ' + cast(@MinLengthLocal as varchar(100)) + ' THEN ' + @RequiredResult +  ''' = PASS'' '' ELSE ''' + @RequiredResult + ''' = FAIL ''' + 'END'''
        end
        EXEC sp_executesql @SQLString, @RequiredResult output

How can I accomplish this?

Thank you for your help.

cdun2

Answer 1

I'm afraid that I don't understand what this code is supposed to achieve. Does it really have to be dynamic SQL in the first place? I see that you have a variable that holds a column name, but you can deal with this with a CASE expression:

   CASE @SourceColumnNameLocal
      WHEN 'thiscol' THEN thiscol
      WHEN 'thatcol' THEN thatcol
      WITH 'thisothercol' THEN thisothercol
   END AS somecol

Stick this in a CTE, so you don't have repeat it all over the query.

If you are going to write dynamic SQL, use quotename for the column name, and don't inline parameters that can be parameters. For instance:

SELECT @sql = 'SELECT @cnt = COUNT(*) ' +
              ' FROM tbl ' +
              ' WHERE ' + quotename(@col) + ' = @val'
EXEC sp_executesql @sql, N'@val int, @cnt int OUTPUT', @val, @cnt OUTPUT

When writing dynamic SQL it is imperative to exercise strict discipline with formatting to increase readability. Although the best bet for good readability is to not use dynamic SQL at all!               

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

CASE is an EXPRESSION, not a STATEMENT. Expressions return a scalar value; they do not do control flow. 

SET @SQLString
   = CASE WHEN @Required_Local = 1 
          THEN 'SELECT CASE WHEN DATALENGTH([' 
          + @Source_Column_Name_Local 
          + ']) > 0 ' 
          + CAST(@Min_Length_Local AS VARCHAR (100)) 
          + ' THEN ''PASS'' ELSE ''FAIL'' END'
        ELSE @SQLString
        END;

But the real point is that using dynamic SQL is how you tell the world that you have no idea how to do your job. You have to wait until run time and have some unknown user to do your job.  

What is the actual problem? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

In an ETL system, I need to examine the contents of various source table columns to insure that the data conforms to some basic rules. Each column will be checked for every record. The tables that require examination are created 'on the fly' in the ETL process. Each table represents a client (customer). When new client is adopted, the metadata about their source data is stored in metadata tables.

The code that I posted represents an attempt to examine the data of some column, and determine if the data in it passes/fails the test for 'Required'.

Part of the rationale behind this is to avoid having to create new connection managers, and specialized processes every time a new client comes on board. The problem this creates seems to be that dynamic sql has to be used in order to examine any client's data in a generic way.

Answer 4

Here is an example of what I need to do:

I have a table named UDMPilot.dbo.GEHADataTEMP:

UDMPilot.dbo.GEHADataTemp
(

[Claim number] varchar(2000)
,[Claim Paid Date] varchar(2000)
,[Line Item Counter] varhar(2000)

)

This table is created 'on the fly', and is designed to be a staging table.

I have a View that returns the names of the columns expected, and the test to be performed against the colums. The data returned from it looks like this:

SELECT SourceColumnName,[Required],Required_Test FROM SourceTargetColumnMapDetail
WHERE SourceColumnName in ('Claim Number','Claim Paid Date','Line Item Counter')

That query returns a result that looks like this:

SourceColumnName Required Required_Test
claim number 1 SELECT CASE WHEN DATALENGTH([claim number]) > 1 THEN 'PASS' ELSE 'FAIL' END
Claim Paid Date 0 No Test
Line item Counter 1 SELECT CASE WHEN DATALENGTH([Line item Counter]) > 1 THEN 'PASS' ELSE 'FAIL' END

'Required' indicates whether or not data is required for the column. For each value of 'Claim Number', and 'Line item Counter', I need to return 'PASS' or 'FAIL' based on the 'Required_Test' to be performed.

Does that make sense?

Answer 5

I am not going to give you an answer to your problem, because I still can't put the pieces together.

However, if you are working with tables created on the fly, you have since long left the realm relational database were created for, and you are on your own in the wilderness. To work with the setup you, requires very good understanding of dynamic SQL and a very structured way to approach things.

Personally, if I would find myself in a situation where I were to work with tables created on the fly, I would administer the whole thing from Perl or C#. All you really do is string manipulation, and T-SQL is not good at string manipulation. You can easily get lost in a myriad of nested quotes.

On my web site, I have an article about dynamic SQL,
http://www.sommarskog.se/dynamic_sql.html
I strongly recommend that you read it to learn best practice how to use sp_executesql and how to pass parameters, and the use of quotename().

I also recommend that you study the section on dynamic SQL in my article on dynamic search conditions,
http://www.sommarskog.se/dyn-search-2008.html#dynsql
to see more examples.

---

Erland Sommarskog, SQL Server MVP, [e-mail]