Post reply

Is there a way to rewrite sql statement to minimize guage of the complexity

  • July 5, 2016 at 9:11 am

    #327506

    SELECT

    ISNULL(ED.descr,'') + CASE WHEN ED.descr IS NOT NULL AND (ED.Date IS NOT NULL OR ED.Text IS NOT NULL OR dbo.GetInfo(ED.Index) <> '') THEN '\\' ELSE '' END +

    ISNULL(CONVERT(varchar(50), ED.Date, 107),'') + CASE WHEN ED.Date IS NOT NULL THEN '\\' ELSE '' END +

    ISNULL(ED.Text,'') + CASE WHEN ED.Text IS NOT NULL AND dbo.GetInfo(ED.Index) <> '' THEN '\\' ELSE '' END +dbo.GetInfo(ED.Index) AS Description,

    From Detail ED

  • July 5, 2016 at 9:46 am

    #1888496

    From what I understand, you're concatenating 4 values delimited by double backslashes.

    Here's an option, it adds the delimiter before each value and uses STUFF to remove the first delimiter:

    SELECT STUFF( ISNULL('\\' + ED.descr,'')

    + ISNULL('\\' + CONVERT(varchar(50), ED.Date, 107),'')

    + ISNULL('\\' + ED.Text,'')

    + ISNULL('\\' + NULLIF( dbo.GetInfo(ED.Index), ''), ''), 1, 2, '') AS Description,

    From Detail ED

    Remember that scalar user-defined functions can kill performance even if used in the column list. The least it would do, is to avoid parallelism and be called once for every row returned. Read the following which might help: http://www.sqlservercentral.com/articles/T-SQL/91724/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 5, 2016 at 9:59 am

    #1888503

    Thank you for the reply,So you are saying to not to use scalar functions in the column list?

    Is it good or bad to use?

  • July 5, 2016 at 10:15 am

    #1888510

    Using user-defined scalar functions (anywhere) is bad.

    Exceptions are possible but thorough testing must be done to confirm that, so just keep in mind the previous sentence.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 5, 2016 at 10:16 am

    #1888511

    TY So much.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply