Displaying No Data

Question

Hi

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> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
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'<table>'
    + N'<tr><th>AcctCode</th><th>AcctName</th></tr>'
    + CAST((
        SELECT  p.AcctCode AS td,
                p.AcctName AS td
        FROM    Oact p
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'
END
GO

Thanks

Amit Mohanty · Answer

Missing Output for HTML Table: ALTER PROCEDURE sp_color -- Add the parameters for the stored procedure here -- = , -- = 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' AcctCode AcctName ' + CAST(( SELECT p.AcctCode AS td, p.AcctName AS td FROM Oact p FOR XML RAW('tr'), ELEMENTS ) AS NVARCHAR(MAX)) + N' '; -- 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

Tahir Ansari · Answer

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' AcctCode AcctName '; -- Fetch data from the Oact table and add rows to @body SELECT @body = @body + N' ' + p.AcctCode + N' ' + p.AcctName + N' ' FROM Oact p; -- Close the 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;

Lokesh Varman · Answer

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' AcctCode AcctName ' + CAST(( SELECT p.AcctCode AS td, p.AcctName AS td FROM Oact p FOR XML RAW('tr'), ELEMENTS ) AS NVARCHAR(MAX)) + N' ' -- Return the generated HTML content SELECT @body AS HtmlContent END GO Try the above code and let me know if you have any doubts.

Cr Bhargavi · Answer

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' AcctCode AcctName ' + CAST(( SELECT p.AcctCode AS td, p.AcctName AS td FROM Oact p FOR XML RAW('tr'), ELEMENTS ) AS NVARCHAR(MAX)) + N' ' -- Return the generated HTML content SELECT @body AS HtmlContent END