SQLTeam.com | Weblogs | Forums

Dynamic SQL


#1

Please take a look at the code and help me on the last part --put all together = fail !!!
I wanted to generate a string for executing sp_executesql but I'm getting an error at the end
I also tried open cursor but it error out too
basically I'm trying to get actual row count per column per table for all tables
because general row count want give you if new column added and populated

Thanks,
Ed Dror
USE AdventureWorks2014
GO
-- Create view to hold the dataset
Alter view vColumnSchema
AS
with myColumnName
As
(
Select TOP (100) PERCENT
GetDate() As create_date,
@@SERVERNAME As Server_Name,
DB_Name() AS database_name,
c.[object_id],
s.name AS [schema_name],
t.name as table_name,
c.name as column_name,
p.rows AS NUM_ROWS,
c.[precision]
from sys.tables as t
INNER JOIN sys.columns as c with(nolock) on t.[object_id] = c.[object_id]
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.name NOT LIKE 'dt%'
AND i.object_id > 255
AND i.index_id <= 1
And s.name is not null
Order by s.name,
t.name
)
Select Top 100 percent
create_date,
Server_Name,
database_name,
[schema_name],
table_name,
column_name,
NUM_ROWS,
case when precision = 0 then
'Select Count('+column_name+')'+' from ' +[schema_name]+'.'+table_name+ ' where '+column_name+'<> '''' '
else
'Select Count(' +column_name+')'+' from ' +[schema_name]+ '.'+table_name+' where '+column_name+'> 0'
END as ColumnCount,
case when precision = 0
then ' <> '''' '
Else '> 0'
END As WhereClause
from myColumnName
Order by [schema_name],
table_name

GO
select * from vColumnSchema
where table_name = 'ProductCostHistory'
go

select * from Production.ProductCostHistory -- 395 records
--pick one record and test it = pass
Select Count(EndDate) from Production.ProductCostHistory where EndDate<> '' --200 records

GO
--test = pass on both whereClause
Declare @Column_name nvarchar(50) = 'EndDate'
Declare @Schema_Name nvarchar(20) = 'Production'
declare @table_name nvarchar(50) = 'ProductCostHistory'
declare @whereClause nvarchar(5) = '<> '''' '
declare @sqltext nvarchar(max) ='
select count('+@Column_name+') as ColumnCount from ' +@Schema_Name+'.'+@table_name+' where '+@Column_name+' '+@WhereClause+'
'
EXECUTE sp_executesql @sqltext
GO
--examine each column = pass
Declare @Column_name nvarchar(50) = 'select Column_name from vColumnSchema'
EXECUTE sp_executesql @Column_name
GO
Declare @schema_name nvarchar(50) = 'select schema_name from vColumnSchema'
EXECUTE sp_executesql @schema_name
GO
declare @table_name nvarchar(50) = 'select table_name from vColumnSchema'
EXECUTE sp_executesql @table_name
GO
declare @WhereClause nvarchar(50) = 'select WhereClause from vColumnSchema'
EXECUTE sp_executesql @WhereClause
GO
--put all together = fail !!!
declare @Column_name nvarchar(50) = 'select Column_name from vColumnSchema'
declare @schema_name nvarchar(50) = 'select schema_name from vColumnSchema'
declare @table_name nvarchar(50) = 'select table_name from vColumnSchema'
declare @WhereClause nvarchar(5) = 'select WhereClause from vColumnSchema';
declare @sqltext nvarchar(max) ='
select count('+@Column_name+') as ColumnCount from ' +@Schema_Name+'.'+@table_name+' where '+@Column_name+' '+@WhereClause+'
'
EXECUTE sp_executesql @sqltext
go


#2

I have a number of observation :smile:

INNER JOIN sys.columns as c with (nolock)

Don't use NOLOCK . not EVER. If you think you need to use NOLOCK ask me first, you will find that I am hard to persuade and you will have to make a case to me for how it can never fail in a way that could cause end users to make a business-critical decision based on, avoidable, bad data.

CREATE VIEW vColumnSchema
Select TOP (100) PERCENT
...
ORDER BY ...

Don't use ORDER BY in Views. It is not supported, and the TOP (100) PERCENT is not guaranteed [to make the ORDER BY work]. Just do the ORDER BY in the actual query.

By the by in your first query, after the ALTER VIEW, you have SELECT TOP 100 PERCENT. You don't need that, although its not doing any harm (and maybe it is there so you can adjust it to get a smaller sample of data during DEV?)

Select Count(EndDate) 
from Production.ProductCostHistory 
where EndDate <> '' --200 records

I would use COUNT(*) rather than Count(EndDate) because Count(EndDate) will not include any row where EndDate IS NULL. Your WHERE clause (EndDate <> '') will NOT select any row where EndDate IS NULL, so its a moot point, but if it is your habit to use that style it might be worth being aware of. There again, maybe you specifically want to count EndDate EXCEPT where it is NULL - but that won't exclude EndDate = ""

where EndDate <> ''

Sorry, but Yuck! EndDate should be a Date or DateTime datatype. Storing dates in text strings is really REALLY bad. (if you want reasons why please ask). You should store NULL in EndDate if it has no value (and then COUNT(EndDate) will tell you how many actual values you have :smile:

declare @sqltext nvarchar(max) ='
select count('+@Column_name+') as ColumnCount 
from ' +@Schema_Name+'.'+@table_name+
' where '+@Column_name+' '+@WhereClause+'
'
EXECUTE sp_executesql @sqltext 

I addways add a

PRINT @sqltext

statement (just above EXECUTE) to check what the content of @sqltext looks like. I would then comment-out the PRINT when DEV was completed.

If you add that into your final "--put all together = fail !!!" you will see that it produces this:

select count(select Column_name from vColumnSchema) as ColumnCount 
from 
select schema_name 
from vColumnSchema.select table_name 
from vColumnSchema 
where select Column_name from vColumnSchema selec 

There are several issues here, but one is the truncation at the end caused by

declare @WhereClause nvarchar(5) 
    = 'select WhereClause from vColumnSchema';

although that might be a cut&paste error into this post, and your original might actually be nvarchar(50)?

Personally I don't think there is any penalty to declaring VARCHAR(8000) and Nvarchar(4000) (whereas just 1 bigger than that requires using Streams, and IS likely to effect performance), so where the size is "somewhat uncertain" I use that large size - as distinct from a @Variable used to handle a specific database column, in which case I am likely to size it to exactly match that column's definition)

declare @sqltext nvarchar(max) ='
select count('+@Column_name+') as ColumnCount 
from ' +@Schema_Name+'.'+@table_name+
' where '+@Column_name+' '+@WhereClause+'
'

I would be inclined to use QuoteName() for schema / table / column thus:

declare @sqltext nvarchar(max) ='
select count('+QuoteName(@Column_name)+') as ColumnCount 
from ' +QuoteName(@Schema_Name)+'.'+QuoteName(@table_name)+
' where '+QuoteName(@Column_name)+' '+@WhereClause+'
'

which, notwithstanding other comments about this code, will mean that if you have a table/column name that includes characters other than "A-Z", "0-9" and "_" they won't cause a syntax error. For example if you had a column / table name containing a space such as "Product Cost History"

P.S. For this type of dynamic SQL it makes a very significant difference if you parametrise your query. You need to make string-manipulation, as you have done, for table / column names etc. but for any data values you should use a parameter.

For example lets' say that your @sqltext was

select count(*) as ColumnCount 
from Production.ProductCostHistory 
where EndDate <> ''

I would change that to

select count(*) as ColumnCount 
from Production.ProductCostHistory 
where EndDate <> @MyEndDate

and then use this syntax to execute it

EXECUTE sp_executesql
    @sqltext 
    N'@MyEndDate varchar(20),
    @MyEndDate

this does two things:

Firstly the actual SQL query, @sqltext, is now NOT specific to a particular value of @MyEndDate. SQL will cache the query plan; each time you run the query @sqltext will be identical, so SQL will reuse the query plan (i.e. it won't have to make a new one), and that will improve performance - often A LOT

Secondly, it doesn't matter what characters @MyEndDate contains. If you use string manipulation, as you have it, the value of @MyEndDate can interfere with the syntax of your code. This is particularly important if End Users will provide values for @MyEndDate which is something referred to as "SQL Injection". For a seriously outrageous example say a user inputs the End Date as

';drop database YourDatabase;select '

your combined @sqltext is now

select count(*) as ColumnCount 
from Production.ProductCostHistory 
where EndDate <> '';drop database YourDatabase;select ''

whereas if you use

EXECUTE sp_executesql
    @sqltext 
    N'@MyEndDate varchar(20),
    @MyEndDate

the query which SQL will actually execute will be

select count(*) as ColumnCount 
from Production.ProductCostHistory 
where EndDate <> ''';drop database YourDatabase;'''

which is clearly daft! but is, at least, benign.

Hope that is of some help


#3

I understood ur last query i think it would be better
to write it this way

DECLARE @Query NVARCHAR(max) = '';

SELECT @Query =
@Query + ' select count(' + column_name + ') as ColumnCount from ' + schema_name + '.' +
table_name + ' where ' + column_name + ' ' + whereclause + ';'
FROM vColumnSchema

Where i am stuck is .. is that the sql text for @Query
being generated is very huge ..


#4

Looks to me as though it is supposed to use the earlier queries to get specific queries for the final query.

So I think the earlier queries should select a single column name & table from [vColumnSchema] and then use that to construct a "narrow" final query.


#5
----------------------------------------------
-- Here we create the Temp Table #Result which 
-- store our sql statements
----------------------------------------------
DROP TABLE #Result

CREATE TABLE #Result (
	row_num INT NULL
	,h_sql VARCHAR(500) NULL
	);

------------------------------------------------------
-- Here we populate our Temp Table #Result one sql record at a time 
-- using dynamic sql 
------------------------------------------------------
WITH abc_cte
AS (
	SELECT row_number() OVER (
			ORDER BY (
					SELECT 1
					)
			) AS rownum
		,' select count([' + column_name + ']) as ColumnCount from ' + schema_name + '.' + table_name + ' where [' + column_name + '] ' + whereclause AS x
	FROM vColumnSchema
	)
INSERT INTO #Result (
	row_num
	,h_sql
	)
SELECT rownum
	,x
FROM abc_cte

----------------------------------------------------------------
-- Here we loop through the sql records in #Result table 
--    and execute one sql statement at a time 
-- Please note its still giving errors on some sql statements
--    which need to be rectified 
----------------------------------------------------------------
SELECT *
FROM #Result

-- Get the number of rows in the looping table
DECLARE @RowCount INT

SET @RowCount = (
		SELECT COUNT(*)
		FROM #Result
		)

-- Declare an iterator
DECLARE @I INT

-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
	-- Declare variables to hold the data which we get after looping each record 
	DECLARE @sql NVARCHAR(max) = ''

	SELECT @sql = h_sql
	FROM #Result
	WHERE row_num = @I

	EXEC (@sql)

	-- Increment the iterator
	SET @I = @I + 1
END

#6

Thank you for your reply you I added the row_num into the view and skipped the #table part
And yes I will investigate the precision of the rows to get them right

Thanks,
Ed Dror


#7

Kristen,

Thank you for your response, I think you miss the question it wasn't about performance tuning it was about Dynamic SQL
The view is not important, I could represent the final string in a table
The whole exercise is how to make dynamic SQL on a table and not just one string

Thanks,
Ed Dror


#8

I took the trouble to point out issues in your code which will cause problems for you, in general. You can choose to ignore them.

I also pointed out the things in your code that I found which look like they are preventing the Dynamic SQL it from working, so I did answer your question or at least attempt to.

With regard to performance I think I only made an observation that parametrised use of sp_ExecuteSQL is more efficient; far more important is reducing the risk of SQL Injection. I don't know if you will ever have data in your query that came from a user (data entry form), if not the data being used in your query may be totally under your control, but even then parametrising it avoids any possible risk of a single quote in the data breaking your Dynamic SQL syntax