Color column

Ramco Ramco

3y927SQL

I have below code I want if AcctCode is '10011' its color should be red

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

Thanks

6 Replies

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

Tasadduq Burney3y ago

Here's how you can modify your code to achieve this effect:

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  CASE 
                               WHEN p.AcctCode = '10011' THEN 
                                   ''
                               ELSE
                                   ''
                           END AS td,
                           p.AcctName AS td
                   FROM    Oact p
                   FOR XML RAW('tr'), ELEMENTS
               ) AS NVARCHAR(MAX))
               + N'AcctCode AcctName
' + p.AcctCode + ' ' + p.AcctCode + ''

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

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

Amit Mohanty3y ago

Hey Ramco, thy this

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''
               + ((
                   SELECT  '' + p.AcctCode + N''
                   FROM    Oact p
                   FOR XML PATH(''), TYPE
               ).value('.', 'NVARCHAR(MAX)'))
               + N'AcctCode AcctName
' + p.AcctName + N''

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

AcctCode	AcctName
' + p.AcctName + N'

Output:

Ramco Ramco3y ago
Hi Amit Mohanty

Can u pls show me the Image output

Thanks

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)
    SET @body = N''
               + N''
               + CAST((
                   SELECT  CASE WHEN p.AcctCode = '10011' THEN N''
                               ELSE N''
                           END AS td,
                           p.AcctName AS td
                   FROM    Oact p
                   FOR XML RAW('tr'), ELEMENTS
               ) AS NVARCHAR(MAX))
               + N'AcctCode AcctName
' + p.AcctCode + N' ' + p.AcctCode + N''

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

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

Amit Mohanty3y ago

ALTER PROCEDURE sp_color
AS
BEGIN
    SET NOCOUNT ON;

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

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

Abhishek Yadav3y 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  CASE WHEN p.AcctCode = '10011' THEN N'' ELSE N'' END AS td,
                           p.AcctName AS td
                   FROM    Oact p
                   FOR XML RAW('tr'), ELEMENTS
               ) AS NVARCHAR(MAX))
               + N'AcctCode AcctName
' + p.AcctCode + ' ' + p.AcctCode + ''

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