date parameters in views and subquery views

[Colm Kavanagh]

I am trying to convert my Access SQL queries into equivalent SQL server queries. However, SQL Server does not seem to like me using parameters. How can get around this problem? This is what I have now (@ContextDate is my date parameter):

SELECT dbo.[MIND Accounts].[Client-id], dbo.[MIND Accounts].[Client-name], SUM(dbo.Data.[Created transactions (number)]) AS [SumOfCreated transactions (number)]]],
SUM(dbo.Data.[Created refunds (number)]) AS [SumOfCreated refunds (number)],
SUM(dbo.Data.[Created transactions (value)] * dbo.[Currency conversion].[Conversion rate]) AS [Created transactions (EUR value)], dbo.Data.[Processing date]
FROM dbo.Data INNER JOIN
dbo.[Currency conversion] ON dbo.Data.Currency = dbo.[Currency conversion].Currency INNER JOIN
dbo.[MIND Accounts] ON dbo.Data.[Merchant CID] = dbo.[MIND Accounts].[Account-id]
GROUP BY dbo.[MIND Accounts].[Client-id], dbo.[MIND Accounts].[Client-name], dbo.Data.[Processing date]
HAVING (dbo.Data.[Processing date] <= CONVERT(DATETIME, @ContextDate, 102)) AND (dbo.Data.[Processing date] >= DATEADD(d, - 27, CONVERT(DATETIME, @ContextDate, 102)))

[Erland Sommarskog]

Post by Colm Kavanagh
I am trying to convert my Access SQL queries into equivalent SQL server
queries. However, SQL Server does not seem to like me using parameters.

At a quick glance the query looks OK, but that presumes that you
declared the parameter. What error message do you get? (Please always
include any error message you get.)

If this is intended to be a parameterised view, you want to look at
inline table functions.

[Colm Kavanagh]

Thx for your reply. Yes I am trying to create a parameterised view. I didn't declare it but I assumed this wouldn't be needed (it isn't in Access queries).

The error message I get is: "The following errors were encountered while parsing the contents of the SQL pane: 'Parameters cannot be used in this query type'

How do you suggest I use an inline function here?

[Erland Sommarskog]

Post by Colm Kavanagh
The error message I get is: "The following errors were encountered while
parsing the contents of the SQL pane: 'Parameters cannot be used in this
query type'

Sounds like you are using the Query Designer. That's a very ugly sucker,
and which does not understand all legal T-SQL syntax. Not that it matters
in this case, but you will hurt yourself sooner or later, if you say there.

Post by Colm Kavanagh
How do you suggest I use an inline function here?

You can use a table function like this:

SELECT ... FROM dbo.func(@parameter)

Or

SELECT ..
FROM tbl
CROSS APPLY dbo.func(tbl.col)