undeclared parameter cannot be used more than once

[fsprojects]

undeclared parameter cannot be used more than once

[dsevastianov]

That's a good one, thanks for testing!
On Oct 25, 2013 10:57 PM, "Jack Fox" [email protected] wrote:

It appears you cannot use the same parameter more than once.

Comment out the second occurence:

--OR city LIKE @startsWith

and it builds.

Error 6 The type provider 'FSharp.Data.SqlClient.SqlCommandTypeProvider'
reported an error: The undeclared parameter '@startsWith' is used more than
once in the batch being analyzed.

[]
let sqlCityCounty = @"
SELECT DISTINCT
streetName = null,
streetAddress = null,
postalCode = null,
listingID = null,
mlsID = null,
classID = null,
neighborhood = null,
countryCode,
state,
county,
city
FROM tbMLSQuickLocationListingCount WITH (NOLOCK)
WHERE
(
county LIKE @startsWith
OR city LIKE @startsWith
)
AND locationType = 'City'
AND MLSID IN (select * from dbo.ConvertCommaSeparatedStringToTableOfInts(@mlsIDList))
AND CASE WHEN (MLSID = 10 AND companyID = @companyid) OR MLSID <> 10 THEN 1 ELSE 0 END = 1"

type QueryCityCounty = SqlCommand<sqlCityCounty, ConnectionStringName="staging", ResultType = ResultType.Records, ConfigFile="AutoComplete.config">
let cmd1 = QueryUserMLS(startsWith = "7L%", mlsIDList = "1,2,3", companyID = 1)

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/13
.

[dmitry-a-morozov]

@jackfoxy Jack, I will look into the issue.

[jackfoxy]

Thanks.

Found this by accident. I don’t have to perform that query, but good to identify the issue.

From: Dmitry [mailto:[email protected]]
Sent: Friday, October 25, 2013 8:09 PM
To: dmitry-a-morozov/FSharp.Data.SqlCommandTypeProvider
Cc: Jack Fox
Subject: Re: [FSharp.Data.SqlCommandTypeProvider] undeclared parameter cannot be used more than once (#13)

@jackfoxy https://github.com/jackfoxy Jack, I will look into the issue.

—
Reply to this email directly or view it on GitHub #13 (comment) . https://github.com/notifications/beacon/qagoFbftMXzJr4d1v9KIqp5_a7A6T-YB64Px8e6g0fl2Jx45DElcwzcm0crU2eST.gif

[rojepp]

From the docs:

sp_describe_undeclared_parameters returns an error in any of the following cases.

• If the input @tsql is not a valid Transact-SQL batch. Validity is determined by parsing and analyzing the Transact-SQL batch. Any errors caused by the batch during query optimization or during execution are not considered when determining whether the Transact-SQL batch is valid.
• If @params is not NULL and contains a string that is not a syntactically valid declaration string for parameters, or if it contains a string that declares any parameter more than one time.
• If the input Transact-SQL batch declares a local variable of the same name as a parameter declared in @params.
• If the statement creates any temporary tables.

These are the limitations of this TP, and maybe they should be documented here.
There's not much to be done about this, other than a simple workaround. Make sure each @param is declared only once:

DECLARE @input int
SET @input = @parm
SELECT *
FROM sys.indexes
WHERE @input = 1 or @input = 2

[dmitry-a-morozov]

@rojepp Thanks for help

[dmitry-a-morozov]

fixed in ver 1.7.6

[geandbe]

Looks like the issue reopen is due. TP fails to accept the following query:

.....................
WHERE <predicate>
AND (@Param = '' OR [Field] = @Param)

with diagnostics "An item with the same key has already been added".

[rojepp]

@geandbe The workaround I posted above doesn't work?

[geandbe]

Sure it does, but workaround <> closed issue, right?

[cmeeren]

The workaround works, but is this not possible to fix? Using e.g. @input = '' OR @input = 'asd' in a normal SQL script works fine without declaring it, but fails using using this type provider.