December 10, 2011 at 8:13 am
Hello,
The following stored procedure is working just right in Microsoft SQL Server Studio, returning the records in the grid correctly.
What I'm not able to, for any error probably is that the program in Delphi, there are no records being returned, as if the procedure returns empty. I did a test with another procedure that returns a record of a simple select on a table (without using dynamic sql) and return to work program showing the records.
What is wrong for me to return the records in the procedure below?
ALTER PROCEDURE [markbook].[Atividades]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Atividades table
(usuid int,
usunome nvarchar(50),
usuemail nvarchar(50),
atvdesc nvarchar(200),
atvdthrini smalldatetime,
atvdthrfin smalldatetime)
-- Monta uma string para concatenação dos comandos
DECLARE @Tabelas VARCHAR(MAX)
SET @Tabelas = (
SELECT 'SELECT usuid, atvdesc, atvdthrini, atvdthrfin, atvlemb FROM ' + SCHEMA_NAME(SCHEMA_ID) + '.Atividade UNION ALL ' As Nome FROM sys.tables
WHERE name = 'Atividade'
FOR XML RAW('Tabela'),ROOT('Tabelas'))
SET @Tabelas =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@Tabelas,'<Tabela Nome="',CHAR(10))
,'"/>','')
,' UNION ALL </Tabelas>','')
,'<Tabelas>','')
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = ';WITH Atividades As (' + @Tabelas + ')' + CHAR(10) + CHAR(10)
SET @cmd = @cmd + 'SELECT a.USUID, u.USUNOME, u.USUEMAIL, a.ATVDESC, a.ATVDTHRINI, a.ATVDTHRFIN FROM Atividades As a
INNER JOIN markbook.Usuario As u ON a.USUID = u.USUID
where a.USUID = 3
and a.ATVLEMB <> ''Não Lembrar'''
insert into @Atividades
exec (@cmd)
select * from @Atividades
END
December 14, 2011 at 10:09 am
Any idea for my problem?
Thanks.
December 14, 2011 at 10:11 am
What error does it return?
Why do you do html formatting in a sp? It should be done client side, not server (sql server) side.
January 6, 2012 at 2:14 am
The expression SCHEMA_NAME(SCHEMA_ID) throws an error, you would need parens after SCHEMA_ID, i.e., SCHEMA_NAME(SCHEMA_ID()), though that would be redundant, just SCHEMA_NAME() returns the same thing. Both return the default schema of the caller if no parameter is passed.
If that's not it, as a debugging measure try returning the dynamically generated SQL statement to the app, to make sure it's being rendered as you expect.
-MM
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply