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
>

Retrieving @@Identity

Hi,
I have the following proc:
CREATE PROCEDURE SP_CadastraPessoaFisica
(
@.Email Varchar(60) = '',
@.Senha Varchar(10) = '',
@.Nome Varchar(50) = '',
@.Sobrenome Varchar(50) = '',
@.DataNascimento DateTime,
@.Sexo Char(1),
@.CPF Varchar(12)
)
As
Set Nocount On
Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
GO
Simple.Isn't it ? So...I need to retrieve the ID from this insert command
when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
use @.@.Identity!
Thanks in advance,
Daniel Groh
Daniel
If you are using SQL Server 2000 you can use SCOPE_IDENITY() function
INSERT INTO Table (col) VALUES (1)
SELECT SCOPE_IDENITY()
Note : there is a difference between @.@.IDENTITY and SCOPE_IDENTITY() which
may bring unexpected result
See BOL for details
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
|||Please don't multipost ,it is answered in .programming
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
|||Hi
You can use this:
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
This will help you solve the problem
thanks and regards
Chandra
"Daniel Groh" wrote:

> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
>

Retrieving @@Identity

Hi,
I have the following proc:
CREATE PROCEDURE SP_CadastraPessoaFisica
(
@.Email Varchar(60) = '',
@.Senha Varchar(10) = '',
@.Nome Varchar(50) = '',
@.Sobrenome Varchar(50) = '',
@.DataNascimento DateTime,
@.Sexo Char(1),
@.CPF Varchar(12)
)
As
Set Nocount On
Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
GO
Simple.Isn't it ? So...I need to retrieve the ID from this insert command
when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
use @.@.Identity!
Thanks in advance,
Daniel GrohDaniel
If you are using SQL Server 2000 you can use SCOPE_IDENITY() function
INSERT INTO Table (col) VALUES (1)
SELECT SCOPE_IDENITY()
Note : there is a difference between @.@.IDENTITY and SCOPE_IDENTITY() which
may bring unexpected result
See BOL for details
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Please don't multipost ,it is answered in .programming
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Hi
You can use this:
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
This will help you solve the problem
thanks and regards
Chandra
"Daniel Groh" wrote:

> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
>|||Hi, I tryed
CREATE PROCEDURE SP_CadastraPessoaFisica
(
@.Email Varchar(60) = '',
@.Senha Varchar(10) = '',
@.Nome Varchar(50) = ''
)
As
Declare @.CdUsuario Int
Set Nocount On
Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
Select @.CdUsuario = @.@.Identity
Insert Into Teste(CdUsuario,Nome) Values(@.CdUsuario,'Daniel Groh')
GO
I have this error now:
Server: Msg 8152, Level 16, State 9, Procedure SP_CadastraPessoaFisica, Line
15
String or binary data would be truncated.
The statement has been terminated.
Atenciosamente,
Daniel Groh
CTF Technologies do Brasil Ltda.
Analista Programador
Fone: 11 3837-4203
E-mail: dgroh@.ctf.com.br
"Chandra" <Chandra@.discussions.microsoft.com> escreveu na mensagem
news:2A563E0C-350B-44CE-9C5D-4B1DD1C2F42F@.microsoft.com...
> Hi
> You can use this:
> SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
> This will help you solve the problem
> thanks and regards
> Chandra
>
> "Daniel Groh" wrote:
>|||Hi
Just see the data capacity for
Teste(CdUsuario, Nome)
Is the column: Nome (Name) more than 11 chars '
thanks and regards
Chandra
"Daniel Groh" wrote:

> Hi, I tryed
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = ''
> )
> As
> Declare @.CdUsuario Int
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> Select @.CdUsuario = @.@.Identity
> Insert Into Teste(CdUsuario,Nome) Values(@.CdUsuario,'Daniel Groh')
> GO
>
> I have this error now:
> Server: Msg 8152, Level 16, State 9, Procedure SP_CadastraPessoaFisica, Li
ne
> 15
> String or binary data would be truncated.
> The statement has been terminated.
> --
> Atenciosamente,
> Daniel Groh
> CTF Technologies do Brasil Ltda.
> Analista Programador
> Fone: 11 3837-4203
> E-mail: dgroh@.ctf.com.br
> "Chandra" <Chandra@.discussions.microsoft.com> escreveu na mensagem
> news:2A563E0C-350B-44CE-9C5D-4B1DD1C2F42F@.microsoft.com...
>
>|||Yes, That was the problem
Thanks in advance!
Atenciosamente,
Daniel Groh
CTF Technologies do Brasil Ltda.
Analista Programador
Fone: 11 3837-4203
E-mail: dgroh@.ctf.com.br
"Chandra" <Chandra@.discussions.microsoft.com> escreveu na mensagem
news:5B01A3DD-F70E-49E8-A5C0-C077C0E45223@.microsoft.com...
> Hi
> Just see the data capacity for
> Teste(CdUsuario, Nome)
> Is the column: Nome (Name) more than 11 chars '
> thanks and regards
> Chandra
> "Daniel Groh" wrote:
>

Retrieving @@Identity

Hi,
I have the following proc:
CREATE PROCEDURE SP_CadastraPessoaFisica
(
@.Email Varchar(60) = '',
@.Senha Varchar(10) = '',
@.Nome Varchar(50) = '',
@.Sobrenome Varchar(50) = '',
@.DataNascimento DateTime,
@.Sexo Char(1),
@.CPF Varchar(12)
)
As
Set Nocount On
Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
GO
Simple.Isn't it ? So...I need to retrieve the ID from this insert command
when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
use @.@.Identity!
Thanks in advance,
Daniel GrohDaniel
If you are using SQL Server 2000 you can use SCOPE_IDENITY() function
INSERT INTO Table (col) VALUES (1)
SELECT SCOPE_IDENITY()
Note : there is a difference between @.@.IDENTITY and SCOPE_IDENTITY() which
may bring unexpected result
See BOL for details
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Please don't multipost ,it is answered in .programming
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Hi
You can use this:
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
This will help you solve the problem
thanks and regards
Chandra
"Daniel Groh" wrote:

> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
>