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?