1

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

1

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,''''))'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.