October 31, 2008 at 2:33 pm
In my table, I have count rows within a segmentID who have a formula, that formula will create the Dynamic SQL...
As you can see in my UPDATE STATEMENT I have moved every value up and down, inside and out...
I don't even know how to explain it, because I don't understand how SQL Server brings into memory a SELECT or FROM Clause...
basically I can't find the pattern to populate my variable @SQL with the first part of the UPDATE STATEMENT, i.e. UPDATE TABLENAME SET.... AND then depending upon how many rows are their for a segmentid, the @SQL + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + SourceFormulaStep2 + ' AS ' + DataType + ')' should be done that many times; thus building me an update statement that has the UPDATE Tablename plus SET howevermany rows and the WHERE CLAUSE at the end...
I'm just now sure where to place things or how to word the CASE STATEMENTS... can you believe I did an Amoritization with this a few postings back, and can't get this...doh
Thanks
Everything is below for you to try this out...
DECLARE @DynamicColumnsTABLE
--CREATE TABLE DynamicColumns_JASDEVTESTDELETE
(
RowIDINT
,ReportDateDateTime
,SegmentIDINT
,SegmentGroupIDINT
,SegmentGroupVARCHAR(100)
,SegmentGroupSortFLOAT(8)
,SegmentHeaderVARCHAR(250)
,SegmentHeaderSortFLOAT(8)
,SourceFormulaVARCHAR(500)
,SourceFormulaStep2VARCHAR(500)
,CommitmentVARCHAR(3000)
,SUBTRACTINT
,ExcludeFromReportsBIT
,STEP2BIT
,SegmentTableVARCHAR(500)
,SegmentFormulaVARCHAR(1000)
,DataTypeVARCHAR(100)
,LengthCHAR(5)
,DestinationColumnVARCHAR(50)
,ColumnOrderINT
,CNTINT
,[SQL]VARCHAR(1950)
PRIMARY KEY (RowID, SegmentID, STEP2)
)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (1,50, NULL, 'BorrowerName', 'VARCHAR', 50, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (2,50, NULL, 'Bank', 'INT', '', 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (3,50, NULL, 'Obligor', 'CHAR', 12, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (4,50, NULL, 'Obligation', 'CHAR', 12, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (5,54, NULL, 'GLCompanyID', 'CHAR', 4, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (6,54, NULL, 'GLCostCenter', 'CHAR', 7, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (7,54, NULL, 'GLAccountNbr', 'CHAR', 6, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (8,54, NULL, 'Balance', 'MONEY', '', 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (9,78, NULL, 'Bank', 'INT', '', 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (10,78, NULL, 'Obligor', 'CHAR', 12, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (11,78, NULL, 'Obligation', 'CHAR', 12, 4, NULL, 1)
INSERT INTO @DynamicColumns
(ROWID, SEGMENTID, SOURCEFORMULASTEP2, DESTINATIONCOLUMN, DATATYPE, LENGTH, CNT, SQL, STEP2)
VALUES (12,78, NULL, 'GLCompanyID', 'CHAR', 4, 4, NULL, 1)
DECLARE @iCNTAS INT
DECLARE @iCNTWATCHAS INT
DECLARE @SQLAS VARCHAR(8000)
DECLARE @SEGIDAS INT
DECLARE @PRIORIDAS INT
DECLARE @TABLENAMEAS NVARCHAR(50)
DECLARE @UPDATEAS VARCHAR(20)
SET @iCNT= 1
SET @UPDATE= 'UPDATE ' + 'TESTTABLE '
UPDATE @DynamicColumns
SET
-- START THE COUNTER
@ICNT =CASE
WHEN [SEGMENTID] = @PRIORID
-- AT MY NEXT RECORD WITHIN A SEGMENTID...
THEN @ICNT + 1
-- I'M AT MY FIRST NEW RECORD
ELSE 1
END
,
-- AT ALL FIRST RECORDS CLEAR OUT @SQO
@SQL =CASE WHEN @ICNT <= 1 THEN '' END
,
[SQL] =
CASE
WHEN @ICNT = [CNT]
THEN
--CASE
-- NOT GETTING TRUE HERE...
--WHEN [SEGMENTID] = @PRIORID
--THEN
ISNULL(@SQL,'DAMMIT') + 'WHERE SegmentID = ' + CAST([SEGMENTID] AS VARCHAR(20)) -- + CHAR(13)
--ELSE
--'DOH2'
--END
ELSE
'DOH'
END
,
@SQL = --@SQL + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + SourceFormulaStep2 + ' AS ' + DataType + ')'
CASE
-- FIRST ROW IS NOT TRUE SO GO TO ELSE
WHEN [SEGMENTID] = @PRIORID
THEN
--CASE
--WHEN @ICNT > 1
--THEN @UPDATE + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + SourceFormulaStep2 + ' AS ' + DataType + ')' + CHAR(13)
--ELSE
@SQL + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + SourceFormulaStep2 + ' AS ' + DataType + ')' + CHAR(13)
--END
ELSE
--CASE
--WHEN @ICNT = 1
--THEN
@UPDATE + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + SourceFormulaStep2 + ' AS ' + DataType + ')' + CHAR(13)
--END
END
,
-- CLEAR OUT AFTER DONE WITH SEGMENTID
@PRIORID = [SEGMENTID]
FROM @DynamicColumns DC
WHERE STEP2 = 1 -- MEANS THAT THE SourceFormulaStep2 HAS A FORMULA IN IT
SELECT
DC.SegmentID
,DC.SourceFormulaStep2
,DC.DestinationColumn
,DC.DATATYPE
,DC.LENGTH
,DC.CNT
,DC.[SQL]
,DC.STEP2
FROM @DynamicColumns DC
October 31, 2008 at 4:36 pm
Think you're looking for something like this. One of the biggest issues is you forgot something vital... something that rhymes with "Flustered Windex".
PRIMARY KEY CLUSTERED(RowID, SegmentID, STEP2)
[font="Courier New"] DECLARE @iCNT AS INT
DECLARE @iCNTWATCH AS INT
DECLARE @SQL AS VARCHAR(8000)
DECLARE @SEGID AS INT
DECLARE @PRIORID AS INT
DECLARE @TABLENAME AS NVARCHAR(50)
DECLARE @UPDATE AS VARCHAR(20)
DECLARE @ClearSQL INT
SET @iCNT = 1
-- SET @PriorID = 50
SET @UPDATE = 'UPDATE ' + 'TESTTABLE '
UPDATE #DynamicColumns
SET
-- START THE COUNTER
@ICNT = CASE WHEN [SEGMENTID] = @PRIORID THEN @ICNT + 1 ELSE 1 END,
@ClearSQL = CASE WHEN @ICNT <= 1 THEN 1 ELSE 0 END,
[SQL] = CASE WHEN @ICNT = [CNT]
THEN ISNULL(@SQL,'D####T') + 'WHERE SegmentID = ' + CAST([SEGMENTID] AS VARCHAR(20))
ELSE
'DOH'
END
,
@SQL = CASE WHEN [SEGMENTID] = @PRIORID
THEN
CASE WHEN @ClearSql = 1 THEN '' ELSE @SQL END + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + ISNULL(SourceFormulaStep2,'') + ' AS ' + DataType + ')'-- + CHAR(13) END
ELSE
@UPDATE + ' SET ' + [DESTINATIONCOLUMN] + ' = CAST(' + ISNULL(SourceFormulaStep2,'') + ' AS ' + DataType + ')' + CHAR(13)
END,
@PRIORID = [SEGMENTID]
FROM #DynamicColumns DC WITH (INDEX(0))
WHERE STEP2 = 1 -- MEANS THAT THE SourceFormulaStep2 HAS A FORMULA IN IT[/font]
October 31, 2008 at 9:47 pm
ah, is that why you used a temp table rather than the table variable, or does it matter...
Looks good, thanks, I will test it out now... and let you know...
John
October 31, 2008 at 10:09 pm
Yerp. I actually gave an example earlier where I used a table variable, but it doesn't support the WITH INDEX clause, and this actually served as a reminder that I needed to go back and make a note on that one.
October 31, 2008 at 10:33 pm
There are certain times where you can get away without using the table hint to force the index. I've done it in certain functions using table variables. You can force the use of the index with a WHERE clause something like WHERE PKCol > 0 or somesuch. Obviously, it's better and safer if you can force the index using a table hint. As Seth stated, that's why it's better to use a TempTable rather than a Table Variable for such things.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply