Input parameter to function in SQL query

I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:

Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate]( , -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.

The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.

Odd thing is, if I replace the in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.

Answer this question

Input parameter to function in SQL query

the.chrismo

wag...what needs to be known is what gets interpreted as once it arrives...the fact that it works when you 'seed' it correctly seems to indicate, in addition to the workability of your query syntax, that a convert needs to happen somewhere.

But, of course, convert will be death to performance.
Michael Chroman
The variable that is being used to map the is defined as a DateTime variable. This same variable is used in the following query with no problems:

DELETE [PExecutionPosition] WHERE [LoadDate] =

So the variable itself seems fine. What type of convert would I need To what It is already a date and since it works on the DELETE query, SQL seems to like it.

(I am not too worried about performance since the SELECT query is only being called once.)

I am almost thinking of forget the parameter and "build" the query string using an expression.
KKKKKK
Try setting the bypass prepare to true
Maximilian Raditya

i was guessing that [LoadDate] and were different data types; sorry that wasnt it.
Input parameter to function in SQL query

Answer this question