[Feature Request] Table-Valued-Parameters for raw sql

[0x53A]

This issue (#23) and pr (#24) add the ability to provide custom table valued parameters to stored procedures / funcs.

Would it be possible to lift this limitation and also enable this for raw sql?

I would like to be able to write something like

select * from myTable join @tvp ids on ids.Id = myTable.Id where @tvp is a user defined table type (CREATE TYPE)

[smoothdeveloper]

@0x53A I might be overlooking something but I think I had need but this doesn't seem to be possible due to limitation in sys.sp_describe_undeclared_parameters IIRC.

Can you run sys.sp_describe_undeclared_parameters on your statement and have it infer the correct TVP type?

[0x53A]

It can't infer the TVP type automagically.
Any type which has the correct column (e.g. a column of type int with name Id) could be used.

So for this to be possible
a) the TP would need to detect that @tvp referes to a table variable
b) the user needs to declare the actual type of the variable somewhere
c) when sending the request to the server, the tp would need to dummy-declare the table variable so that it can get a result back.

When I run it, I get the following error:

Msg 1087, Level 15, State 2, Line 8
Die @tvp-Tabellenvariable muss deklariert werden.
Msg 11501, Level 16, State 2, Line 8
Der Batch konnte aufgrund von Kompilierungsfehlern nicht analysiert werden.

(The @tvp table variable must be declared)

A simplistic solution for a) could be to search the query string for join @param. There are probably a lot of other cases where a tvp could be inserted, so this may need a complete T-SQL parser ...

Or maybe the error message could be analysed.

Ok, the further I think about this, the less I think this is possible to implement in a sane way ...

[smoothdeveloper]

@0x53A I've tried applying the same convention I use to make sure parameter order remains consistent as well as circumventing the limitation of using one parameter only once:

declare @theValue sometype = @value

-- use @theValue instead of @value, can be used more than once

But AFAIR, using a TVP in lieu of sometype would not work and it was due to sys.sp_describe_undeclared_parameters so there wasn't much that could be done.

[0x53A]

Your trick compiles for me:

Executing

Exec sys.sp_describe_undeclared_parameters @tsql= N'declare @tvp dbo.TVP_IDs = @val; select * from tabObject join @tvp ids on ids.ObjectId = tabObject.ObjectId'

returns

1 @val 243 NULL 8 0 0 266 TIM2017-cad-tim dbo TVP_IDs NULL NULL NULL NULL NULL 0 0 0 1 0 NULL 243 8

with the type

This is with SQL Server 2012 (11.0.3156.0)

Thank you!

[0x53A]

So, the trick compiled ......... - but failed at runtime. The error was something like missing scalar parameter @val.

I was able to workaround it with a dummy function:

create function dbo.dummy_tvp_ids(@ids dbo.TVP_IDs readonly)
returns @tableVar table (ObjectId uniqueidentifier not null)
as
begin
insert into @tableVar select * from @ids
return
end

and calling it like this

select o.ObjectId, ...
from dbo.dummy_tvp_ids(@tvp) e
join tabObject o on o.ObjectId = e.ObjectId
...

This way, the sql server is able to deduce the type of @tvp.

I would still like a way to pass a table valued parameter without the dummy function, but no idea how this could be accomplished ...

[davidtme]

@0x53A I’ve come up with a bit of a hack:

let fixTableValue name (cmd : 'a) =
    let cmd' = (cmd :> ISqlCommand).Raw
    cmd'.CommandText <- Regex("(DECLARE (@[^ ]+) .+?)(=.*?"+name+")", RegexOptions.IgnoreCase).Replace(cmd'.CommandText, "$1 INSERT INTO $2 SELECT * FROM " + name)
    cmd

This allow me to use DECLARE @Source dbo.MyType = @Items, which the parser likes but I can fix the query before it runs

type AccountLoaded = SqlCommandProvider<"
    DECLARE @Ids dbo.ider = @Items
    ...
    """, CompileConnectionString>

let run connectionString items =
    let items' = items |> List.map(fun item -> AccountLoaded.Ider(item.Id))
    use cmd = new AccountLoaded(connectionString) |> fixTableValue "@Items"
    cmd.Execute(Items = items') |> ignore

[davidtme]

@smoothdeveloper I see there is a RewriteSqlStatementToEnableMoreThanOneParameterDeclaration I wonder if the same could be done which looks for

DECLARE @Source dbo.Account_Dim_LoadType
INSERT INTO @Source SELECT * FROM @Items

...

at design time and alters the query before sending it to sp_describe_undeclared_parameters to something like:

DECLARE @Source dbo.Account_Dim_LoadType = @Items

...

[smoothdeveloper]

@davidtme the only way I've seen to alter the command for runtime versus design is the way you did (cast to ISqlCommand).

I have such hack to have "with result sets" design time support but stripped at runtime to run on SQL 2008.

[smoothdeveloper]

Those hacks should not make it to the library, but we should somehow give some love to the documentation and samples to help advanced users to use the many techniques that are only documented in issues / discussions.

[0x53A]

If possible, I would really like to enable working with table-valued parameters in a sane way.

What would you think about adding a new type-provider parameter TVP_Mapping? There you would map parameters to types.

Usage:

use cmd = new SqlCommandProvider<"
select o.ObjectId, ...
from @tvp e
join tabObject o on o.ObjectId = e.ObjectId"  , designTimeConnectionString, tvp_mapping = "@tvp=[dbo].[TVP_IDs]" >(connectionString)

The sql statement as written would produce an error, so the type provider would need to prepend a dummy declaration at design-time.

Example:

-- dummy
declare @tvp [dbo].[TVP_IDs]
-- original user-sql
select o.ObjectId, ...
from @tvp e
join tabObject o on o.ObjectId = e.ObjectId

At first glance, this seems a bit hacky, but in the five minutes I spent thinking about this, I couldn't think of any obvious issues. =)

[smoothdeveloper]

Closing, this has been implemented in #302 via TableVarMapping parameter.