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)
 Dynamic query providing wrong output
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockstar283
Yak Posting Veteran

90 Posts

Posted - 04/24/2013 :  10:53:11  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/24/2013 :  12:51:51  Show Profile  Reply with Quote
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
  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.34 seconds. Powered By: Snitz Forums 2000