I'm currently trying to write a SQL Server 2005 function, which gets a string as parameter and creates a table with decimal values out of it.
The problem is, that I have to define the decimal type based on parameters. This not working snippet should demonstrate the idea:
CREATE FUNCTION [dbo].[ufn_ParseDecimal]
(
@Sequence VARCHAR(max),
@Delim CHAR(1),
@Prec INT,
@Scale INT
)
RETURNS @DecimalList TABLE (
fValue decimal(@Prec, @Scale)
)
Any ideas, how this could be done?
CAST and DYNAMIC SQL, though I don't believe functions support the latter all that well. I was thinking along the lines of:
EXEC 'SELECT
CAST(''' +
SUBSTRING(@SEQUENCE, 1, @Prec - @Scale) +
@Delim +
SUBSTRING(@SEQUENCE, @Prec - @Scale + 1) +
"''
AS DECIMAL(' + @Prec + ', ' + @Scale + ')'
You can't define this adhoc in SQL.
The best you can do is to create a global temp table (##) using dynamic SQL. Then it can be used subsequently.
In T-SQL, a function has to have a concrete return type. You will not be able to return a table that contains varying data types unless you convert them to something basic to be interpreted by another process (e.g. a VARCHAR), but this would seem to subvert the purpose of your function.
What you can do is create a table using dynamic SQL, which will allow you specify precision and scale in the table definition:
DECLARE @table NVARCHAR(MAX)
SET @table = '#DecimalTable'
DECLARE @sql NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SET @sql = N'CREATE TABLE ' + @table
+ '([fValue] DECIMAL (' + @Prec + ',' + @Scale + '))'
EXEC @sql
With the table defined, you should be able to insert into the rows using the CAST operator to convert the data in a similar way:
SET @sql = N'INSERT INTO ' + @table
+ 'VALUES (CAST(@Seq AS DECIMAL(' + @Prec + ',' @Scale + '))'
SET @params = N'@Seq VARCHAR(MAX)'
EXEC sp_executesql @sql, @params, @Sequence
Arguably, you may not even need the CAST operation, as SQL Server will implicitly attempt to convert your VARCHAR(MAX) expression when you insert into the DECIMAL column.
Either way, it's not pretty and I'd suggest looking at the possibility of solving your problem some other way, before you resort to using dynamic SQL and all the headaches it brings.
As mentioned by others, Table-Valued User-Defined-Functions must have a specific return type for each field.
The way I would get around that is to change the design slightly. Have the function break up the [sequence] in to a table of strings. Don't do the conversion yet...
CREATE FUNCTION [dbo].[ufn_ParseList] (
@Sequence VARCHAR(MAX),
@Delim CHAR(1)
)
RETURNS @List TABLE (
id INT IDENTITY(1,1),
item VARCHAR(MAX) -- You may want to use something smaller than (MAX)
)
Then, once you have a table of strings, apply the conversion you need. As mentioned by others, this would likely by dynamic SQL.
The existance of the Dynamic SQL in your main body of code, however, may be a real pain...
try this, I only coded to support decimals up to a precision of 5, but you can increase it if necessary:
CREATE FUNCTION [dbo].[ufn_ParseDecimal]
(
@Sequence VARCHAR(max),
@Delim CHAR(1),
@Prec INT,
@Scale INT
)
RETURNS sql_variant
AS
BEGIN
DECLARE @L VARCHAR(max)
DECLARE @R VARCHAR(max)
IF CHARINDEX(@Delim,@Sequence)>0
BEGIN
SET @L=LEFT(@Sequence,CHARINDEX(@Delim,@Sequence)-1)
SET @R=RIGHT(@Sequence,LEN(@Sequence)-CHARINDEX(@Delim,@Sequence))
END
ELSE
BEGIN
SET @L=@Sequence
SET @R=''
END
DECLARE @1_0 decimal(1,0)
DECLARE @1_1 decimal(1,1)
DECLARE @2_0 decimal(2,0)
DECLARE @2_1 decimal(2,1)
DECLARE @2_2 decimal(2,2)
DECLARE @3_0 decimal(3,0)
DECLARE @3_1 decimal(3,1)
DECLARE @3_2 decimal(3,2)
DECLARE @3_3 decimal(3,3)
DECLARE @4_0 decimal(4,0)
DECLARE @4_1 decimal(4,1)
DECLARE @4_2 decimal(4,2)
DECLARE @4_3 decimal(4,3)
DECLARE @4_4 decimal(4,4)
DECLARE @5_0 decimal(5,0)
DECLARE @5_1 decimal(5,1)
DECLARE @5_2 decimal(5,2)
DECLARE @5_3 decimal(5,3)
DECLARE @5_4 decimal(5,4)
DECLARE @5_5 decimal(5,5)
DECLARE @v sql_variant
IF @Prec=1
BEGIN
IF @Scale=0 BEGIN SET @1_0=RIGHT(@L,1) SET @v= @1_0 END
ELSE IF @Scale=1 BEGIN SET @1_1='0.'+LEFT(@R,1) SET @v= @1_1 END
END
ELSE IF @Prec=2
BEGIN
IF @Scale=0 BEGIN SET @2_0=RIGHT(@L,2) SET @v= @2_0 END
ELSE IF @Scale=1 BEGIN SET @2_1=RIGHT(@L,1)+'.'+LEFT(@R,1) SET @v= @2_1 END
ELSE IF @Scale=2 BEGIN SET @2_2= '0.'+LEFT(@R,2) SET @v= @2_2 END
END
ELSE IF @Prec=3
BEGIN
IF @Scale=0 BEGIN SET @3_0=RIGHT(@L,3) SET @v= @3_0 END
ELSE IF @Scale=1 BEGIN SET @3_1=RIGHT(@L,2)+'.'+LEFT(@R,1) SET @v= @3_1 END
ELSE IF @Scale=2 BEGIN SET @3_2=RIGHT(@L,1)+'.'+LEFT(@R,2) SET @v= @3_2 END
ELSE IF @Scale=3 BEGIN SET @3_3= '0.'+LEFT(@R,3) SET @v= @3_3 END
END
ELSE IF @Prec=4
BEGIN
IF @Scale=0 BEGIN SET @4_0=RIGHT(@L,4) SET @v= @4_0 END
ELSE IF @Scale=1 BEGIN SET @4_1=RIGHT(@L,3)+'.'+LEFT(@R,1) SET @v= @4_1 END
ELSE IF @Scale=2 BEGIN SET @4_2=RIGHT(@L,2)+'.'+LEFT(@R,2) SET @v= @4_2 END
ELSE IF @Scale=3 BEGIN SET @4_3=RIGHT(@L,1)+'.'+LEFT(@R,3) SET @v= @4_3 END
ELSE IF @Scale=4 BEGIN SET @4_4= '0.'+LEFT(@R,4) SET @v= @4_4 END
END
ELSE IF @Prec=5
BEGIN
IF @Scale=0 BEGIN SET @5_0=RIGHT(@L,5) SET @v= @5_0 END
ELSE IF @Scale=1 BEGIN SET @5_1=RIGHT(@L,4)+'.'+LEFT(@R,1) SET @v= @5_1 END
ELSE IF @Scale=2 BEGIN SET @5_2=RIGHT(@L,3)+'.'+LEFT(@R,2) SET @v= @5_2 END
ELSE IF @Scale=3 BEGIN SET @5_3=RIGHT(@L,2)+'.'+LEFT(@R,3) SET @v= @5_3 END
ELSE IF @Scale=4 BEGIN SET @5_4=RIGHT(@L,1)+'.'+LEFT(@R,4) SET @v= @5_4 END
ELSE IF @Scale=5 BEGIN SET @5_5= '0.'+LEFT(@R,5) SET @v= @5_5 END
END
RETURN @v
END
this sample code uses the function:
SELECT CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.4','.',4,1) , 'BaseType')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.4','.',4,1) , 'Precision')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.4','.',4,1) , 'Scale')) ,dbo.ufn_ParseDecimal('123.4','.',4,1)
UNION SELECT CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.45','.',5,2), 'BaseType')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.45','.',5,2), 'Precision')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('123.45','.',5,2), 'Scale')) ,dbo.ufn_ParseDecimal('123.45','.',5,2)
UNION SELECT CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('1.234','.',5,4) , 'BaseType')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('1.234','.',5,4) , 'Precision')),CONVERT(varchar(10),SQL_VARIANT_PROPERTY(dbo.ufn_ParseDecimal('1.234','.',5,4) , 'Scale')) ,dbo.ufn_ParseDecimal('1.234','.',5,4)
OUTPUT from sample code:
---------- ---------- ---------- ---------
decimal 4 1 123.4
decimal 5 2 123.45
decimal 5 4 1.2340
(3 row(s) affected)
This is a generic function to parse any text string into a table of values... You can easily use it to do what you are trying to accomplish:
ALTER FUNCTION [dbo].[ParseTextString] (@S Text, @delim VarChar(5))
Returns @tOut Table
(ValNum Integer Identity Primary Key,
sVal VarChar(8000))
As
Begin
Declare @dLLen TinyInt -- Length of delimiter
Declare @sWin VarChar(8000) -- Will Contain Window into text string
Declare @wLen Integer -- Length of Window
Declare @wLast TinyInt -- Boolean to indicate processing Last Window
Declare @wPos Integer -- Start Position of Window within Text String
Declare @sVal VarChar(8000) -- String Data to insert into output Table
Declare @BtchSiz Integer -- Maximum Size of Window
Set @BtchSiz = 7900 -- (Reset to smaller values to test routine)
Declare @dPos Integer -- Position within Window of next Delimiter
Declare @Strt Integer -- Start Position of each data value within Window
-- -------------------------------------------------------------------------
If @delim is Null Set @delim = '|'
If DataLength(@S) = 0 Or
Substring(@S, 1, @BtchSiz) = @delim Return
-- ---------------------------
Select @dLLen = Len(@delim),
@Strt = 1, @wPos = 1,
@sWin = Substring(@S, 1, @BtchSiz)
Select @wLen = Len(@sWin),
@wLast = Case When Len(@sWin) = @BtchSiz
Then 0 Else 1 End,
@dPos = CharIndex(@delim, @sWin, @Strt)
-- ------------------------------------
While @Strt <= @wLen
Begin
If @dPos = 0 -- No More delimiters in window
Begin
If @wLast = 1 Set @dPos = @wLen + 1
Else
Begin
Set @wPos = @wPos + @Strt - 1
Set @sWin = Substring(@S, @wPos, @BtchSiz)
-- ----------------------------------------
Select @wLen = Len(@sWin), @Strt = 1,
@wLast = Case When Len(@sWin) = @BtchSiz
Then 0 Else 1 End,
@dPos = CharIndex(@delim, @sWin, 1)
If @dPos = 0 Set @dPos = @wLen + 1
End
End
-- -------------------------------
Set @sVal = LTrim(Substring(@sWin, @Strt, @dPos - @Strt))
Insert @tOut (sVal) Values (@sVal)
-- -------------------------------
-- Move @Strt to char after last delimiter
Set @Strt = @dPos + @dLLen
Set @dPos = CharIndex(@delim, @sWin, @Strt)
End
Return
End