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.
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;
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)
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;
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.
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.
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.
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.
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.