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.
| 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 OUTPUTSET @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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
Dynamic query providing wrong output