question

naveenkumart avatar image
naveenkumart asked

Place multiline T_SQL Query in a variable

                    
declare @query varchar(8000)                    
set @query = ''

I want to pass the below query to a variable (@query), here @dbid and @dbname are other variables.

                    
"select @dbname,object_NAME(DM.[object_id],@dbid), SI.[name], index_type_desc,                    
avg_fragmentation_in_percent                    
from sys.dm_db_index_physical_stats (@dbname,null,null,null,null) as [DM]                    
inner join sys.indexes [SI]                     
    on DM.object_id = SI.object_ID and DM.index_id = SI.index_id                    
where --DM.avg_fragmentation_in_percent > 30                    
DM.avg_fragmentation_in_percent between 15 and 30                    
and index_type_desc <> 'HEAP'                    
and SI.object_id NOT IN                    
(SELECT ID                     
FROM SYSOBJECTS                     
WHERE XTYPE = 'U'                     
      AND CONVERT(VARCHAR,CRDATE,101) = '04/30/2009')"

How should i pass the above statement to @query variable.

sql-server-2005t-sqlquerydynamic
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
RickD avatar image
RickD answered 
set @Query = 'select ' + @dbname + '            
    				,object_NAME(DM.[object_id],' + @dbid + ')            
    				,SI.[name]            
    				,index_type_desc            
    				,avg_fragmentation_in_percent             
    		from sys.dm_db_index_physical_stats (' + @dbname + ',null,null,null,null) as [DM]             
    		inner join sys.indexes [SI]             
    			on DM.object_id = SI.object_ID             
    			and DM.index_id = SI.index_id             
    		where --DM.avg_fragmentation_in_percent > 30             
    			DM.avg_fragmentation_in_percent between 15 and 30             
    		and index_type_desc <> ''HEAP''             
    		and SI.object_id NOT IN (SELECT ID FROM SYSOBJECTS WHERE XTYPE = ''U''             
    		AND CONVERT(VARCHAR,CRDATE,101) = ''04/30/2009'')'
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.