I am trying to generate a string in SQL Server with a contingent using a Stored Procedure.
Lets say I have a table as

ID   FileKey
1    AEDAT
2    ERDAT

Now I want to generate a string as follows:

AEDAT GT LastUpdatedValue OR ERDAT GT LastUpdatedValue

Now if the above table contains only one row (With FileKey) AEDAT then the resultant string should be

AEDAT GT LastUpdatedValue  <---No OR operator

And if there are, say, 3 or 4 rows in the above table then the resultant string would be like :

AEDAT GT LastUpdatedValue OR ERDAT GT LastUpdatedValue OR MCVBL GT LastUpdatedValue...

Now to achieve the same I am taking the following approach:

DECLARE @ColumnName varchar(50)
SELECT @ColumnName = STRING_AGG(x.ColumnName   'GT LastUpdatedValue',' OR ') FROM <table_name> x;

With the above I have two questions:

1. The above is not working i.e. I am not getting @ColumnName values as AEDAT,ERDAT. What I am missing? --- This one is resolved.
2. Assume that I get @ColumnName as AEDAT,ERDAT. How can I generate the dynamic and contingent string as given above? --- This is also now resolved.

CodePudding user response：

It looks like you need the following

DECLARE @ColumnName varchar(50) = (
    SELECT
      STRING_AGG(CAST(x.ColumnName   ' GT LastUpdatedValue' AS varchar(max)), ' OR ')
          WITHIN GROUP (ORDER BY x.ID)
    FROM <table_name> x
);

You need to cast to (max) otherwise it gets cut off at 8000 characters (or 4000 for nvarchar)

CodePudding user response：

   declare @t table(ID int,   FileKey varchar(20))
    insert into @t values
    (1,   'AEDAT'),
    (2 ,   'ERDAT')
    
    
    SELECT TOP 1
(
    SELECT FileKey   ' GT LastUpdatedValue'   CASE
                                                  WHEN id < 2
                                                  THEN ' OR '
                                                  ELSE ''
                                              END
    FROM @t t1 FOR XML PATH('')
)
FROM @t t;

if you share exact table and requirement then logic like WHEN id < 2 can be handle in some other dynamic and efficient way. Removing last OR with string manipulation may be lengthy.