Problems with a SP with a Temporary Table in .NET

Hy people, i hava a problem with add a SP with a tempory table inside in a
SQLDataAdapter in VS .NET 2003. Is this the point of sintax that occur the
error:
(...)
if object_id('tempdb.dbo.#tempprod') is not null drop table #tempprod
create table #tempprod (seq int, sit varchar(6),Atividade varchar(20),
historico varchar(6))
EXEC SP_FI_ListToTable '#tempprod','atividade','^',@.Atividade
EXEC SP_FI_ListToTable '#tempprod','sit','^', @.SituacaoProposta
EXEC SP_FI_ListToTable '#tempprod','historico','^', @.HistoricoFinanceiro
(...)
The error is "Invalid object name #tempprod" and this occurs 3 times.
Is this the full code of the SP SP_FI_ListToTable:
"ALTER PROC SP_FI_ListToTable
@.table varchar(12),
@.campo varchar(12),
@.sep char,
@.texto varchar(5000)
as
set nocount on
declare @.sub varchar(100)
declare @.posini int
declare @.seq int
select @.seq = 1
while len(@.texto) > 0
begin
select @.posini = charindex(@.sep,@.texto)
if @.posini > 0
begin
select @.sub = LEFT(@.texto,@.posini-1)
select @.texto = substring(@.texto,@.posini+1,len(@.texto))
end
else
begin
select @.sub = @.texto
select @.texto = ''
end
exec (' if exists(select * from ' + @.table + ' where seq = ' + @.seq + ')
'+
' update '+@.table + ' set ' + @.campo + ' = '''+@.sub+''' where seq =
' + @.seq +
' else ' +
' insert into '+@.table + ' (seq,'+@.campo+') values (' + @.seq +
','''+@.sub+''')')
select @.seq = @.seq + 1
End
set nocount off
return "
Tks for help.
Bruno Renato
C# .NET DeveloperHi
It is better if you do not call your stored procedure sp_...
I have not tried this through the adapter, but...
Your temporary table will be dropped when it goes out of scope, so testing
the existance should not be necessary.
You will not need to use dynamic SQL if you only access the same temporary
table from a procedure that creates the temporary table. You may want to put
begin/end around your batch.
This works fine:
DECLARE @.Atividade varchar(5000)
DECLARE @.SituacaoProposta varchar(5000)
DECLARE @.HistoricoFinanceiro varchar(5000)
SELECT @.Atividade = 'Un',
@.SituacaoProposta = 'dos',
@.HistoricoFinanceiro= 'tres'
CREATE TABLE #tempprod (seq int, sit varchar(6),Atividade varchar(20),
historico varchar(6))
EXEC uSP_FI_ListToTable '#tempprod','atividade','^',@.Atividade
EXEC uSP_FI_ListToTable '#tempprod','sit','^', @.SituacaoProposta
EXEC uSP_FI_ListToTable '#tempprod','historico','^', @.HistoricoFinanceiro
SELECT * FROM #tempprod
DROP TABLE #tempprod
You may want to look at:
http://www.sommarskog.se/arrays-in-sql.html
John
"Bruno Renato" wrote:

> Hy people, i hava a problem with add a SP with a tempory table inside in a
> SQLDataAdapter in VS .NET 2003. Is this the point of sintax that occur the
> error:
> (...)
> if object_id('tempdb.dbo.#tempprod') is not null drop table #tempprod
> create table #tempprod (seq int, sit varchar(6),Atividade varchar(20),
> historico varchar(6))
> EXEC SP_FI_ListToTable '#tempprod','atividade','^',@.Atividade
> EXEC SP_FI_ListToTable '#tempprod','sit','^', @.SituacaoProposta
> EXEC SP_FI_ListToTable '#tempprod','historico','^', @.HistoricoFinancei
ro
> (...)
> The error is "Invalid object name #tempprod" and this occurs 3 times.
> Is this the full code of the SP SP_FI_ListToTable:
> "ALTER PROC SP_FI_ListToTable
> @.table varchar(12),
> @.campo varchar(12),
> @.sep char,
> @.texto varchar(5000)
> as
> set nocount on
> declare @.sub varchar(100)
> declare @.posini int
> declare @.seq int
> select @.seq = 1
> while len(@.texto) > 0
> begin
> select @.posini = charindex(@.sep,@.texto)
> if @.posini > 0
> begin
> select @.sub = LEFT(@.texto,@.posini-1)
> select @.texto = substring(@.texto,@.posini+1,len(@.texto))
> end
> else
> begin
> select @.sub = @.texto
> select @.texto = ''
> end
> exec (' if exists(select * from ' + @.table + ' where seq = ' + @.seq + '
)
> '+
> ' update '+@.table + ' set ' + @.campo + ' = '''+@.sub+''' where seq
=
> ' + @.seq +
> ' else ' +
> ' insert into '+@.table + ' (seq,'+@.campo+') values (' + @.seq +
> ','''+@.sub+''')')
> select @.seq = @.seq + 1
> End
> set nocount off
> return "
> Tks for help.
>
> Bruno Renato
> C# .NET Developer