Return data from a temp table

I have the following stored proc
CREATE PROCEDURE [dbo].[GetPartHistory]
@.PartID int
AS
DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
Step_Description varchar(200), Station_Step_Description varchar(200), Result
varchar(100), Operator_Name varchar(50), Result_Date datetime)
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Step_Result.Result,
Operator.Operator_Name,
Step_Result.Result_Date
FROM
Step_Result
INNER JOIN
Operator ON Step_Result.Operator_# = Operator.Operator_#
INNER JOIN
Station_Step_Data ON Step_Result.Station_Step_Data_# =
Station_Step_Data.Station_Step_Data_#
INNER JOIN
Traveler_Step ON Station_Step_Data.Traveler_Step_# =
Traveler_Step.Traveler_Step_#
INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
WHERE
Step_Result.Product_# = @.PartID
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Weight.Weight,
Operator.Operator_Name,
Weight.Weight_Date
FROM Traveler_Step INNER JOIN
Station_Step_Data ON Traveler_Step.Traveler_Step_# =
Station_Step_Data.Traveler_Step_# INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
INNER JOIN
Weight ON Station_Step_Data.Station_Step_Data_# =
Weight.Station_Step_Data_# INNER JOIN
Operator ON Weight.Operator_# = Operator.Operator_#
WHERE
Weight.Product_# = @.PartID
SELECT Station_Name, Step, Step_Description, Station_Step_Description,
Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
GO
when executed in query analyser it returns the data I am after, but when I
try to access it via ADO I get nothing. What should I do to return data
from the temp table.
John WrightTry adding SET NOCOUNT ON in the beginning of the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int, Step_Descri
ption varchar(200),
> Station_Step_Description varchar(200), Result varchar(100), Operator_Name
varchar(50), Result_Date
> datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# = Traveler_
Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result
, Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_
# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# = Wei
ght.Station_Step_Data_#
> INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description, Res
ult, Operator_Name,
> Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
try to access it via
> ADO I get nothing. What should I do to return data from the temp table.
> John Wright
>|||Yea that did it. I remembered just after I posted the message.
John
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
> Step_Description varchar(200), Station_Step_Description varchar(200),
> Result varchar(100), Operator_Name varchar(50), Result_Date datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
> Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# =
> Traveler_Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# =
> Station.Station_# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# =
> Weight.Station_Step_Data_# INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
> try to access it via ADO I get nothing. What should I do to return data
> from the temp table.
> John Wright
>

Return attributes from all child nodes

Ok this is the last step I hope. Trying to return a table based on
this XML:
<PARENT>
<ITEM spec="spec1 specval="red" /ITEM>
<ITEM spec="spec2 specval="white" /ITEM>
<ITEM spec="spec3 specval="blue" /ITEM>
</PARENT>
I need to return a table:
SPEC SPECVAL
spec1 red
spec2 white
spec3 blue
Thanks for any help on this.
-RichardHow about something like this?
DECLARE @.selxml xml
SET @.selxml ='
<PARENT>
<ITEM spec="spec1" specval="red" />
<ITEM spec="spec2" specval="white" />
<ITEM spec="spec3" specval="blue" />
</PARENT>'
SELECT x.value('data(./@.spec)', 'nvarchar(MAX)') as SPEC,
x.value('data(./@.specval)', 'nvarchar(MAX)') as SPECVAL
FROM @.selxml.nodes('/PARENT/ITEM') T(x)
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:dqk3925c44br94nrn7m3msru3glgpq7h2c@.
4ax.com...
> Ok this is the last step I hope. Trying to return a table based on
> this XML:
> <PARENT>
> <ITEM spec="spec1 specval="red" /ITEM>
> <ITEM spec="spec2 specval="white" /ITEM>
> <ITEM spec="spec3 specval="blue" /ITEM>
> </PARENT>
> I need to return a table:
> SPEC SPECVAL
> spec1 red
> spec2 white
> spec3 blue
> Thanks for any help on this.
> -Richard