x

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.

more ▼

asked Nov 25, 2009 at 09:14 AM in Default

avatar image

naveenkumart
23 3 3 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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'')'            
more ▼

answered Nov 25, 2009 at 09:28 AM

avatar image

RickD
1.7k 2 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x1089
x450
x58

asked: Nov 25, 2009 at 09:14 AM

Seen: 4067 times

Last Updated: Nov 25, 2009 at 01:48 PM

Copyright 2017 Redgate Software. Privacy Policy