2

I need help with a piece of code in a stored procedure that returns a dataset to a .NET app containing a variable set of columns--standard columns, plus user-defined columns. My code creates a temp table to hold the dataset and appends the custom columns. The issue I'm having is with the population of these custom columns. (By the way, my code needs to be compatible with Microsoft SQL Server 2008 R2).

I want to use dynamic SQL to put together an Update statement that updates the values of the custom columns in a temp table using SQL snippets stored in a lookup table.

For example, a user might want a custom field that contains an employee's name concatenated a certain way (e.g. "Last Name, First Name"), so the SQL snippet for the custom field would be "LastName + ', ' + FirstName" to concatenate the FistName and LastName fields in the temp table. That's easy. The hard part is when the custom field needs to get populated using a variable passed into the sproc, say to create a unique ID (e.g. The SQL snippet for the custom field is 'Inc_' + CAST(MONTH(@PayrollDate) AS VARCHAR)' to get something like 'Inc_10' if the input parameter @PayrollDate is '10/15/17').

Are still with me? Good, let's do some SQL.

Let's assume we have a temp table with our standard fields and then append a couple of custom fields, as follows (please don't pay attention to my alter table method; in my real sproc, I use dynamic SQL to append the fields, based on saved values in a lookup table).

-- 1. Create the temp table with the standard fields
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable

CREATE TABLE #MyTempTable (
EmpID VARCHAR(50),
FirstName VARCHAR(100),
LastName VARCHAR(100),
EarningsValue MONEY)

-- 2. Add sample earnings data to the temp table
INSERT INTO #MyTempTable (EmpID,FirstName,LastName,EarningsValue) VALUES('1234','Tom','Jones',525.50)
INSERT INTO #MyTempTable (EmpID,FirstName,LastName,EarningsValue) VALUES('4455','Mary','Smith',800.25)
INSERT INTO #MyTempTable (EmpID,FirstName,LastName,EarningsValue) VALUES('9876','Aaron','Lee',200.00)

-- 3. Add the custom fields to the temp table
ALTER TABLE #MyTempTable ADD EmployeeName VARCHAR(100)
ALTER TABLE #MyTempTable ADD BatchID VARCHAR(50)

I know of two methods to execute dynamic SQL to populate my custom fields. The method that works partway is the EXEC(@SQL) method, where you do something like this:

DECLARE @SQL VARCHAR(1000), @CustomField VARCHAR(50), @SQLSnippet VARCHAR(500)
SELECT @CustomField = 'EmployeeName', @SQLSnippet = 'LastName + '','' + FirstName'
SET @SQL = 'UPDATE #MyTempTable SET ' + @CustomField + ' = ' + @SQLSnippet
EXEC(@SQL)

If you were to do PRINT @SQL instead of EXEC(@SQL), you would get:

UPDATE #MyTempTable SET EmployeeName = LastName + ',' + FirstName

If I do SELECT * FROM #MyTempTable, I'll see my 'EmployeeName' custom field populated just fine.

The other dynamic SQL method to use is sp_executesql. However, if I try to do my UPDATE, I get NOTHING, because the it doesn't recognize @CustomField in my SET statement.

DECLARE @NSQL NVARCHAR(1000), @CustomField NVARCHAR(50), @SQLSnippet NVARCHAR(500)
SELECT @CustomField = 'EmployeeName', @SQLSnippet = 'LastName + '','' + FirstName'
SET @NSQL = 'UPDATE #MyTempTable SET @CustomField = @SQLSnippet'
EXECUTE sp_executesql @NSQL,N'@CustomField NVARCHAR(50), @SQLSnippet NVARCHAR(500)',@CustomField,@SQLSnippet

If I do PRINT @NSQL, I get UPDATE #MyTempTable SET @CustomField = @SQLSnippet. Obviously, that doesn't look good, but theoretically the values passed into the @CustomField and @SQLSnippet fields should work, shouldn't it?

At this point, you're thinking, "Steve, why don't you use the EXEC(@SQL) method and forget the sp_executesql nonsense?" Ah, but there's a catch.

In the case where I need to use dynamic SQL using a variable in the SQL snippet, the EXEC(@SQL) method fails. It complains with 'Must declare the scalar variable "@PayrollDate"'. Really? Yeah, really. Here, try it...

DECLARE @SQL VARCHAR(1000), @CustomField VARCHAR(50), @SQLSnippet VARCHAR(500), @PayrollDate SMALLDATETIME
SET @PayrollDate = '10/15/17'
SELECT @CustomField = 'BatchID', @SQLSnippet = '''INC_'' + CAST(MONTH(@PayrollDate) AS VARCHAR)'
SET @SQL = 'UPDATE #MyTempTable SET ' + @CustomField + ' = ' + @SQLSnippet
EXEC(@SQL)

From what I've researched online, only sp_executesql works with parameters in the dynamic SQL. However, I still don't get any results using it. Here's the dynamic SQL using sp_executesql:

DECLARE @NSQL NVARCHAR(1000), @CustomField NVARCHAR(50), @SQLSnippet NVARCHAR(500), @PayrollDate SMALLDATETIME
SET @PayrollDate = '10/15/17'
SELECT @CustomField = 'BatchID', @SQLSnippet = '''INC_'' + CAST(MONTH(@PayrollDate) AS VARCHAR)'
SET @NSQL = 'UPDATE #MyTempTable SET @CustomField = @SQLSnippet'
EXECUTE sp_executesql @NSQL, N'@CustomField NVARCHAR(50),@SQLSnippet NVARCHAR(500),@PayrollDate SMALLDATETIME',@CustomField,@SQLSnippet,@PayrollDate

If you do a SELECT * FROM #MyTempTable, the BatchID custom field is NULL. Grrrr!

So how do I get the blanking dynamic SQL to work properly? Do I use the EXEC(@SQL) method or the sp_executesql method, and how? Much appreciated!

Improve this question
5
  • 1
    Question to you, why are you trying to implement such a system? (I call is "code dictionary"). The reason I ask is that these systems are quite cumbersome and extremely hard to debug and maintain. User name formatting can be done in the front end rather easily, so is batch column. P.S. I have dealt with these type of systems before - not pretty. Commented Jul 12, 2017 at 6:01
  • Alex, as a team, we agreed the frontend would simply output the data table produced by the backend. I agree the business rules for concatenating columns and doing other transformations would be better served on the frontend, but I didn't think this wasn't going to be such a pain-in-the-you-know-what to implement on the backend. Commented Jul 12, 2017 at 15:23
  • @StevePantazis Look at my answer below. it will work for what your are asking. Commented Jul 12, 2017 at 17:28
  • *** ALERT! *** Hey everyone, I hate to be the bearer of bad news, but this method really opens you up to SQL injection attacks. I successfully employed the technique CuriousKid suggested and I got my sproc working. Then I decided to experiment, so I set @SQLSnippet = '''x''; DROP TABLE SVTest;'. Guess what? Even though it was supposed to update a field in my temp table to 'X', it also dropped a real table. That means an end-user saving a SQL snippet via a front-end app could do some real damage. One option is to have a blacklist and remove SQL keywords, but that probably won't pass a pen test. Commented Jul 13, 2017 at 0:26
  • To piggyback off my last comment, we're implementing a frontend solution that prevents end-users from saving snippets of SQL to our backend. Instead, we're giving end-users the equivalent of an authoring language to design business rules for doing things like concatenation and other extract field manipulation. E.g. they would save something like 'INC' + Month2 + Day2, which we would translate into a SQL snippet like '''INC'' + CAST(DAY(MONTH( + ''@PayrollDate'' + )) + CAST(DAY( + ''@PayrollDate'' + ) AS VARCHAR)', thus controlling what SQL gets saved for execution. Commented Jul 13, 2017 at 20:34

3 Answers 3

Reset to default
1

In order to Insert the data into a column, first you need to ADD the column (with its datatype) to the table and because ALTER and UPDATE cannot be in the same batch you will have to use sp_executesql twice. So below is the query which will accomplish what you want.

DECLARE @NSQL NVARCHAR(1000),@ALTSQL NVARCHAR(1000), @CustomField NVARCHAR(50), @CustomFieldDataType NVARCHAR(50), @SQLSnippet NVARCHAR(500), @PayrollDate SMALLDATETIME
SET @PayrollDate = '10/15/17'
SELECT @CustomField = 'BatchID', @CustomFieldDataType = ' NVARCHAR(50)', @SQLSnippet = '''INC_'' + CAST(MONTH(@PayrollDate) AS VARCHAR)'
SET @ALTSQL = 'ALTER TABLE #MyTempTable ADD '+ @CustomField + @CustomFieldDataType 
SET @NSQL = 'UPDATE #MyTempTable SET '+ @CustomField +' = '+ @SQLSnippet

EXECUTE sp_executesql @ALTSQL, N'@CustomField NVARCHAR(50), @CustomFieldDataType NVARCHAR(50)',@CustomField, @CustomFieldDataType
EXECUTE sp_executesql @NSQL, N'@CustomField NVARCHAR(50), @SQLSnippet NVARCHAR(500),@PayrollDate SMALLDATETIME',@CustomField, @SQLSnippet,@PayrollDate


SELECT * FROM #MyTempTable
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That worked. I'll just have to loop through the custom fields defined in my lookup table in a cursor and set both the field names and SQL snippets to the cursor variables and execute them one at a time. The construct of the @NSQL variable will have to be like you had:
@StevePantazis ,If the answer worked for you then please go ahead and accept this as answer and upvote if you want so it will be helpful for others in your situation to find the answer easily than reading every answer and comment. This way you can help community grow.
I accepted the answer, but I don't have enough "street cred" on StackOverflow, it seems, to do the upvote. It rejects me.
No worries. Atleast it will be on the top and will be helpful. Thank you!
1

RE: sp_executeSQL - you cannot pass a column name as a variable. If you want to the column name to come from a variable, you need dynamically construct the query string:

SET @NSQL = 'UPDATE #MyTempTable SET ' + @CustomField + ' = @SQLSnippet'
EXECUTE sp_executesql @NSQL, N'@PayrollDate SMALLDATETIME, @SQLSnippet NVARCHAR(500)',@PayrollDate, @SQLSnippet

What your original query was doing is assigning the values in variable @SQLSnippet to variable @CustomField.

Improve this answer

1 Comment

Just to add to that, with regard to sp_executesql Alex is completely correct. You can only use variables in the actualy dynamic sql text where you would be able to use a variable in static SQL. So you could say myColumn = @myPassedVariable but you couldn't say something like select * from test.dbo.@myPassedTableName. The parameterization, among other things, primarily gets around the risk of injection attacks. But if you could't do it in static SQL with a variable, then you have to concatenate the variable itself to the dynamic sql string you construct.
0 
try this    

DECLARE @SQL VARCHAR(1000), @CustomField VARCHAR(50), @SQLSnippet VARCHAR(500), @PayrollDate smalldatetime
    SET @PayrollDate = '10/15/17'
    SELECT @CustomField = 'BatchID', @SQLSnippet = '''INC_' + CAST(MONTH(@PayrollDate)  AS VARCHAR)+''''
    SET @SQL = 'UPDATE #MyTempTable SET ' + @CustomField + ' = ' + @SQLSnippet
    exec(@SQL)
Improve this answer

2 Comments

I get an "Invalid column name 'BatchID" error. CuriousKid below has the solution that works. It requires the use of sp_executesql, using the construct you suggested: "'UPDATE #MyTempTable SET ' + @CustomField + ' = ' + @SQLSnippet"
alter table code needs to be executed before executing the update statement

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.