Friday, March 01, 2013 9:35 PM
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'
SELECT @RequiredResult = EXECUTE sp_executesql @SQLString
Here is another way that I am trying this:
If @RequiredLocal = 1
set @SQLString = 'SELECT CASE WHEN DATALENGTH([' + @SourceColumnNameLocal + ']) > 0 ' + cast(@MinLengthLocal as varchar(100)) + ' THEN ' + @RequiredResult + ''' = PASS'' '' ELSE ''' + @RequiredResult + ''' = FAIL ''' + 'END'''
EXEC sp_executesql @SQLString, @RequiredResult output
How can I accomplish this?
Thank you for your help.
- Edited by cdun2 Friday, March 01, 2013 9:51 PM
Saturday, March 02, 2013 11:35 AM
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:
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, email@example.com
- Proposed As Answer by Kalman TothMicrosoft Community Contributor, Moderator Saturday, March 02, 2013 11:55 AM
Saturday, March 02, 2013 2:09 PM
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(['
+ ']) > 0 '
+ CAST(@Min_Length_Local AS VARCHAR (100))
+ ' THEN ''PASS'' ELSE ''FAIL'' 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
Monday, March 04, 2013 8:21 PM
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.
Monday, March 04, 2013 10:57 PM
Here is an example of what I need to do:
I have a table named 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?
Tuesday, March 05, 2013 9:17 AM
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,
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,
to see more examples.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
- Marked As Answer by Iric WenModerator Tuesday, March 12, 2013 7:32 AM