September 21, 2018 at 10:55 am
Hi all,
I am attempting to create a stored procedure that has the following requirements:
1. The ability to pass it a table name
2. Run the below on each column in the table:2. SELECT CASE WHEN COUNT(DISTINCT ColumName)
3. The end result should be a single row, with each column populated with 'NULL', 'Single Value' (plus the value)', or 'Multiple Values' based on the results
This sounds easy enough, but I do not have much experience with Dynamic SQL, and I am having a hard time wrapping my head around the logic.
Essentially, I want to take the below code block, which applies this principle to a single table and column, and create a stored procedure that takes a table as a parameter and performs the same operation on all columns in the table:
SELECT
CASE
WHEN COUNT(DISTINCT [Column]) IS NULL
THEN 'NULL'
WHEN COUNT(DISTINCT [Column]) = '1'
THEN 'Single Value: ' + (SELECT DISTINCT [Column] FROM [Table])
WHEN COUNT(DISTINCT [Column]) > '1'
THEN 'Multiple Values'
END AS [Column]
FROM [Table]
I started the skeleton of a stored procedure, but I am missing something and can't fill in the blanks. Or it may just be completely wrong. Here is what I have so far:
CREATE PROCEDURE dbo.Proc @table
AS
BEGIN
DECLARE @sqlCommand varchar(max) = 'SELECT COUNT(DISTINCT ' + COLUMN_NAME +
' FROM ' + @table + ' WHERE '
Could anyone provide some insight on this, or let me know if there is a better way? Any assistance would be appreciated.
September 21, 2018 at 12:16 pm
You mean something like this?
DECLARE @SQLCommandTemplate NVARCHAR(MAX)
,@SQLCommand NVARCHAR(MAX)
,@inTable sysname
,@ObjectId INT
,@Table sysname
,@Schema sysname
,@Column sysname;
SET @SQLCommandTemplate = N'
SELECT
CASE
WHEN COUNT(DISTINCT [Column]) IS NULL
THEN ''NULL''
WHEN COUNT(DISTINCT [Column]) = 1
THEN ''Single Value: '' + (SELECT DISTINCT CAST([Column] AS VARCHAR(30)) FROM [Table])
ELSE ''Multiple Values''
END AS [ColumnInfo]
FROM [Table]
';
SET @inTable = 'HTally';
-- Validate that the table actually exists
-- NOTE: This will fail if the name exists in multiple schemas
SELECT
@Schema = OBJECT_SCHEMA_NAME([tab].[object_id])
, @ObjectId = [tab].[object_id]
, @Table = [tab].[name]
FROM
[sys].[tables] AS [tab]
WHERE
[tab].[name] = @inTable; -- Validate that the table actually exists
DECLARE ColumnCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
[col].[name]
FROM
[sys].[columns] AS [col]
WHERE
[col].[object_id] = @ObjectId
ORDER BY
[col].[column_id];
OPEN [ColumnCur];
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [ColumnCur]
INTO @Column;
IF @@FETCH_STATUS <> 0
BREAK;
SET @SQLCommand = REPLACE(REPLACE(@SQLCommandTemplate,'[Table]', QUOTENAME(@Schema) + '.' + QUOTENAME(@Table)),'[Column]', QUOTENAME(@Column));
PRINT @SQLCommand;
EXEC [sys].[sp_executesql] @stmt = @SQLCommand;
END
CLOSE [ColumnCur];
DEALLOCATE [ColumnCur];
This will need some changes to put it in a stored procedure.
September 21, 2018 at 12:26 pm
I'm not sure why you would need these, but you can try this. Also, I out in a case for null, but not sure that will ever be used
declare @table varchar(40) = 'MyTable',
@schema varchar(20) = 'MySchema'
DECLARE @sqlCommand varchar(max) = '',
@sqlcount varchar(max) = ''
select @sqlCommand = @sqlCommand + 'COUNT(DISTINCT ' + COLUMN_NAME + ') ' + Column_name + ','
, @sqlcount = @SQLCount + ' case ' + Column_name + ' when null then ''null'' when 1 then ''Single Value: '' + cast(' + Column_name + ' as varchar(10)) else ''Multiple Value: '' + cast(' + Column_name + ' as varchar(10)) end as ' + Column_Name + ','
from INFORMATION_SCHEMA.Columns
where table_name = @Table
and table_schema = @schema
select @sqlCommand = Left(@sqlCommand, len(@SQLCommand) - 1),
@SQLCount = Left(@SQLCount, len(@SQLCount) - 1)
-- select 'Select ' + @SQLCount + ' from (Select ' + @SQLCommand + ' from ' + @Schema + '.' + @Table + ') v'
exec ('Select ' + @SQLCount + ' from (Select ' + @SQLCommand + ' from ' + @Schema + '.' + @Table + ') v')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply