CODEWITHSUNDEEP
sqlsql-servert-sql
variable
variable

Reputation: 9736

What is a better alternative to procedural approach to produce a string based on columns?

I am working on making string based on column values. Example:

SELECT @Registered = a.hasRegistered
    ,@Subscribed = a.hasSubscribed
FROM AuthorData a
WHERE a.authorId = 10 --@AUTHORID

IF (@Registered  = 1)
    SET @ReturnString = @ReturnString + '0,'

IF (@Subscribed = 1)
    SET @ReturnString = @ReturnString + '1,'

IF EXISTS (
        SELECT TOP 1 name
        FROM AUTHORDATA AS A
            INNER JOIN AUHTORPROFILE B on A.AUTHORID=B.AUTHORID
            INNER JOIN AUTHORHISTORY C ON B.AUTHORPROFILEID=C.AUTHORPROFILEID
        WHERE ISNULL(AUTHORDATA.authorId, 0) = 10 --@AUTHORID
        )
BEGIN
    SET @ReturnString = @ReturnString + '10,'
END

select CONVERT(NVARCHAR(50), STUFF(@ReturnString, LEN(@ReturnString), 1, ''))

This performs the calculation for 1 author (based on WHERE clause IN 2 places -> authorId=10)

I can make this into a function and then call from a select query as follows:

SELECT *,FN_CALCUALTE_OPTIONS(AUTHORID)
FROM AUTHORDATA

I want to ask if there is any way so I can do the calculations in the above SELECT query rather than create the function?

I have tried:

SELECT *,CASE WHEN A.hasRegistered=1 THEN '0,' ELSE '' END + 
    CASE WHEN A.hasSubscribed=1 THEN '1,' ELSE '' END
FROM AUTHORDATA as A

How can I have the exists part of select?

Upvotes: 0

Views: 75

Answers (3)

Dale K
Dale K

Reputation: 27472

Pretty sure you can put them all together into a single query as follows. If you didn't need the final stuff you would just have a regular query, but because you need to use the results twice in the stuff CROSS APPLY is a convenient way to calculate it once and use it twice. Also you can correlate your sub-query since you are using the same AuthorData record.

SELECT CONVERT(NVARCHAR(50), STUFF(X.ReturnString, LEN(X.ReturnString), 1, ''))
FROM AuthorData a
CROSS APPLY (
    VALUES
    (
        CASE WHEN a.hasRegistered = 1 THEN '0,' ELSE '' END
        + CASE WHEN a.hasSubscribed = 1 THEN '1,' ELSE '' END
        + CASE WHEN EXISTS (
            SELECT 1
            FROM AUHTORPROFILE B
            INNER JOIN AUTHORHISTORY C ON B.AUTHORPROFILEID = C.AUTHORPROFILEID
            WHERE B.AUTHORID = a.AuthorId
        )
        THEN '10,' else '' end
    )
) AS X (ReturnString)
WHERE a.AuthorId = 10 --@AUTHORID

Upvotes: 2

trenton-ftw
trenton-ftw

Reputation: 972

The easiest way IMO is to move your criteria being found via EXISTS to an OUTER APPLY. Like so:

SELECT 
    CONVERT(NVARCHAR(50), STUFF(calc.ReturnString, LEN(calc.ReturnString), 1, ''))
FROM 
    AUTHORDATA AS A
    OUTER APPLY (SELECT TOP (1) 
                    1 AS Found 
                FROM 
                    AUHTORPROFILE AS B 
                    INNER JOIN AUTHORHISTORY AS C ON B.AUTHORPROFILEID = C.AUTHORPROFILEID
                WHERE 
                    B.authorId = A.authorId) AS lookup_author
    /*outer apply here just for readibility in final select*/
    OUTER APPLY (SELECT 
                    CONCAT(CASE WHEN A.hasRegistered = 1 THEN '0,' ELSE '' END
                          ,CASE WHEN A.hasRegistered = 1 THEN '1,' ELSE '' END
                          ,CASE WHEN lookup_author.Found = 1 THEN '10,' ELSE '' END) AS ReturnString) AS calc;

Then you can use lookup_author.Found = 1 to determine that it was found in your lookup. From there, you just have to apply the rest of your conditions correctly via CASE statements and then use your final SELECT over the result.

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 13009

You can put a single CASE statement and get data as given below. Make sure that you are covering every possible condition.

SELECT *, CASE WHEN a.Registered =1 AND a.Subscribed =1  
               AND EXISTS (SELECT TOP 1 1
               FROM AUHTORPROFILE B on 
               INNER JOIN AUTHORHISTORY C ON B.AUTHORPROFILEID=C.AUTHORPROFILEID
               WHERE B.AUTHORID =A.AUTHORID) THEN '0,1,10'

               WHEN a.Registered =1 AND a.Subscribed =0  
               AND EXISTS (SELECT TOP 1 1
               FROM AUHTORPROFILE B on 
               INNER JOIN AUTHORHISTORY C ON B.AUTHORPROFILEID=C.AUTHORPROFILEID
               WHERE B.AUTHORID =A.AUTHORID) THEN '0,10'

               WHEN a.Registered =0 AND a.Subscribed =1  
               AND EXISTS (SELECT TOP 1 1
               FROM AUHTORPROFILE B on 
               INNER JOIN AUTHORHISTORY C ON B.AUTHORPROFILEID=C.AUTHORPROFILEID
               WHERE B.AUTHORID =A.AUTHORID) THEN '1,10'

               WHEN a.Registered =0 AND a.Subscribed =0  
               AND EXISTS (SELECT TOP 1 1
               FROM AUHTORPROFILE B on 
               INNER JOIN AUTHORHISTORY C ON B.AUTHORPROFILEID=C.AUTHORPROFILEID
               WHERE B.AUTHORID =A.AUTHORID) THEN '10'
               END AS CalculateOptions

FROM AUTHORDATA AS a

Upvotes: 0

Related Questions