Sql-server – Dynamic Condition Creation function

sql server

I have a function which needs to create a dynamic where clause by reading the columns from the temporary table .

CREATE FUNCTION [dbo].[GetWhereClause]
(
 @TempTableName nvarchar(600)

)

RETURNS nvarchar(max)
BEGIN
DECLARE @sql as nvarchar(max)

 SET @sql='(SELECT STUFF('+'(SELECT space(2)+'+
            ''''+'and '+'''' +
            '+space(2)+'+''''+'B'+'.'+''''+'+'+'name+'+''''+
            ' = '+''''
            +'+'+''''+'A'+'.'+''''+'+'+'name+'+''''+''+''''+
            'from tempdb.sys.columns where object_id =
            object_id('+''''+'tempdb..'+@TempTableName+''''+')'+
            'FOR XML PATH ('+''''''+')),1, 5,'+ ''''''+'))'

RETURN @sql

END

code to see the ouput

     CREATE TABLE #SingleTempTable(ID  uniqueidentifier  ,Name  nvarchar    (50)COLLATE    SQL_Latin1_General_CP1_CI_AS,Age  nvarchar  (50)COLLATE  SQL_Latin1_General_CP1_CI_AS)
   DECLARE @QueryTable AS TABLE (ID SMALLINT IDENTITY (1, 1) PRIMARY KEY,Clause NVARCHAR (1000));


     DECLARE @sqlsource AS NVARCHAR (MAX) = '';

     DECLARE @whereClause AS NVARCHAR (MAX) = '';

     DECLARE @TempTableName AS NVARCHAR (MAX) = '#SingleTempTable'; 

     SET @sqlsource= [dbo].[GetWhereClause](@TempTableName,'')  

      INSERT INTO @QueryTable
      EXECUTE sp_executesql @sqlsource

      SELECT @whereClause=Clause FROM @QueryTable

      SELECT @whereClause

Is there any better way I can write this line in function

 SET @sql='(SELECT STUFF('+'(SELECT space(2)+'+
            ''''+'and '+'''' +
            '+space(2)+'+''''+'B'+'.'+''''+'+'+'name+'+''''+
            ' = '+''''
            +'+'+''''+'A'+'.'+''''+'+'+'name+'+''''+''+''''+
            'from tempdb.sys.columns where object_id =
            object_id('+''''+'tempdb..'+@TempTableName+''''+')'+
            'FOR XML PATH ('+''''''+')),1, 5,'+ ''''''+'))'

Now its not in a good format and its difficult to understand

Best Answer

How about this?

set @sql = '(SELECT STUFF((
              SELECT space(2)+''and ''+space(2)+''B.''+name+'' = ''+''A.''+name 
              from tempdb.sys.columns 
              where object_id = object_id(''tempdb..'+quotename(@TempTableName)+''')
              FOR XML PATH ('''')),1, 5,''''))'