Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Using GETDATE inside dynamic sql

Author  Topic 

sql-lover
Yak Posting Veteran

99 Posts
Posted - 2014-06-12 : 19:31:56
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

38200 Posts
Posted - 2014-06-12 : 19:33:41
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

99 Posts
Posted - 2014-06-12 : 23:22:54
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

38200 Posts
Posted - 2014-06-13 : 11:38:39
, ' + 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

99 Posts
Posted - 2014-06-13 : 13:08:42
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

38200 Posts
Posted - 2014-06-13 : 13:16:38
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

99 Posts
Posted - 2014-06-13 : 13:26:26
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

38200 Posts
Posted - 2014-06-13 : 13:28:46
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

99 Posts
Posted - 2014-06-13 : 14:28:28
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

38200 Posts
Posted - 2014-06-13 : 14:30:50
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

99 Posts
Posted - 2014-06-13 : 14:35:12
[code]
(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'.
[/code]

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

38200 Posts
Posted - 2014-06-13 : 14:36:05
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

99 Posts
Posted - 2014-06-13 : 14:41:14
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

38200 Posts
Posted - 2014-06-13 : 14:44:36


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

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2014-06-17 : 02:24:54
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site