Member Avatar for jovillanuev

Guys,

Using this query, i got an error. any idea guys.
what method should i used for this report.
Im using text queries instead of SP.
thanks.

The variable name '@FromDate' has already been declared. Variable names must be unique within a query batch or stored procedure.

Fromdate:'2012/06/01'
FromTime:12:00AM

Todate:'2012/06/29'
Totime:12:00PM

DECLARE @fromDateTime as datetime, @toDateTime as datetime
SET @fromdatetime = dbo.getCombinedDateTime(@fromDate,@fromTime)
SET @todatetime = dbo.getCombinedDateTime(@toDate,@toTime)

Select
v.JOURNALID
,v.TRANSDATE
,v.ITEMID
,v.QTY
,v.COSTAMOUNT
,v.JOURNALTYPE
,v.BOMLINE
From INVENTJOURNALTRANS v with (nolock)
Where v.TRANSDATE between @FromDatetime and @ToDatetime
and v.JOURNALTYPE=3
and v.BOMLINE=0

  • 3 Contributors
  • 2 Replies
  • 216 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Short answer: SQL Server is telling you the same variable name is being declared twice inside one batch. In SSRS a text dataset with parameter tokens (for example @FromDate) is sent to SQL Server as a parameterized batch (typically via sp_executesql), so the engine already knows those names as parameters — if your query then includes explicit DECLARE @FromDate (or a UDF/inner script that does), you’ll get the “already been declared” error.

Practical fixes:

  • Best and simplest: do the date/time combination in the report and pass a single DateTime per bound parameter to the dataset. Example SSRS expression to combine a date parameter and a time parameter into a DateTime value:

    = CDate(
  Format(Parameters!FromDate.Value, "yyyy-MM-dd")
  & " " &
  Format(Parameters!FromTime.Value, "HH:mm:ss")
)

    Map that expression to the dataset parameter (e.g. @FromDateTime) so the dataset only receives ready-to-use datetimes.

  • If you need server-side logic, move the logic into a stored procedure that accepts the combined datetimes as parameters. Stored procedures avoid the sp_executesql/inline-declare collision and are easier to maintain and test.

Notes on earlier replies: ’s GO suggestion isn’t appropriate here — GO is a client-side batch separator (SSMS/SQLCMD) and won’t behave the way you expect when SSRS runs the query; was right to warn that splitting batches will break variable scope.

Quick troubleshooting checklist (in order): run your text query in SSMS replacing SSRS params with literals to reproduce the error; test the getCombinedDateTime UDF on its own; remove any DECLARE that duplicates parameter names or rename local variables; then re-run from SSRS with mapped parameters.

Recommended Answers

All 2 Replies

Member Avatar for ChrisHunter

Put GO between SET and SELECT, failing that put GO between DECLARE, SET, SET and SELECT.

The GO key word tells it that it is executing something that is in a different batch, you have to use it if you're creating schemas and tables in the same script so i guess it's worth a try . . .

let me know how it goes.

Member Avatar for BitBlt

Actuall, , putting a GO in between the statements will separate batches so you'll just lose your variable declarations. Try it.

To the OP, you're not giving us a lot of information. What is the error you get? Which statement is erroring? Can we assume the dbo.getCombinedDateTime is a user-defined function? If so, what happens inside it? What are it's parameter datatypes? What is it's return type?

I have my suspicions that the problem is inside the UDF, but we really can't help without more to work with.

Edited by BitBlt
commented: learn something new every day, thanks. +4
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.