Parameter Error When Using Temp Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parameter Error When Using Temp Table

Author

Topic

CJ81
Starting Member

9 Posts

Posted - 2010-11-08 : 10:24:12

Hi everyone,

I have some code which firstly puts some data into a temporary table, and then secondly runs a SELECT query on that data.

I want to add a parameter to it so that my user can be more specific about the records that are returned.

However it gives me the error "Must declare the scalar variable @Date1".

I can get it to run fine if I don't use a temporary table (remove the first bracketed section or remove the # symbols to create a 'real' table), or if I take out the parameter (the last line)!

Here is my code:

(SELECT PropertyValue, PropertyCode, PropertyTable, LEFT(PropertyCode, CHARINDEX('^', PropertyCode) - 1) AS ENo, SUBSTRING(PropertyCode, CHARINDEX('^', PropertyCode) + 1, 15) AS Header
INTO [#TempTable]
FROM vw_WWMPropertyValue
WHERE (PropertyTable = 'Event') AND (PropertyCode LIKE '%^%'))

SELECT
#TempTable.PropertyValue, #TempTable.PropertyCode, #TempTable.PropertyTable, #TempTable.ENo, #TempTable.Header, vw_EventDetail.EventNumber, vw_EventDetail.EventDate, vw_EventDetail.MajorPaintSupplier AS NewSupplier, vw_VesselDetail.IMONumber, vw_VesselDetail.DWT, vw_VesselDetail.VesselName, vw_VesselDetail.CurrOwner, vw_VesselDetail.CurrOwnerCountry, vw_VesselDetail.CurrOwnerRegion, vw_VesselDetail.VesselGroup, vw_VesselDetail.OwnerRegionCode, vw_VesselDetail.OwnerCountryCode

FROM #TempTable
RIGHT OUTER JOIN vw_EventDetail ON #TempTable.ENo = vw_EventDetail.EventNumber INNER JOIN vw_VesselDetail ON vw_EventDetail.VesselNumber = vw_VesselDetail.VesselNumber

WHERE (vw_EventDetail.EventDate = @Date1)

Would really appreciate any help anyone can offer.

Thanks!
CJ

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 10:36:32

You are talking about a parameter.
In the given example I can't see a parameter and I can't see if this is astored procedure.
Do you know that a variable has to be declared?
If we talk about a stored procedure then please show the code.

No, you're never too old to Yak'n'Roll if you're too young to die.

CJ81
Starting Member

9 Posts

Posted - 2010-11-08 : 10:44:39

Hi Fred,

Sorry I think I've used the wrong terminology. It is the variable @Date1 that is causing the problem. This variable will be linked to a parameter in my final report.

CJ

CJ81
Starting Member

9 Posts

Posted - 2010-11-08 : 11:47:47

PS - this is not a stored procedure, I am not able to create these so my code is written directly into the query editor of my report.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-08 : 12:38:57

So you are using a query in some reporting system and you want to know how to get the reporting system to use a variable/paramter in your query?

CJ81
Starting Member

9 Posts

Posted - 2010-11-09 : 05:41:43

I've used the above code in Visual Studio 2005 Reporting Services (directly in the query window, not in a stored procedure) and it works if I don't include the last line:

WHERE (vw_EventDetail.EventDate = @Date1)

It gives me the error "Must declare the scalar variable @Date1".

If I leave that line in, but remove this first section (below) it also works:

(SELECT PropertyValue, PropertyCode, PropertyTable, LEFT(PropertyCode, CHARINDEX('^', PropertyCode) - 1) AS ENo, SUBSTRING(PropertyCode, CHARINDEX('^', PropertyCode) + 1, 15) AS Header
INTO [#TempTable]
FROM vw_WWMPropertyValue
WHERE (PropertyTable = 'Event') AND (PropertyCode LIKE '%^%'))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 06:05:49

Read about Reporting Services and parameters, for example here:
http://www.sql-server-performance.com/articles/reporting/report_parameters_p1.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources