Benötigen Sie Hilfe beim Aktualisieren einer Tabelle mit variablen Spalten mit dynamischem SQL

Ich benötige Hilfe bei einem Codeabschnitt in einer gespeicherten Prozedur, der einen Datensatz an eine .NET-App zurückgibt, die einen variablen Satz von Spalten enthält – Standardspalten plus benutzerdefinierte Spalten. Mein Code erstellt eine temporäre Tabelle, die das Dataset enthält, und hängt die benutzerdefinierten Spalten an. Das Problem, das ich habe, ist mit der Auffüllung dieser benutzerdefinierten Spalten. (Mein Code muss übrigens mit Microsoft SQL Server 2008 R2 kompatibel sein).

Ich möchte dynamisches SQL verwenden, um eine Update-Anweisung zusammenzustellen, die die Werte der benutzerdefinierten Spalten in einer temporären Tabelle mithilfe von SQL-Ausschnitten aktualisiert, die in einer Nachschlagetabelle gespeichert sind.

Ein Benutzer möchte beispielsweise ein benutzerdefiniertes Feld, das den Namen eines Mitarbeiters auf eine bestimmte Art und Weise enthält (z. B. "Nachname, Vorname"), so dass der SQL-Ausschnitt für das benutzerdefinierte Feld "Nachname + ', ' + Vorname" lautet, um Verketten Sie die Felder FistName und LastName in der temporären Tabelle. Das ist leicht. Der schwierige Teil ist, wenn das benutzerdefinierte Feld mit einer an den Sproc übergebenen Variablen gefüllt werden muss, um beispielsweise eine eindeutige ID zu erstellen (z. B. das SQL-Snippet für das benutzerdefinierte Feld ist 'Inc_' + CAST(MONTH(@PayrollDate) AS VARCHAR) ', um etwas wie 'Inc_10' zu erhalten, wenn der Eingabeparameter @PayrollDate '10/15/17' ist).

Bist du noch bei mir? Gut, lass uns etwas SQL machen.

Nehmen wir an, wir haben eine temporäre Tabelle mit unseren Standardfeldern und fügen dann ein paar benutzerdefinierte Felder wie folgt hinzu (bitte beachten Sie meine alter table-Methode nicht; in meinem echten Sproc verwende ich dynamisches SQL, um die Felder anzuhängen, basierend auf gespeicherten Werten in einer Nachschlagetabelle).

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

Ich kenne zwei Methoden, um dynamisches SQL auszuführen, um meine benutzerdefinierten Felder zu füllen. Die Methode, die teilweise funktioniert, ist die EXEC(@SQL)-Methode, bei der Sie Folgendes tun:

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)

Wenn Sie PRINT @SQL anstelle von EXEC(@SQL) ausführen würden, erhalten Sie:

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

Wenn ich SELECT * FROM #MyTempTable auswähle, wird mein benutzerdefiniertes Feld "EmployeeName" gut ausgefüllt.

Die andere zu verwendende dynamische SQL-Methode ist sp_executesql. Wenn ich jedoch versuche, mein UPDATE durchzuführen, erhalte ich NICHTS, da @CustomField in meiner SET-Anweisung nicht erkannt wird.

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

Wenn ich @NSQL DRUCKE, erhalte ich UPDATE #MyTempTable SET @CustomField = @SQLSnippet. Das sieht natürlich nicht gut aus, aber theoretisch sollten die an die Felder @CustomField und @SQLSnippet übergebenen Werte funktionieren, oder?

An diesem Punkt denken Sie: "Steve, warum verwenden Sie nicht die Methode EXEC(@SQL) und vergessen den Unsinn von sp_executesql?" Ah, aber es gibt einen Haken.

Wenn ich dynamisches SQL mit einer Variablen im SQL-Snippet verwenden muss, schlägt die EXEC(@SQL)-Methode fehl. Es beschwert sich mit 'Muss die Skalarvariable "@PayrollDate" deklarieren'. "Ja wirklich?" Ja wirklich. Hier, versuch es...

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)

Nach dem, was ich online recherchiert habe, funktioniert nur sp_executesql mit Parametern in der dynamischen SQL. Allerdings bekomme ich damit immer noch keine Ergebnisse. Hier ist die dynamische SQL mit 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

Wenn Sie SELECT * FROM #MyTempTable ausführen, ist das benutzerdefinierte BatchID-Feld NULL. Grrrr!

Wie kann ich also das dynamische Ausblenden von SQL dazu bringen, richtig zu funktionieren? Verwende ich die EXEC(@SQL)-Methode oder die sp_executesql-Methode und wie? Sehr geschätzt!