How to get SQL results into variables?

Weller0123
Weller0123 used Ask the Experts™
on
What I'm trying to do is get the single row of results from the first query and insert these values for the row returned by the second query:

What's the easiest way to do this?  Do I even need the variables?  How do I set them from the query?

Thanks in advance.  This is MSSQL 2005

declare @cust_sat_comment   nvarchar(1000),
@cust_sat_sts           nvarchar(30),
@fin_sts                nvarchar(30),
@fin_sts_comments       nvarchar(1000),
@overall_sts            nvarchar(30),
@overall_sts_comment    nvarchar(1000),
@sch_sts                nvarchar(30),
@sch_sts_comment        nvarchar(1000)

Query 1:
select
sr.id,
sr.cust_sat_comment,
sr.cust_sat_sts,
sr.fin_sts,
sr.fin_sts_comment,
sr.overall_sts,
sr.overall_sts_comment,
sr.sch_sts,
sr.sch_sts_comment
from
ODF_CA_SRV_STATUS_RPT  sr
where
sr.odf_parent_id  = '5014891' and
sr.created_date IN(
select MAX(sr.created_date) from ODF_CA_SRV_STATUS_RPT  sr
where
sr.odf_parent_id  = '5014891' and
sr.created_date < (select MAX(created_date) from odf_ca_srv_status_rpt where odf_parent_id = '5014891'))

Query 2:  If I set the variables, I can simple set these values with this where clause.
select
sr.cust_sat_comment,
sr.cust_sat_sts,
sr.fin_sts,
sr.fin_sts_comment,
sr.overall_sts,
sr.overall_sts_comment,
sr.sch_sts,
sr.sch_sts_comment
from
ODF_CA_SRV_STATUS_RPT  sr
where
sr.odf_parent_id  = '5014891' and
sr.created_date IN (select MAX(created_date) from odf_ca_srv_status_rpt where odf_parent_id = '5014891')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm sorry, but I don't seem to follow your logic here. Can you please post some sample data from the first query and some sample data from the second query to see where I could help you?

I don't see what you're using the variables for, and how do Q1 and Q2 relate.

Thanks,

Author

Commented:
The result set looks like this below.  It's just a row of data.  

What I'm doing is copying the values from the 2nd most recent status report into the most recent status report.

The first query returns the fields from the 2nd most recent.  The second query returns fields from the new (empty) status report.

cust_sat_comment     cust_sat_sts     fin_sts     fin_sts_comment     overall_sts     overall_sts_comment     sch_sts     sch_sts_comment    
 -------------------  ---------------  ----------  ------------------  --------------  ----------------------  ----------  ------------------
 (null)               no_issues        on_track    (null)              on_track        dsaf                    on_track    (null)            

 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
Do you want to update the results from the first query? Or... Do you want to update that row from the ODF_CA_SRV_STATUS_RPT  table?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I want to update the row returned by the second query with the row returned by the first query.

Author

Commented:
..just to explain, these are fields in a status report.  When a new one is created, I'm copying these fields from the most recent into the the brand new status report.

Author

Commented:
First query pulls the most recent results.  Second pulls the fields for the one they just clicked create on.
Do you want to update the table or just the results in the query?

Author

Commented:
I need to update the fields returned by the query and only those fields for that single row.
If you want to use variables you can do something like this:

DECLARE @cust_sat_comment   nvarchar(1000),
@cust_sat_sts           nvarchar(30),
@fin_sts                nvarchar(30),
@fin_sts_comments       nvarchar(1000),
@overall_sts            nvarchar(30),
@overall_sts_comment    nvarchar(1000),
@sch_sts                nvarchar(30),
@sch_sts_comment        nvarchar(1000)

select
@cust_sat_comment=sr.cust_sat_comment,
@cust_sat_sts=sr.cust_sat_sts,
@fin_sts=sr.fin_sts,
@fin_sts_comments=sr.fin_sts_comment,
@overall_sts=sr.overall_sts,
@overall_sts_comment=sr.overall_sts_comment,
@sch_sts=sr.sch_sts,
@sch_sts_comment=sr.sch_sts_comment
from
ODF_CA_SRV_STATUS_RPT  sr
where
sr.odf_parent_id  = '5014891' and
sr.created_date IN(
select MAX(sr.created_date) from ODF_CA_SRV_STATUS_RPT  sr
where
sr.odf_parent_id  = '5014891' and
sr.created_date < (select MAX(created_date) from odf_ca_srv_status_rpt where odf_parent_id = '5014891'))

select
@cust_sat_comment,
@cust_sat_sts,
@fin_sts,
@fin_sts_comments,
@overall_sts,
@overall_sts_comment,
@sch_sts,
@sch_sts_comment
from
ODF_CA_SRV_STATUS_RPT  sr
where
sr.odf_parent_id  = '5014891' and
sr.created_date IN (select MAX(created_date) from odf_ca_srv_status_rpt where odf_parent_id = '5014891')

Open in new window


That code will show the values of the first query (stored in variables) in the second query. Although I have a feeling there's a more productive way of doing this, but I find it hard to visualize your scenario.

Let me know if this works for you.

Author

Commented:
This is what I needed and it works.  I was having trouble getting the results from the SQL into the variables.

I then added an update set where for the second part and I'm set.

I agree there might be a more efficient way to do this without variables, but this isn't horribly inefficient I don't think, just var declarations.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial