Displaying No Data

Ramco Ramco

3y912SQL

I have beow query but it is not displaying any Data.

Alter PROCEDURE sp_color 
	-- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1>  = , 
	--<@Param2, sysname, @p2>  = 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX)
SET     @body = N''
    + N''
    + CAST((
        SELECT  p.AcctCode AS td,
                p.AcctName AS td
        FROM    Oact p
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'AcctCode AcctName'
END
GO

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Cr Bhargavi3y ago

ALTER PROCEDURE sp_color
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX)
    SET @body = N''
               + N''
               + CAST((
                   SELECT  p.AcctCode AS td,
                           p.AcctName AS td
                   FROM    Oact p
                   FOR XML RAW('tr'), ELEMENTS
               ) AS NVARCHAR(MAX))
               + N'AcctCode AcctName'

    -- Return the generated HTML content
    SELECT @body AS HtmlContent
END

Tahir Ansari3y ago

ALTER PROCEDURE sp_color
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX);
    
    -- Initialize @body with the opening  tag and header row
    SET @body = N''
        + N'';

    -- Fetch data from the Oact table and add rows to @body
    SELECT @body = @body + 
        N''
    FROM Oact p;

    -- Close the AcctCode AcctName
' + p.AcctCode + N' ' + p.AcctName + N' tag
    SET @body = @body + N'';

    -- Output the HTML content
    SELECT @body AS HtmlOutput;
END;
GO

-- Execute the stored procedure to see the HTML output
EXEC sp_color;

AcctCode	AcctName
' + p.AcctCode + N'	' + p.AcctName + N'

Amit Mohanty3y ago

Missing Output for HTML Table:

ALTER PROCEDURE sp_color
    -- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1>  = ,
	--<@Param2, sysname, @p2>  = 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX);
    SET @body = N''
        + N''
        + CAST((
            SELECT  p.AcctCode AS td,
                    p.AcctName AS td
            FROM    Oact p
            FOR XML RAW('tr'), ELEMENTS
        ) AS NVARCHAR(MAX))
        + N'AcctCode AcctName';

    -- Print or return the generated HTML
    PRINT @body;  -- This will print the HTML to the Messages tab in SQL Server Management Studio

    -- Alternatively, you can return the HTML as a result set
    SELECT @body AS HTML;
END

Lokesh Varman3y ago

ALTER PROCEDURE sp_color
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @body NVARCHAR(MAX)
    SET @body = N''
               + N''
               + CAST((
                   SELECT  p.AcctCode AS td,
                           p.AcctName AS td
                   FROM    Oact p
                   FOR XML RAW('tr'), ELEMENTS
               ) AS NVARCHAR(MAX))
               + N'AcctCode AcctName'
    
    -- Return the generated HTML content
    SELECT @body AS HtmlContent
END
GO

Try the above code and let me know if you have any doubts.