Set Variable based on Query is this possible?

[Rogoflap]

I would like to set a variable to a result of a query to the database.

Is this possible and how do I do this?

The reason for this is I actually have a SQL Select statement that will be running to get results. The sql query uses a paramter when it's run. The parameter will be a value based on anohter query. Similar to this below.

I need to run a query like below and thougth the best way would be to pass along a variable as the parameter. This way RDL will not prompt user when it's run.

SQL Query:

Declare @PayDate as datetime

select top 1 @paydate = editdate
from vs_paycateditimport
order by convert(datetime,editdate) desc

select
oe.paygroup,vsp.paycat,sum(convert(float,[amount])) as Dollars
from vs_paycateditimport vsp inner join ol_employee2 oe on oe.employeenumber = vsp.empid
where convert(datetime,Editdate) = @paydate
and vsp.paycat in (733,734)
group by oe.paygroup,vsp.paycat

 

[ousoonerjoe]

There are 2 ways to set a variable from a SELECT:

DECLARE @Variable DATETIME;
SET @Variable = (SELECT FieldName FROM TableName WHERE something);

or

DECLARE @Variable DATETIME;
SELECT @Variable = FieldName FROM TableName WHERE something;

The caveat is that you MUST return a single value to the variable. Multiple return values will result in an error.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[Rogoflap]

ousoonerjoe,

Thanks for the quick response, but in my given SQL I do this and yes in SQL Management Studio it works perfect.

But in the RDL (Visual Studio) Development Environment, I am getting an error about the parameter not being set properly.

Visual Studio, puts the parameter in it's parameter set and when running the report I get a parameter prompt before the report runs.

Roger

 

[ousoonerjoe]

Ah. Try this: Place the entire item into a Stored Procedure. Then call the stored procedure. Stored Procedures also tend to process much more efficiently.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[DaveInIowa]

Here's a couple ideas ...

Create Dataset SinglePayDate with your first query

Create another Dataset with your main query

With the parameter that is automatically generated by the second query:
Change its visibility to Internal
Set its Default Values to Get values from a query and select the SinglePayDate Dataset and editdate Value field

or, can you just include the top pay date in the main query?:

SELECT oe.paygroup, vsp.paycat, sum(convert(float,[amount])) as Dollars
  FROM vs_paycateditimport vsp 
 INNER
  JOIN ol_employee2 oe
    ON oe.employeenumber = vsp.empid
 WHERE CONVERT(DATETIME,Editdate) = (SELECT TOP 1 editdate
                                       FROM vs_paycateditimport
                                      ORDER BY CONVERT(DATETIME, editdate) DESC)
   AND vsp.paycat in (733,734)
 GROUP BY oe.paygroup, vsp.paycat