I'm trying to convert a stored procedure into a multi-statement table valued function and i've come across the following piece of code inside a while loop, that changes the contents of @fields on every iteration.:
INSERT INTO @BarrierData (@fields) VALUES (@values)
I know for a fact that all possible values that could feed into the @fields variable represent a column defined in the table to be returned by the function.
However, I get the following error:
Incorrect syntax near '@fields'. Expecting '.', ID, PSEUDOCOL or QUOTED_ID.
So, for each iteration of the while loop, let's say that @fields = a on the first iteration, 'b' on the second iteration and so on, i'd be expecing it to insert @values into column 'a' on the first run, insert @values on column 'b' on the second run, and so on.
Is there a way to make this work?
I did not write the original SP, and while I somewhat understand what it does, my knowledge of SQL is rather limited to rewrite it from scratch
EDIT: see below the code for the original procedure, and an example string that may be passed to it.
SP:
CREATE TABLE #prodvalues
(
IndexDef varchar(15)
, BarrierType varchar(15)
, SubType varchar(15)
, BarrierLevel float
, Strike float
, PayOnBarrier char(1)
, PayOnStrike char(1)
, PayOnStrike2 char(1)
, CrossDate date
, StartDate date
, EndDate date
, [Lower] float
, [Upper] float
, Payoff float
)
SET @proddata = REPLACE(@proddata,'"','')
IF LEFT(@proddata, 10) = '|ASSETBAR|'
BEGIN
DECLARE @proddata2 varchar(max)
IF CHARINDEX('AssetBarrList', @proddata) > 0
BEGIN
SET @proddata2 = SUBSTRING(@proddata, CHARINDEX('AssetBarrList', @proddata), LEN(@proddata))
SET @proddata2 = SUBSTRING(@proddata2, 1, LEN(@proddata2) - CHARINDEX(']', REVERSE(@proddata2)) +1)
SET @proddata = REPLACE(REPLACE(@proddata, @proddata2, ''), '|ASSETBAR|', '|EBARR|')
SET @proddata = SUBSTRING(@proddata,CHARINDEX('(',@proddata),LEN(@proddata))
SET @proddata = REPLACE(REPLACE(@proddata,'(',''),')','')
DECLARE @fields varchar(max) = ''
DECLARE @values varchar(max) = ''
WHILE LEN(@proddata) > 0
BEGIN
DECLARE @temp varchar(100) = LEFT(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)))
SET @fields = @fields + ',' + REPLACE(LEFT(@temp, CHARINDEX(':', @temp)-1),' ','')
SET @values = @values + ',''' + REPLACE(SUBSTRING(@temp, CHARINDEX(':', @temp)+1,LEN(@temp)),'"','') + ''''
SET @proddata = SUBSTRING(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)),LEN(@proddata))
END
SET @fields = SUBSTRING(@fields,2,LEN(@fields))
SET @values = SUBSTRING(@values,2,LEN(@values))
EXEC ('INSERT INTO #prodvalues (' + @fields + ') VALUES (' + @values + ')')
SET @proddata2 = REPLACE(@proddata2, 'AssetBarrList', '')
WHILE @proddata2 LIKE '%|ASSBARDEF|%'
BEGIN
DECLARE @tmpproddata varchar(max) = @proddata2
DECLARE @nxtpos int = CHARINDEX('|ASSBARDEF|', @proddata2, 10)
IF @nxtpos = 0 SET @nxtpos = CHARINDEX(')', @proddata2)
SET @proddata = SUBSTRING(@proddata2, 1, @nxtpos)
SET @proddata = SUBSTRING(@proddata,CHARINDEX('(',@proddata),LEN(@proddata))
SET @proddata = REPLACE(REPLACE(@proddata,'(',''),')','')
SET @fields = ''
SET @values = ''
WHILE LEN(@proddata) > 0
BEGIN
SET @temp = LEFT(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)))
DECLARE @fld varchar(max) = REPLACE(LEFT(@temp, CHARINDEX(':', @temp)-1),' ','')
IF @fld = 'BarrType' SET @fld = 'BarrierType'
SET @fields = @fields + ',' + @fld
SET @values = @values + ',''' + REPLACE(SUBSTRING(@temp, CHARINDEX(':', @temp)+1,LEN(@temp)),'"','') + ''''
SET @proddata = SUBSTRING(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)),LEN(@proddata))
END
SET @fields = SUBSTRING(@fields,2,LEN(@fields))
SET @values = SUBSTRING(@values,2,LEN(@values))
print 'INSERT INTO #prodvalues (' + @fields + ') VALUES (' + @values + ')'
EXEC ('INSERT INTO #prodvalues (' + @fields + ') VALUES (' + @values + ')')
SET @proddata2 = REPLACE(@proddata2, @tmpproddata, '')
--print @proddata2
END
END
END
IF LEFT(@ProdData, 7) = '|EBARR|'
BEGIN
SET @proddata = SUBSTRING(@proddata,CHARINDEX('(',@proddata),LEN(@proddata))
SET @proddata = REPLACE(REPLACE(@proddata,'(',''),')','')
SET @fields = ''
SET @values = ''
WHILE LEN(@proddata) > 0
BEGIN
SET @temp = LEFT(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)))
SET @fields = @fields + ',' + REPLACE(LEFT(@temp, CHARINDEX(':', @temp)-1),' ','')
SET @values = @values + ',''' + REPLACE(SUBSTRING(@temp, CHARINDEX(':', @temp)+1,LEN(@temp)),'"','') + ''''
SET @proddata = SUBSTRING(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)),LEN(@proddata))
END
SET @fields = SUBSTRING(@fields,2,LEN(@fields))
SET @values = SUBSTRING(@values,2,LEN(@values))
EXEC ('INSERT INTO #prodvalues (' + @fields + ') VALUES (' + @values + ')')
END
SELECT * FROM #prodvalues
Example string:
|ASSETBAR| (IndexDef: PayOnStrike:"N" PayOnStrike2:"N" Strike:0.0 Payoff:0.0 Lower:0.0 Upper:0.0 AssetBarrList:[|ASSBARDEF| (StartDate:"20120619" EndDate:"20190619" IndexDef:[1mEUReur1m] BarrType:"EUS" SubType:"DOWN_IN" PayOnStrike:"Y" PayOnStrike2:"N" Strike:0.0091 Payoff:0.0 Upper:0.0 Lower:0.003 )] )
I "ported" this code to table function:
DROP FUNCTION dbo.FN_PRODDATA_GET
GO
CREATE FUNCTION dbo.FN_PRODDATA_GET(@proddata NVARCHAR(MAX))
RETURNS @prodvalues TABLE
(
IndexDef varchar(15)
, BarrierType varchar(15)
, SubType varchar(15)
, BarrierLevel float
, Strike float
, PayOnBarrier char(1)
, PayOnStrike char(1)
, PayOnStrike2 char(1)
, CrossDate date
, StartDate date
, EndDate date
, [Lower] float
, [Upper] float
, Payoff float
)
AS
BEGIN
DECLARE @t TABLE (fieldName sysname, value nvarchar(max))
SET @proddata = REPLACE(@proddata,'"','')
IF LEFT(@proddata, 10) = '|ASSETBAR|'
BEGIN
DECLARE @proddata2 varchar(max)
IF CHARINDEX('AssetBarrList', @proddata) > 0
BEGIN
SET @proddata2 = SUBSTRING(@proddata, CHARINDEX('AssetBarrList', @proddata), LEN(@proddata))
SET @proddata2 = SUBSTRING(@proddata2, 1, LEN(@proddata2) - CHARINDEX(']', REVERSE(@proddata2)) +1)
SET @proddata = REPLACE(REPLACE(@proddata, @proddata2, ''), '|ASSETBAR|', '|EBARR|')
SET @proddata = SUBSTRING(@proddata,CHARINDEX('(',@proddata),LEN(@proddata))
SET @proddata = REPLACE(REPLACE(@proddata,'(',''),')','')
DECLARE @fields varchar(max) = ''
DECLARE @values varchar(max) = ''
WHILE LEN(@proddata) > 0
BEGIN
DECLARE @temp varchar(100) = LEFT(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)))
SET @fields = REPLACE(LEFT(@temp, CHARINDEX(':', @temp)-1),' ','')
SET @values = REPLACE(SUBSTRING(@temp, CHARINDEX(':', @temp)+1,LEN(@temp)),'"','')-- + ''''
INSERT INTO @t (
fieldName,value
)
SELECT @fields, @values
SET @proddata = SUBSTRING(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)),LEN(@proddata))
END
SET @fields = ''--SUBSTRING(@fields,2,LEN(@fields))
SET @values = ''--SUBSTRING(@values,2,LEN(@values))
-- Transfer them
INSERT INTO @prodvalues (
IndexDef, BarrierType, SubType, BarrierLevel, Strike, PayOnBarrier, PayOnStrike, PayOnStrike2, CrossDate, StartDate, EndDate, Lower, Upper, Payoff
)
SELECT MAX(CASE WHEN fieldName = 'IndexDef' THEN value END) AS IndexDef
, MAX(CASE WHEN fieldName = 'BarrierType' THEN value END) AS BarrierType
, MAX(CASE WHEN fieldName = 'SubType' THEN value END) AS SubType
, MAX(CASE WHEN fieldName = 'BarrierLevel' THEN value END) AS BarrierLevel
, MAX(CASE WHEN fieldName = 'Strike' THEN value END) AS Strike
, MAX(CASE WHEN fieldName = 'PayOnBarrier' THEN value END) AS PayOnBarrier
, MAX(CASE WHEN fieldName = 'PayOnStrike' THEN value END) AS PayOnStrike
, MAX(CASE WHEN fieldName = 'PayOnStrike2' THEN value END) AS PayOnStrike2
, MAX(CASE WHEN fieldName = 'CrossDate' THEN value END) AS CrossDate
, MAX(CASE WHEN fieldName = 'StartDate' THEN value END) AS StartDate
, MAX(CASE WHEN fieldName = 'EndDate' THEN value END) AS EndDate
, MAX(CASE WHEN fieldName = 'Lower' THEN value END) AS Lower
, MAX(CASE WHEN fieldName = 'Upper' THEN value END) AS Upper
, MAX(CASE WHEN fieldName = 'Payoff' THEN value END) AS Payoff
FROM @t
DELETE @t
SET @proddata2 = REPLACE(@proddata2, 'AssetBarrList', '')
WHILE @proddata2 LIKE '%|ASSBARDEF|%'
BEGIN
DECLARE @tmpproddata varchar(max) = @proddata2
DECLARE @nxtpos int = CHARINDEX('|ASSBARDEF|', @proddata2, 10)
IF @nxtpos = 0 SET @nxtpos = CHARINDEX(')', @proddata2)
SET @proddata = SUBSTRING(@proddata2, 1, @nxtpos)
SET @proddata = SUBSTRING(@proddata,CHARINDEX('(',@proddata),LEN(@proddata))
SET @proddata = REPLACE(REPLACE(@proddata,'(',''),')','')
SET @fields = ''
SET @values = ''
WHILE LEN(@proddata) > 0
BEGIN
SET @temp = LEFT(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)))
DECLARE @fld varchar(max) = REPLACE(LEFT(@temp, CHARINDEX(':', @temp)-1),' ','')
IF @fld = 'BarrType' SET @fld = 'BarrierType'
SET @fields = @fld
SET @values = REPLACE(SUBSTRING(@temp, CHARINDEX(':', @temp)+1,LEN(@temp)),'"','')
INSERT INTO @t (
fieldName,value
)
SELECT @fields, @values
SET @proddata = SUBSTRING(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)),LEN(@proddata))
END
INSERT INTO @prodvalues (
IndexDef, BarrierType, SubType, BarrierLevel, Strike, PayOnBarrier, PayOnStrike, PayOnStrike2, CrossDate, StartDate, EndDate, Lower, Upper, Payoff
)
SELECT MAX(CASE WHEN fieldName = 'IndexDef' THEN value END) AS IndexDef
, MAX(CASE WHEN fieldName = 'BarrierType' THEN value END) AS BarrierType
, MAX(CASE WHEN fieldName = 'SubType' THEN value END) AS SubType
, MAX(CASE WHEN fieldName = 'BarrierLevel' THEN value END) AS BarrierLevel
, MAX(CASE WHEN fieldName = 'Strike' THEN value END) AS Strike
, MAX(CASE WHEN fieldName = 'PayOnBarrier' THEN value END) AS PayOnBarrier
, MAX(CASE WHEN fieldName = 'PayOnStrike' THEN value END) AS PayOnStrike
, MAX(CASE WHEN fieldName = 'PayOnStrike2' THEN value END) AS PayOnStrike2
, MAX(CASE WHEN fieldName = 'CrossDate' THEN value END) AS CrossDate
, MAX(CASE WHEN fieldName = 'StartDate' THEN value END) AS StartDate
, MAX(CASE WHEN fieldName = 'EndDate' THEN value END) AS EndDate
, MAX(CASE WHEN fieldName = 'Lower' THEN value END) AS Lower
, MAX(CASE WHEN fieldName = 'Upper' THEN value END) AS Upper
, MAX(CASE WHEN fieldName = 'Payoff' THEN value END) AS Payoff
FROM @t
DELETE @t
SET @proddata2 = REPLACE(@proddata2, @tmpproddata, '')
END
END
END
IF LEFT(@ProdData, 7) = '|EBARR|'
BEGIN
SET @proddata = SUBSTRING(@proddata,CHARINDEX('(',@proddata),LEN(@proddata))
SET @proddata = REPLACE(REPLACE(@proddata,'(',''),')','')
SET @fields = ''
SET @values = ''
DELETE @t
WHILE LEN(@proddata) > 0
BEGIN
SET @temp = LEFT(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)))
SET @fields = REPLACE(LEFT(@temp, CHARINDEX(':', @temp)-1),' ','')
SET @values = REPLACE(SUBSTRING(@temp, CHARINDEX(':', @temp)+1,LEN(@temp)),'"','') --+ ''''
INSERT INTO @t (
fieldName,value
)
SELECT @fields, @values
SET @proddata = SUBSTRING(@proddata,CHARINDEX(' ',@proddata,CHARINDEX(':',@proddata)),LEN(@proddata))
END
INSERT INTO @prodvalues (
IndexDef, BarrierType, SubType, BarrierLevel, Strike, PayOnBarrier, PayOnStrike, PayOnStrike2, CrossDate, StartDate, EndDate, Lower, Upper, Payoff
)
SELECT MAX(CASE WHEN fieldName = 'IndexDef' THEN value END) AS IndexDef
, MAX(CASE WHEN fieldName = 'BarrierType' THEN value END) AS BarrierType
, MAX(CASE WHEN fieldName = 'SubType' THEN value END) AS SubType
, MAX(CASE WHEN fieldName = 'BarrierLevel' THEN value END) AS BarrierLevel
, MAX(CASE WHEN fieldName = 'Strike' THEN value END) AS Strike
, MAX(CASE WHEN fieldName = 'PayOnBarrier' THEN value END) AS PayOnBarrier
, MAX(CASE WHEN fieldName = 'PayOnStrike' THEN value END) AS PayOnStrike
, MAX(CASE WHEN fieldName = 'PayOnStrike2' THEN value END) AS PayOnStrike2
, MAX(CASE WHEN fieldName = 'CrossDate' THEN value END) AS CrossDate
, MAX(CASE WHEN fieldName = 'StartDate' THEN value END) AS StartDate
, MAX(CASE WHEN fieldName = 'EndDate' THEN value END) AS EndDate
, MAX(CASE WHEN fieldName = 'Lower' THEN value END) AS Lower
, MAX(CASE WHEN fieldName = 'Upper' THEN value END) AS Upper
, MAX(CASE WHEN fieldName = 'Payoff' THEN value END) AS Payoff
FROM @t
END
RETURN
END
GO
-- Test code
SELECT *
FROM dbo.FN_PRODDATA_GET('|ASSETBAR| (IndexDef: PayOnStrike:"N" PayOnStrike2:"N" Strike:0.0 Payoff:0.0 Lower:0.0 Upper:0.0 AssetBarrList:[|ASSBARDEF| (StartDate:"20120619" EndDate:"20190619" IndexDef:[1mEUReur1m] BarrType:"EUS" SubType:"DOWN_IN" PayOnStrike:"Y" PayOnStrike2:"N" Strike:0.0091 Payoff:0.0 Upper:0.0 Lower:0.003 )] )') x
Basically, i just changed the handling of fields / values by inserting them separately into a @temporary table and then pivoting them all at once. This removes the need for dynamic SQL.
It seems to work with the supplied test data, but of course it needs more testing. I also didn't bother optimizing code any, leaving it as exercise for the reader