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)
 Dynamic query providing wrong output

Author  Topic 

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2013-04-24 : 10:53:11
I have a table data_profile_stats which contains all possible information about all the tables and columns in the DB. I have a cursor running over data_profile_stats which goes line by line, picks up a table name, column name and creates a dynamic query. This query is used to count number of blank values in that column. For some reason, instead of an INTEGER number, I am getting '*' as output which is wrong. Here is the query:

SET @QUERY='SELECT @BLANK_COUNT= COUNT(*) FROM ['+@DB_NAME+'].['+@SCHEMA_NAME+'].['+@TABLE_NAME + '] WHERE ['+@DB_NAME+'].['+@SCHEMA_NAME+'].['+@TABLE_NAME+'].['+@COLUMN_NAME + ']='+''''+''''

EXEC SP_EXECUTESQL @QUERY, N'@BLANK_COUNT INT OUTPUT', @BLANK_COUNT=@BLANK_COUNT OUTPUT

SET @QUERY='UPDATE [dbo].[Data_Profile_Stats] SET BLANK_COUNT='+''''+CAST(@BLANK_COUNT AS VARCHAR(5))+''''+' WHERE [DB_NAME]='+''''+@DB_NAME+''''+' AND SCHEMA_NAME='+''''+@SCHEMA_NAME+''''+' AND TABLE_NAME='+''''+@TABLE_NAME+''''+' AND COLUMN_NAME='+''''+@COLUMN_NAME+''''

exec sp_executesql @QUERY

Example of queries are:

SELECT @BLANK_COUNT= COUNT(*) FROM [TEST].[dbo].[TBL_1] WHERE [TEST].[dbo].[TBL_1].[TCode]=''

UPDATE [dbo].[Data_Profile_Stats] SET BLANK_COUNT='*' WHERE [DB_NAME]='TEST' AND SCHEMA_NAME='dbo' AND TABLE_NAME='TBL_1' AND COLUMN_NAME='TCode'

As you can see, Blank_Count is getting set to *..can someone please help me out here

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-24 : 12:51:51
that means the varchar field is too small to hold integer number. change it like below and see

...
SET @QUERY='UPDATE [dbo].[Data_Profile_Stats] SET BLANK_COUNT='+''''+CAST(@BLANK_COUNT AS VARCHAR(15))+''''+' WHERE [DB_NAME]='+''''+@DB_NAME+''''+' AND SCHEMA_NAME='+''''+@SCHEMA_NAME+''''+' AND TABLE_NAME='+''''+@TABLE_NAME+''''+' AND COLUMN_NAME='+''''+@COLUMN_NAME+''''
...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -