Named Parameters with fsharp data SqlClient

Question

I am having trouble getting this code to compile when loading the T-Sql from an external file. The code works when the T-Sql is inserted directly in F# as:

 type GetRecentMedia =
 SqlCommandProvider<"DECLARE @MedId NVARCHAR(128);
 DECLARE @TopN INT;
 select Top(@TopN) * from media.mediaInfo
 where MediaId = @MedId",
 Admin.connectionString, ConfigFile = "Web.config">

But I get this error when trying to load T-Sql from a sql file:

Execute takes 0 arguments but here is given 2.

T-Sql:

DECLARE @MedId NVARCHAR(128);
DECLARE @TopN INT;
select Top(@TopN) * from media.mediaInfo
where MediaId = @MedId

F#:

module internal FeedRecentMedia =
 type GetRecentMedia =
 SqlCommandProvider<"Social\InstagramFeed_Select_GetRecentMedia.sql",
 Admin.connectionString, ConfigFile = "Web.config">
module MediaTool =
 // get current media
 let rM = new FeedRecentMedia.GetRecentMedia()
 let curMedia = rM.Execute(MedId = "Id", TopN = 1) |> Seq.head

I tested the t-sql in management studio and it works there. I only get the above mentioned error. What am I missing here?

• user1206480: I've made an update. I've discovered that it works perfectly when the t-sql is inserted directly in F#, but still does not work when loaded externally.
• Dmitry Sevastianov: Why do you think you need to declare variables in T-SQL?
• user1206480: Mainly just for testing the query in management studio. I left the declarations just because i didnt think that it would affect anything. Should i remove it completely?
• Dmitry Sevastianov: Yeah I think it freaks out SQL analyser

Answer 1

This is a documented limitation of the SqlClient type provider. The parameters you pass from F# code to T-SQL must be

• undeclared (which can be a problem if the type is ambiguous, e.g. for numeric types)
• used only once

Fortunately, the same workaround provided in the docs solves both problems: you declare a variable using the outer variable as parameter. This also lets you explicitly annotate the type:

type FizzOrBuzz = SqlCommandProvider<"
 DECLARE @x AS INT = @xVal
 SELECT 
 CASE 
 WHEN @x % 3 = 0 AND @x % 5 = 0 THEN 'FizzBuzz' 
 WHEN @x % 3 = 0 THEN 'Fizz' 
 WHEN @x % 5 = 0 THEN 'Buzz' 
 ELSE CONCAT(@x, '') --use concat to avoid nullable column
 END", connectionString>
let fizzOrBuzz = new FizzOrBuzz()
printfn "Answer on interview:\n%A" [ for i = 1 to 100 do yield! fizzOrBuzz.Execute(i) ]