asked on

How to get SQL results into variables?

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')

Member_2_861731

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,

Weller0123

ASKER

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]

Member_2_861731

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?

Weller0123

ASKER

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

Weller0123

ASKER

..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.

Weller0123

ASKER

First query pulls the most recent results. Second pulls the fields for the one they just clicked create on.

Member_2_861731

Do you want to update the table or just the results in the query?

Weller0123

ASKER

I need to update the fields returned by the query and only those fields for that single row.

ASKER CERTIFIED SOLUTION

Member_2_861731

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Weller0123

ASKER

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.