PASSAR DATATABLE P/ STORED PROCEDURE EM SQL SERVER

TUNUSAT 28/11/2014 12:34:45
#442850
Bom dia PessoALL,

Por favor, sem pressa...

Montei este exemplo:
Passing a datatable to a Stored Procedure in SQL Server 2008
http://www.codeproject.com/Tips/214492/Passing-a-datatable-to-a-stored-procedure-in-Sql-S

Mas não entendi, pois não existe o retorno da Stored Procedure. Gostaria que fosse mostrado na tela do Console o Nome e o Endereço do código passado.
Não estou conseguindo retornar esta informação.

Como eu faço isto?

Anexei o exemplo aqui. Está em C# com VS 2013: [Ô]caDataTable_StoredProcedure.sln[Ô].
Abaixo o código fonte para quem não quer fazer download do projeto.

No SQL Server:
     // Passando uma tabela de dados (DataTable) para um procedimento armazenado (Stored Procedure) no SQL Server 2008:

        //-------------------- Crie a tabela:

        //CREATE TABLE dbo.TestTableVariable
        //(
        //    ID        INT IDENTITY(1,1)    primary key,
        //    Name    VARCHAR(10),
        //    Addr    VARCHAR(10)
        //)

        //-------------------- Crie o tipo de dados da tabela:
        //CREATE TYPE dbo.TableVariable AS TABLE
        //(
        //    ID        INT
        //)

        //-------------------- Insira alguns dados padrão:
        //INSERT INTO dbo.TestTableVariable VALUES ([ô]aaa[ô], [ô]addr-aaa[ô])
        //INSERT INTO dbo.TestTableVariable VALUES ([ô]bbb[ô], [ô]addr-bbb[ô])
        //INSERT INTO dbo.TestTableVariable VALUES ([ô]ccc[ô], [ô]addr-ccc[ô])
        //INSERT INTO dbo.TestTableVariable VALUES ([ô]ddd[ô], [ô]addr-ddd[ô])

        //-------------------- Crie o procedimento armazenado (Stored procedure) que tem a variável de tabela como parâmetro:
        //SET ANSI_NULLS ON
        //GO
        //SET QUOTED_IDENTIFIER ON
        //GO
        //CREATE PROCEDURE dbo.SelectTestTableVariable
        //(
        //    @TableVar dbo.TableVariable READONLY
        //)
        //AS
        //BEGIN
        //    SET NOCOUNT ON;

        //    SELECT ID, Name, Addr FROM dbo.TestTableVariable
        //    WHERE ID IN (SELECT ID FROM @TableVar)

        //END
        //GO

No C#:
     /// <summary>
        /// Aqui está o código C# para um aplicativo de console:
        /// </summary>
        /// <param name=[Ô]args[Ô]>Parâmetros recebidos do console.</param>
        static void Main(string[] args)
        {
            try
            {
                DataSet dataset = new DataSet();
                DataTable dataTable = new DataTable();
                SqlDataReader reader;

                dataTable.Columns.Add([Ô]ID[Ô]);
                dataTable.Columns.Add([Ô]Name[Ô]);
                dataTable.Columns.Add([Ô]Adress[Ô]);
                var dr = dataTable.NewRow();
                dr[[Ô]ID[Ô]] = 1;
                dataTable.Rows.Add(dr);

                //Configure seu acesso ao seu Banco de Dados lá no aquivo [Ô]App.Config[Ô]!
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[[Ô]LOCAL TESTE[Ô]].ConnectionString))
                {
                    conn.Open();

                    using (SqlCommand cmd = new SqlCommand([Ô][dbo].[SelectTestTableVariable][Ô]))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = conn;
                        cmd.Parameters.AddWithValue([Ô]@TableVar[Ô],  dataTable);
                        //cmd.Parameters.Add(new SqlParameter ([Ô]@Name[Ô], SqlDbType.VarChar));
                        //cmd.Parameters.Add(new SqlParameter ([Ô]@Addr[Ô], SqlDbType.VarChar));
                        //cmd.Parameters.AddWithValue([Ô]@Name[Ô], SqlDbType.VarChar);
                        //cmd.Parameters.AddWithValue([Ô]@Addr[Ô], SqlDbType.VarChar);
                        cmd.Parameters.AddWithValue([Ô]@Name[Ô], dataTable);
                        cmd.Parameters.AddWithValue([Ô]@Addr[Ô], dataTable);
                        //reader = cmd.ExecuteReader();

                        //Usando o DataAdapter para preencher o DataTable
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(dataset);
                        }
                    }
                }

                //Mostrar na tela.
                if (dataTable.Rows.Count > 0)
                {
                    Console.WriteLine(dataTable.Rows[0][[Ô]Name[Ô]].ToString());
                    //Console.WriteLine(reader.RecordsAffected);
                }
                else
                {
                    Console.WriteLine([Ô]Não mostrou nada...[Ô]);
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.ReadKey();         
            }
        }
    }
}



[][ô]s,
Tunusat.
TUNUSAT 03/12/2014 10:02:43
#442955
Por favor,

Alguém pode ajudar?
Não consigo recuperar a informação!

Fiz uma alteração na Stored Procedure:
ALTER PROCEDURE [dbo].[SelectTestTableVariable]
(
	@TableVar dbo.TableVariable READONLY,
	@NameR VARCHAR(10) OUTPUT,
	@AddrR VARCHAR(10) OUTPUT
)
AS 
BEGIN
	SET NOCOUNT ON;

	SELECT @NameR = Name, @AddrR = Addr 
	FROM dbo.TestTableVariable 
	WHERE ID IN (
		SELECT ID 
		FROM @TableVar 
		) 
END


O código está assim:

        static void Main(string[] args)
        {
            try
            {
                DataTable Result = new DataTable();
                DataTable dataTable = new DataTable();
                //String teste = [Ô][Ô];

                dataTable.Columns.Add([Ô]ID[Ô]);
                var dr = dataTable.NewRow();
                dr[[Ô]ID[Ô]] = 1;
                dataTable.Rows.Add(dr);

                //Configure seu acesso ao seu Banco de Dados lá no aquivo [Ô]App.Config[Ô]!
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[[Ô]LOCAL TESTE[Ô]].ConnectionString))
                {
                    conn.Open();

                    using (SqlCommand cmd = new SqlCommand([Ô][dbo].[SelectTestTableVariable][Ô]))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = conn;
                        cmd.Parameters.AddWithValue([Ô]@TableVar[Ô],  dataTable);
                        cmd.Parameters.AddWithValue([Ô]@NameR[Ô], SqlDbType.VarChar);
                        cmd.Parameters.AddWithValue([Ô]@AddrR[Ô], SqlDbType.VarChar);

                        //Usando o DataAdapter para preencher o DataTable
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(Result);
                        }
                    }
                }

                //Mostrar na tela.
                if (Result.Rows.Count > 0)
                {
                    Console.WriteLine([Ô]Para o ID passado: {0} [Ô], Result.Rows[0][[Ô]Id[Ô]].ToString());
                    Console.WriteLine([Ô]  Retornou o nome: {0} [Ô], Result.Rows[0][[Ô]Name[Ô]].ToString());
                    Console.WriteLine([Ô]     e o endereço: {0} [Ô], Result.Rows[0][[Ô]Addr[Ô]].ToString());
                }
                else
                {
                    Console.WriteLine([Ô]Não retornou nada...[Ô]);
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.ReadKey();         
            }
        }
    }
}


[][ô]s,
Tunusat.
LVFIOROT 03/12/2014 13:14:54
#442960
teste executar essas linhas no mssql e veja se terá o resultada da linha 1

declare @TableVar dbo.TableVariable
insert into @TableVar values (1)

exec SelectTestTableVariable @TableVar
LVFIOROT 03/12/2014 13:25:39
#442961
assim funciona.....

 try
            {
                DataSet dataset = new DataSet();
                DataTable dataTable = new DataTable();

                dataTable.Columns.Add([Ô]ID[Ô]);

                var dr = dataTable.NewRow();

                dr[[Ô]ID[Ô]] = 1;
                dataTable.Rows.Add(dr);

                //Configure seu acesso ao seu Banco de Dados lá no aquivo [Ô]App.Config[Ô]!
                using (SqlConnection conn = new SqlConnection(@[Ô]Data Source=localhost\sqlexpress;Initial Catalog=teste;Integrated Security=True[Ô]))
                {
                    conn.Open();

                    using (SqlCommand cmd = new SqlCommand([Ô][dbo].[SelectTestTableVariable][Ô]))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = conn;
                        cmd.Parameters.AddWithValue([Ô]@TableVar[Ô], dataTable);

                        //Usando o DataAdapter para preencher o DataTable
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(dataset);
                        }
                    }
                }

                //Mostrar na tela.
                if (dataset.Tables.Count > 0)
                {
                    if (dataset.Tables[0].Rows.Count > 0)
                        Console.WriteLine(dataset.Tables[0].Rows[0][[Ô]Name[Ô]].ToString());
                    //Console.WriteLine(reader.RecordsAffected);
                }
                else
                {
                    Console.WriteLine([Ô]Não mostrou nada...[Ô]);
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.ReadKey();
            }
TUNUSAT 08/12/2014 16:14:17
#443064
LVFIOROT,

OKay. Mas espero o retorno de duas strings (Nome e Endereço) a saber:

cmd.Parameters.AddWithValue([Ô]@NameR[Ô], SqlDbType.NVarChar);
   cmd.Parameters[[Ô]@NameR[Ô]].Direction = ParameterDirection.Output;
   cmd.Parameters.AddWithValue([Ô]@AddrR[Ô], SqlDbType.NVarChar);
   cmd.Parameters[[Ô]@AddrR[Ô]].Direction = ParameterDirection.Output;


A Stored Procedure alterada reclamou que eu não informei estes parameters.
Eu passei e depois ela reclamou que estou devolvendo uma string no lugar de um inteiro ... Realmente dentro do Microsoft SQL Server Manager Studio está escrito:

- dbo.SelectTestTableVariable
- Parâmetros
- @TableVar (TableVariable, Entrada, Nenhum Padrão)
- @NameR (varchar(10), Entrada, Nenhum Padrão)
- @AddrR (varchar(10), Entrada, Nenhum Padrão)
- [txt-color=#e80000]Retorna integer[/txt-color]

Não estou retornando no ponto certo?
O erro ocorre em:
adp.Fill(dataset);


[][ô]s,
Tunusat.
TUNUSAT 10/12/2014 08:37:33
#443102
PessoALL,

Consigo retornar a informação dento do [Ô]Microsoft SQL Server Manager Studio[Ô].
Desta forma:
declare @TableVar TableVariable
insert into @TableVar values (1)
declare @NameR VarChar(10)
declare @AddR VarChar(10)

exec SelectTestTableVariable @TableVar, @NameR OUTPUT, @AddR OUTPUT

SELECT @NameR, @AddR


Abaixo a procedure alterada:
ALTER PROCEDURE [dbo].[SelectTestTableVariable]
(
	@TableVar dbo.TableVariable READONLY,
	@NameR VARCHAR(10) OUTPUT,
	@AddrR VARCHAR(10) OUTPUT
)
AS 
BEGIN
	--SET NOCOUNT ON;

	--SELECT @NameR = Name, @AddrR = Addr 
	--SELECT Name, Addr 
	--FROM dbo.TestTableVariable 
	--WHERE ID IN (
	--	SELECT ID 
	--	FROM @TableVar 
	--	) 

	--SELECT @NameR = [ô]TESTE1[ô];
	--SELECT @AddrR = [ô]TESTE2[ô];



	SELECT @NameR = (SELECT Name 
	FROM dbo.TestTableVariable 
	WHERE ID IN (
		SELECT ID 
		FROM @TableVar 
		))

	SELECT @AddrR = (SELECT Addr 
	FROM dbo.TestTableVariable 
	WHERE ID IN (
		SELECT ID 
		FROM @TableVar 
		))

RETURN SELECT ID 
		FROM @TableVar

END


[][ô]s,
Tunusat.
LVFIOROT 10/12/2014 09:01:44
#443104
Aquele acerto de código esta funcionando redondo aqui e volta os dois campos.
era so colocar o segundo campo que ele seria impresso na tela ...
LVFIOROT 10/12/2014 09:05:09
#443105 Resposta escolhida
Você esta confundindo as coisas, com essa alteração que você fez não consigo entender o que você esta querendo fazer.
O codigo que te passei ja estava correto, se entendi bem o que voce queria, mas estou achando que voce está querendo outra coisa.



Citação:

:
PessoALL,

Consigo retornar a informação dento do [Ô]Microsoft SQL Server Manager Studio[Ô].
Desta forma:

declare @TableVar TableVariable
insert into @TableVar values (1)
declare @NameR VarChar(10)
declare @AddR VarChar(10)

exec SelectTestTableVariable @TableVar, @NameR OUTPUT, @AddR OUTPUT

SELECT @NameR, @AddR


Abaixo a procedure alterada:
ALTER PROCEDURE [dbo].[SelectTestTableVariable]
(
	@TableVar dbo.TableVariable READONLY,
	@NameR VARCHAR(10) OUTPUT,
	@AddrR VARCHAR(10) OUTPUT
)
AS 
BEGIN
	--SET NOCOUNT ON;

	--SELECT @NameR = Name, @AddrR = Addr 
	--SELECT Name, Addr 
	--FROM dbo.TestTableVariable 
	--WHERE ID IN (
	--	SELECT ID 
	--	FROM @TableVar 
	--	) 

	--SELECT @NameR = [ô]TESTE1[ô];
	--SELECT @AddrR = [ô]TESTE2[ô];



	SELECT @NameR = (SELECT Name 
	FROM dbo.TestTableVariable 
	WHERE ID IN (
		SELECT ID 
		FROM @TableVar 
		))

	SELECT @AddrR = (SELECT Addr 
	FROM dbo.TestTableVariable 
	WHERE ID IN (
		SELECT ID 
		FROM @TableVar 
		))

RETURN SELECT ID 
		FROM @TableVar

END


[][ô]s,
Tunusat.
TUNUSAT 10/12/2014 09:16:57
#443107
LVFIOROT,

Valew pela resposta.

Eu vou explicar. Quero fazer o seguinte:
1) Passar uma tabela para a Stored Procedure - Okay - funciona.
2) Retornar dois valores: Nome (@NameR) e Endereço (@AddrR ) e mostrá-los na tela - Como não sei usar direito SP estou tendo muitas dificuldades, hora a SP não retorna nenhum valor, ora a SP diz que estou tentando converter CHAR para INT.

Não sei se o problema está na SP ou no código.

... Depois vou tentar ao invés de retornar valores, retornar uma tabela como segundo exemplo.

Vou colocar de exemplo aqui no site depois de terminar... claro que vou escrever que vc me ajudou.

[][ô]s,
Tunusat.
LVFIOROT 10/12/2014 09:21:00
#443108
aquela modificação que tinha feito já fazia isso... com aquele codigo que voce postou.
Ela retorna uma table.

ALTER PROCEDURE [dbo].[SelectTestTableVariable]
(
    @TableVar dbo.TableVariable READONLY
)
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT NameR = Name, AddrR = Addr 
    FROM dbo.TestTableVariable 
    WHERE ID IN (
        SELECT ID 
        FROM @TableVar 
        ) 
END


try
            {
                DataSet dataset = new DataSet();
                DataTable dataTable = new DataTable();

                dataTable.Columns.Add([Ô]ID[Ô]);

                var dr = dataTable.NewRow();

                dr[[Ô]ID[Ô]] = 1;
                dataTable.Rows.Add(dr);

                //Configure seu acesso ao seu Banco de Dados lá no aquivo [Ô]App.Config[Ô]!
                using (SqlConnection conn = new SqlConnection(@[Ô]Data Source=localhost\sqlexpress;Initial Catalog=teste;Integrated Security=True[Ô]))
                {
                    conn.Open();

                    using (SqlCommand cmd = new SqlCommand([Ô][dbo].[SelectTestTableVariable][Ô]))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = conn;

                        cmd.Parameters.AddWithValue([Ô]@TableVar[Ô], dataTable);

                        //Usando o DataAdapter para preencher o DataTable
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(dataset);
                        }
                    }
                }

                //Mostrar na tela.
                if (dataset.Tables.Count > 0)
                {
                    if (dataset.Tables[0].Rows.Count > 0)
                        Console.WriteLine(dataset.Tables[0].Rows[0][[Ô]Name[Ô]].ToString());
                    //Console.WriteLine(reader.RecordsAffected);
                }
                else
                {
                    Console.WriteLine([Ô]Não mostrou nada...[Ô]);
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.ReadKey();
            }
TUNUSAT 10/12/2014 10:24:16
#443109
LVFIOROT,

Consegui entender como ele retorna uma tabela. Não estava entendendo.
Como faço agora para que a SP retorne dois valores de OUTPUT?
Tentei mudar para fazer isto, mas não consegui.

Obrigado,
Tunusat.
Página 1 de 2 [15 registro(s)]
Tópico encerrado , respostas não são mais permitidas
Voltar