April 24, 2013 at 8:52 am
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] [red]SET BLANK_COUNT='*'[/red] 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
April 24, 2013 at 9:45 am
Can you post your code that actually builds and runs these queries? Also, you may not need two dynamic queries, one to get the count and a second to do the update but it is a bit difficult to figure out, at least for me, from the code you have posted.
April 24, 2013 at 10:15 am
I solved it with friend's help..instead of CAST(@BLANK_COUNT AS VARCHAR(5)) it should be CAST(@BLANK_COUNT AS VARCHAR(10))
April 24, 2013 at 11:42 am
A different take on what you are doing, wouldn't hurt to give it a try:
/* Provide test data for building script */
declare @DB_NAME nvarchar(128),
@SCHEMA_NAME nvarchar(128),
@TABLE_NAME nvarchar(128),
@COLUMN_NAME nvarchar(128);
select
@DB_NAME = 'TEST',
@SCHEMA_NAME = 'dbo',
@TABLE_NAME = 'TBL_1',
@COLUMN_NAME = 'TCode';
/****************************************/
declare @SQLCmd nvarchar(max),
@SQLParm nvarchar(max);
set @SQLCmd = N'
WITH BlankCount as (
SELECT
BLANK_COUNT = COUNT(*)
FROM
[' + @DB_NAME + N'].[' + @SCHEMA_NAME + N'].[' + @TABLE_NAME + N'] tc
WHERE
tc.[' + @COLUMN_NAME + N'] = ''''
)
UPDATE [dbo].[Data_Profile_Stats] SET
BLANK_COUNT = (select BLANK_COUNT from BlankCount)
WHERE
[DB_NAME] = @DBNAME AND
SCHEMA_NAME = @SCHEMANAME AND
TABLE_NAME = @TABLENAME AND
COLUMN_NAME = @COLUMNNAME;'
set @SQLParm = N'@DBNAME nvarchar(128), @SCHEMANAME nvarchar(128), @TABLENAME nvarchar(128), @COLUMNNAME nvarchar(128)';
print @SQLCmd;
exec sp_executesql @SQLCmd, @SQLParm, @DBNAME = @DB_NAME, @SCHEMANAME = @SCHEMA_NAME, @TABLENAME = @TABLE_NAME, @COLUMNNAME = @COLUMN_NAME;
April 24, 2013 at 1:11 pm
Works great..Thanks mate:-)
April 24, 2013 at 1:16 pm
Glad to hear it. I think it makes the dynamic code easier to read. You don't have to count all those little single quotes as much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply