SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Using GETDATE inside dynamic sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/12/2014 :  19:31:56  Show Profile  Reply with Quote
I'm trying to add getdate(), using a variable, to following query:


DECLARE @total int, @id int, @sql nvarchar(MAX)
DECLARE @dbname varchar(200)
DECLARE @MetricDate datetime;
  
DECLARE @t table(ID int not null identity(1,1), name varchar(255)) 
INSERT INTO @t(name) 
SELECT name FROM sys.databases WHERE database_id > 4  ORDER BY name;
  
SET @total = @@ROWCOUNT 
SET @id=1 
WHILE @id <= @total 
BEGIN
    SELECT @dbname = name FROM @t WHERE ID = @id 
    SET @sql =  
    '  
    USE [' + @dbname + ']; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
    , s.rowcnt AS ''RowCount'' 
    , s.rowmodctr AS ''RowModified'' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''% RowsChanged'' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    '
	EXECUTE sp_executesql @sql
			,N'@dbname varchar(200), @MetricDate datetime'	-- these are all the parameters
			,@dbname, @MetricDate;							-- these are the corresponding variables for previous parameters.
    SET @id = @id + 1 
END


... that works, but this one, does not ...


DECLARE @total int, @id int, @sql nvarchar(MAX)
DECLARE @dbname varchar(200)
DECLARE @MetricDate datetime;
  
DECLARE @t table(ID int not null identity(1,1), name varchar(255)) 
INSERT INTO @t(name) 
SELECT name FROM sys.databases WHERE database_id > 4  ORDER BY name;
  
SET @total = @@ROWCOUNT 
SET @id=1 
WHILE @id <= @total 
BEGIN
    SELECT @dbname = name FROM @t WHERE ID = @id 
    SET @sql =  
    '  
    USE [' + @dbname + ']; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
	, ' + convert(varchar, @MetricDate, 121 ) + 'AS MetricDate
    , s.rowcnt AS ''RowCount'' 
    , s.rowmodctr AS ''RowModified'' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''% RowsChanged'' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    '
	EXECUTE sp_executesql @sql
			,N'@dbname varchar(200), @MetricDate datetime' 
			,@dbname, @MetricDate;			
    SET @id = @id + 1 
END


It does not generate an error but now it does not execute, it does nothing.

I know the dynamic sql query must be a string, so I need to cast or convert the datetime variable so it will work. But it is not working.

I know I can simply use getdate() and will work, but this will be used in a loop, so the collected date will be different for each database.

Any hints?

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/12/2014 :  19:33:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Define "it is not working". I don't even see GETDATE() being used in your query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/12/2014 :  23:22:54  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Define "it is not working". I don't even see GETDATE() being used in your query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



sorry, I mean, this line ...


, ' + convert(varchar, @MetricDate, 121 ) + 'AS MetricDate


where @MetricDate is getdate()
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/13/2014 :  11:38:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
, ' + convert(varchar(30), @MetricDate, 121 ) + ' AS MetricDate

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/13/2014 :  13:08:42  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

, ' + convert(varchar(30), @MetricDate, 121 ) + ' AS MetricDate

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thanks for reply, Tara,

But still not working ... :-(


Here's the complete code:


DECLARE @total int, @id int, @sql nvarchar(MAX)
DECLARE @dbname varchar(200)
DECLARE @MetricDate datetime;
  
DECLARE @t table(ID int not null identity(1,1), name varchar(255)) 
INSERT INTO @t(name) 
SELECT name FROM sys.databases WHERE database_id > 4  ORDER BY name;
  
SET @total = @@ROWCOUNT 
SET @id=1 
WHILE @id <= @total 
BEGIN
    SELECT @dbname = name FROM @t WHERE ID = @id 
    SET @sql =  
    '  
    USE [' + @dbname + ']; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
	, ' + convert(varchar, @MetricDate, 121 ) + 'AS MetricDate
    , s.rowcnt AS ''RowCount'' 
    , s.rowmodctr AS ''RowModified'' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''% RowsChanged'' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    '
	EXECUTE sp_executesql @sql
			,N'@dbname varchar(200), @MetricDate datetime' 
			,@dbname, @MetricDate;			
    SET @id = @id + 1 
END


If I remove this line:


	, ' + convert(varchar, @MetricDate, 121 ) + 'AS MetricDate


The query runs and the SELECT statement loops into all my databases.

Am I missing something here?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/13/2014 :  13:16:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:

But still not working


Please elaborate and show us the output of PRINT @sql for when it's not working.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/13/2014 :  13:26:26  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

quote:

But still not working


Please elaborate and show us the output of PRINT @sql for when it's not working.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



No output, nothing, nada.. :-(

If I do a select @sql , it returns null 17 times, one for each database on my Dev box. Means, the string contains no data.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/13/2014 :  13:28:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
Oh I see the issue. You haven't set @MetricDate to anything.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/13/2014 :  14:28:28  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Oh I see the issue. You haven't set @MetricDate to anything.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/





We are getting there.

I changed it to:


DECLARE @total int, @id int, @sql nvarchar(MAX)
DECLARE @dbname varchar(200)
DECLARE @MetricDate datetime=GETDATE();
  
DECLARE @t table(ID int not null identity(1,1), name varchar(255)) 
INSERT INTO @t(name) 
SELECT name FROM sys.databases WHERE database_id > 4  ORDER BY name;
  
SET @total = @@ROWCOUNT 
SET @id=1 
WHILE @id <= @total 
BEGIN
    SELECT @dbname = name FROM @t WHERE ID = @id 
    SET @sql =  
    '  
    USE [' + @dbname + ']; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
	, ' + convert(varchar, @MetricDate, 121 ) + 'AS MetricDate
    , s.rowcnt AS ''RowCount'' 
    , s.rowmodctr AS ''RowModified'' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS ''% RowsChanged'' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    '
	PRINT @sql
	EXECUTE sp_executesql @sql
			,N'@dbname varchar(200), @MetricDate datetime' 
			,@dbname, @MetricDate;			
    SET @id = @id + 1 
END


And now I am getting this error:


Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '11'.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/13/2014 :  14:30:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'll need to see the output of PRINT @sql.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/13/2014 :  14:35:12  Show Profile  Reply with Quote

(17 row(s) affected)
  
    USE [xxxxxx]; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
	, 2014-06-13 11:29:14.803AS MetricDate
    , s.rowcnt AS 'RowCount' 
    , s.rowmodctr AS 'RowModified' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS '% RowsChanged' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '11'.
  
    USE [xxxxxx]; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
	, 2014-06-13 11:29:14.803AS MetricDate
    , s.rowcnt AS 'RowCount' 
    , s.rowmodctr AS 'RowModified' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS '% RowsChanged' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '11'.
  
    USE [xxxxxx]; 
    SELECT  
      DB_NAME() AS DBName        
    , ss.name AS SchemaName 
    , st.name AS TableName 
    , s.name AS IndexName 
    , si.type_desc AS IndexType 
    , STATS_DATE(s.id,s.indid) AS StatsLastTaken 
	, 2014-06-13 11:29:14.803AS MetricDate
    , s.rowcnt AS 'RowCount' 
    , s.rowmodctr AS 'RowModified' 
    , CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS '% RowsChanged' 
    FROM sys.sysindexes s 
        INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
        INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
        INNER JOIN sys.indexes si ON si.object_id = s.id 
            AND si.name = s.name 
    --WHERE s.id > 100 
    WHERE s.indid > 0 
    AND s.rowcnt >= 500 
    
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '11'.


That repeats 17 times. One for each database or client on my Dev box. And there is an error on each of them.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/13/2014 :  14:36:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use this: , ''' + convert(varchar, @MetricDate, 121 ) + ''' AS MetricDate

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sql-lover
Yak Posting Veteran

86 Posts

Posted - 06/13/2014 :  14:41:14  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Use this: , ''' + convert(varchar, @MetricDate, 121 ) + ''' AS MetricDate

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Fixed!

Wow ... I need to go back and revise the proper syntax for dynamic queries when escaping characters. It can be tricky.

Thanks a lot! That was a silly but tricky mistake.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/13/2014 :  14:44:36  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 06/17/2014 :  02:24:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You may be interested to read this to know how single quotes work http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000