Weller0123
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')
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')
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]
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?
ASKER
I want to update the row returned by the second query with the row returned by the first query.
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.
ASKER
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?
ASKER
I need to update the fields returned by the query and only those fields for that single row.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
I don't see what you're using the variables for, and how do Q1 and Q2 relate.
Thanks,