SQLTeam.com | Weblogs | Forums

How to SELECT/Evaluate Column expression stored in the Table


#1

Hi Guys,

I have one problem in executing Dynamic SQL in one of my project.

I just want to extract one column from table where that column expression stored in table.

Example,
Id first_name last_name Full_name
1 X Y Case When First_name = 'x' then first_name+Last_name Else last_name end

when I select the above table using
SELECT id,first_name,last_name,full_name

I am expecting the result as

Id first_name last_name Full_name
1 X Y 'XX'

But the below is the answere which am getting,

Id first_name last_name Full_name
1 X Y Case When First_name = 'x' then first_name+Last_name Else last_name end

Actually, I using dynamic query to evaluate the expression for each rows for that column to acheive generic framework to avoid changing the SPs everytime on any changes.

Could you please help me out on this case.

Thanks,
GopiK


#2

Unless you plan on executing queries one row at a time, this isn't going to work out very well...
Using dynamic sql requires that you set a scalar variable, meaning it only holds a single variable at a time.

If one row at a time is what's desired, something like the following should work for you...

--==============================================================
-- populate some test data...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	id INT NOT NULL,
	first_name VARCHAR(20) NOT NULL,
	last_name VARCHAR(20) NOT NULL,
	full_name VARCHAR(200)
	);

INSERT #TestData (id, first_name, last_name, full_name) VALUES 
	(1, 'X', 'Y', 'CASE WHEN td.first_name = ''X'' THEN td.first_name + '' '' + td.last_name ELSE last_name END'),
	(2, 'A', 'B', 'CASE WHEN td.first_name = ''A'' THEN td.first_name + '' '' + td.last_name ELSE last_name END'),
	(3, 'C', 'D', 'CASE WHEN td.first_name = ''C'' THEN td.first_name + '' '' + td.last_name ELSE last_name END');

-- SELECT * FROM #TestData td;

--==============================================================
-- solution query...

DECLARE 
	@DeBug BIT = 0,
	@sql NVARCHAR(4000) = N'';

SELECT 
	@sql = CONCAT(N'
SELECT 
	td.id, 
	td.first_name, 
	td.last_name, 
	full_name = ', td.full_name, '
FROM
	#TestData td;')
FROM 
	#TestData td;

IF @DeBug = 1
BEGIN 
	PRINT(@sql);
END;
ELSE
BEGIN
	EXEC sys.sp_executesql @sql;
END;

#3

I suspect that something like the following is closer to what you're looking for but it still seems like a rather painful method to achieve the stated goal...

--==============================================================
-- populate some test data...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	id INT NOT NULL,
	first_name VARCHAR(20) NOT NULL,
	last_name VARCHAR(20) NOT NULL,
	full_name NVARCHAR(200)
	);

INSERT #TestData (id, first_name, last_name, full_name) VALUES 
	(1, 'X', 'Y', 'WHEN = ''X'' THEN td.first_name + '' '' + td.last_name'),
	(2, 'A', 'B', 'WHEN = ''A'' THEN td.first_name + '' '' + td.last_name'),
	(3, 'C', 'D', 'WHEN = ''C'' THEN td.first_name + '' '' + td.last_name');

-- SELECT * FROM #TestData td;

--==============================================================
-- solution query...

DECLARE 
	@DeBug BIT = 1,
	@sql NVARCHAR(4000) = N'';

SELECT 
	@sql = CONCAT(@sql, CHAR(13), CHAR(10), CHAR(9), CHAR(9),CHAR(9), CHAR(9), CHAR(9), td.full_name)
FROM 
	#TestData td;

SET @sql = CONCAT(N'
SELECT 
	td.id,
	td.first_name,
	td.last_name,
	full_name = CASE td.first_name',
	@sql, '
					ELSE td.last_name
				END
FROM 
	#TestData td;');

IF @DeBug = 1
BEGIN 
	PRINT(@sql);
END;
ELSE
BEGIN
	EXEC sys.sp_executesql @sql;
END;

If you want to "encapsulate" the CASE expression logic in a single location I'd strongly recommend using an inline table valued function. It would have all the "upsides" your looking for with none of the downsides. (see followup post)


#4

Create function...

CREATE FUNCTION dbo.tfn_FullName 
/* ============================================================
06/26/2017 Created Applies predetermined logic to build a 
			full_name from First_name and last_name.
============================================================ */
(
	@first_name varchar(20),
	@last_name varchar(20)
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
	SELECT 
		full_name = CASE @first_name
						WHEN 'X' THEN @first_name + ' ' + @last_name
						WHEN 'A' THEN @first_name + ' ' + @last_name
						WHEN 'C' THEN @first_name + ' ' + @last_name
						ELSE @last_name
					END;
GO

and again... The test data & solution query...

--==============================================================
-- populate some test data...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	id INT NOT NULL,
	first_name VARCHAR(20) NOT NULL,
	last_name VARCHAR(20) NOT NULL
	);

INSERT #TestData (id, first_name, last_name) VALUES 
	(1, 'X', 'Y'),
	(2, 'A', 'B'),
	(3, 'C', 'D'),
	(4, 'E', 'F'),
	(5, 'G', 'H');

-- SELECT * FROM #TestData td;

--==============================================================
-- solution query...
SELECT 
	td.id, 
	td.first_name, 
	td.last_name, 
	fn.full_name
FROM
	#TestData td
	CROSS APPLY dbo.tfn_FullName(td.first_name, td.last_name) fn;

#5

Other options would be to encapsulate the logic as a computed column in the table directly - or create a view with the defined logic in the view.

Unless you need to be able to calculate that value for different tables and/or sources of data then I would prefer using a computed column or view - but that is just my preference.


#6

I could very well be wrong but I got the impression that the OP wanted the option to set the logic at the row level for individual names AND wanted to be able to change that logic as needed w/o having to jump through a bunch of hoops.

That would eliminate computed columns right out of the gate. A view is certainly an option but offers no real advantage over using an iTVF.


#7

If the purpose is to change the Presentation of the data (rather than the data itself, via some post-processing SQL), and using completely dynamic code (rather than "Pick one from this list of FIXED templates") then my preference would be to do that client side. i.e. the language for the "expression" to be applied to the data would be whatever is used Client-Side.

I'd be a lot happier letting some JavaScript run amok on a web page attempting to apply some fancy-formatting ... than I would letting some unvalidated (and unvalidateABLE!) SQL code potentially run amok on the SQL Server itself.

"Little Bobby Tables" anyone?!


#8

Hi Guys,

I really appreciate your time and effort to put on my query and come up with different solutions. Jason solution definitely will be useful to me. In the meantime, I was able to achieve through CURSOR and not sure about the performance when we hit production.

the code looks like below,

DECLARE CurDQExpression CURSOR LOCAL FAST_FORWARD FOR 	
SELECT 	
[SRC_REF_ID],
[ID],
dq.[DQ_CHECK_EXPRESSION] --case expression 
FROM  RPOBI_Dev.dbo.[STG_SRC_REF_DATA_POINT] src WITH (NOLOCK)
	

OPEN CurDQExpression

FETCH NEXT FROM CurDQExpression INTO @iSRC_REF_ID,@iXREF_DQ_ID,@sDQ_CHECK_EXPRESSION

WHILE (@@FETCH_STATUS = 0)
BEGIN	
SET @sSql = '
INSERT INTO [RPOBI_Dev].DQ.[DQ_RESULTS] 
(
..

..
)
SELECT
'+CONVERT(VARCHAR(20),@ibatch_id)+ ' [BATCH_ID],
src.[STG_SRC_REF_ID] as [STG_SRC_REF_ID],
dq.[XREF_DQ_ID] as [ID],'+
@sDQ_CHECK_EXPRESSION+' as [IS_DQ_PASSED],'+
@sQuotes+CONVERT(VARCHAR(30),@dCreated_Date_time,121)+@sQuotes+'  as [CREATED_DATE_TIME],'+
@sQuotes+@screated_user+@sQuotes+' as [CREATED_USER]
FROM  RPOBI_Dev.dbo.[STG_SRC_REF_DATA_POINT] src WITH (NOLOCK)		
	WHERE src.[SRC_REF_ID] = '+CONVERT(VARCHAR(20),@iSRC_REF_ID)+
	' AND dq.[XREF_DQ_ID] = '+CONVERT(VARCHAR(20),@iXREF_DQ_ID)
	
EXEC(@sSql)

	 FETCH NEXT FROM CurDQExpression INTO @iSRC_REF_ID,@iXREF_DQ_ID,@sDQ_CHECK_EXPRESSION
END

CLOSE CurDQExpression
DEALLOCATE CurDQExpression;	

Please add if you have any thoughts on this approach.


#9

General assumption would be that cursor is likely to be a bottleneck. Not always though ...

Just looking at your code I wonder if there is an opportunity. You basically have dynamic SQL for

DO
    Get a row
    SQL = "INSERT INTO ... SELECT ..."
    EXEC SQL
LOOP

I wonder if there is another route:

DO
    Get a row
    SQL = SQL + " UNION ALL " + "SELECT ..."
LOOP
EXEC SQL

i.e. you build a composite SQL variable with ALL (or "enough for a decent sized batch") and THEN execute it.

Once you get to that point it then becomes possible to execute the "LOOP" as a single SQL statement, concatenating all the ROWS to make the composite SQL statement. Which you then execute (just the once)

This will definitely break down on huge batch sizes, so if you get to that size you will need to CHUNK it, but for each batch (i.e. of a reasonable size) a single insert statement that inserts, say, 1,000 rows will operate much faster than 1,000 iterations around a cursor loop making one-row inserts each iteration. I would expect it to be 10x faster, and it might even be 100x faster.


#10

Thanks Kristen for your suggestions.