March 5, 2012 at 8:03 am
I'm on my way to work and will have a go at it after I get home tonight. Chances are, someone will beat me to it, though. Lot's of good folks on this forum.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2012 at 8:20 am
Using this sample data: -
--Sample table
IF OBJECT_ID('tempdb.dbo.SampleTable', 'U') IS NOT NULL
DROP TABLE tempdb.dbo.SampleTable;
--Sample data
CREATE TABLE dbo.SampleTable (PK INT PRIMARY KEY CLUSTERED, C1 VARCHAR(10), C2 VARCHAR(10), C3 VARCHAR(10), C4 VARCHAR(10), C5 VARCHAR(10));
INSERT INTO dbo.SampleTable (PK, C1, C2, C3, C4, C5)
SELECT PK, C1, C2, C3, C4, C5
FROM (VALUES(1, 'a', NULL, 'b', NULL, 'f'),(2, 'd', NULL, 'e', NULL, NULL),(3, 'g', NULL, 'h', NULL, 'h'))a(PK, C1, C2, C3, C4, C5);
Here is how I'd do it dynamically: -
--Actual solution
DECLARE @sql AS NVARCHAR(MAX);
SELECT @sql = 'SELECT ' +
STUFF(CASE WHEN PK > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('PK') ELSE '' END +
CASE WHEN C1 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C1') ELSE '' END +
CASE WHEN C2 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C2') ELSE '' END +
CASE WHEN C3 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C3') ELSE '' END +
CASE WHEN C4 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C4') ELSE '' END +
CASE WHEN C5 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C5') ELSE '' END,1,3,'') + CHAR(13) + CHAR(10) +
'FROM dbo.SampleTable'
FROM (SELECT COUNT(PK), COUNT(C1), COUNT(C2), COUNT(C3), COUNT(C4), COUNT(C5)
FROM dbo.SampleTable) a(PK, C1, C2, C3, C4, C5);
EXECUTE sp_executesql @sql;
Produces:
PK C1 C3 C5
----------- ---------- ---------- ----------
1 a b f
2 d e NULL
3 g h h
March 5, 2012 at 6:10 pm
Jeff Moden (3/5/2012)
I'm on my way to work and will have a go at it after I get home tonight. Chances are, someone will beat me to it, though. Lot's of good folks on this forum.
Heh... I was pretty sure that it was going to be Cadavre to beat me to this one. Well done. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2012 at 1:39 am
Jeff Moden (3/5/2012)
Heh... I was pretty sure that it was going to be Cadavre to beat me to this one. Well done. 🙂
😀
How could I have taken this: -
I'm on my way to work and will have a go at it after I get home tonight. Chances are, someone will beat me to it, though. Lot's of good folks on this forum.
As anything but a challenge to get it done? 😉
March 6, 2012 at 4:18 am
Are you trying some sort of
select ISNULL(c1,'')+ISNULL(c2,'')+...
?
March 6, 2012 at 4:24 am
assefam (3/4/2012)
For security reason( SQL Injection),Dynamic SQL is not allowed for me...
If you use sp_executesql with parameters, there are no risks of SQL Injection and dynamic SQL is safe to use.
March 6, 2012 at 4:27 am
jcb (3/6/2012)
Are you trying some sort ofselect ISNULL(c1,'')+ISNULL(c2,'')+...
?
Nope, the OP wanted to be able to only select the column if the data is not null and originally wanted the solution to not use dynamic SQL.
So, using this sample data: -
--Sample table
IF OBJECT_ID('tempdb.dbo.SampleTable', 'U') IS NOT NULL
DROP TABLE tempdb.dbo.SampleTable;
--Sample data
CREATE TABLE dbo.SampleTable (
PK INT PRIMARY KEY CLUSTERED,
C1 VARCHAR(10),
C2 VARCHAR(10),
C3 VARCHAR(10),
C4 VARCHAR(10),
C5 VARCHAR(10));
INSERT INTO dbo.SampleTable (PK, C1, C2, C3, C4, C5)
SELECT PK, C1, C2, C3, C4, C5
FROM (VALUES (1, 'a', NULL, 'b', NULL, 'f'),
(2, 'd', NULL, 'e', NULL, NULL),
(3, 'g', NULL, 'h', NULL, 'h')) a(PK, C1, C2, C3, C4, C5);
The OP needs a solution that would only select PK, C1, C3 and C5.
Jeff wrote a solution using a temporary table then conditionally dropping columns based on whether or not there was any data.
--===== Conditionally drop the working table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
--===== Copy the data from the original table to a temp table.
SELECT *
INTO #MyHead
FROM dbo.SampleTable
;
--===== Declare variables to hold counts of columns
DECLARE @Total INT,
@C1Count INT,
@C2Count INT,
@C3Count INT,
@C4Count INT,
@C5Count INT
;
--===== Get the non-null counts for each column and the table
SELECT @Total = COUNT(*),
@C1Count = COUNT(C1),
@C2Count = COUNT(C2),
@C3Count = COUNT(C3),
@C4Count = COUNT(C4),
@C5Count = COUNT(C5)
FROM #MyHead
;
--===== Dispose of any columns where the count is 0
IF @C1Count = 0 ALTER TABLE #MyHead DROP COLUMN C1;
IF @C2Count = 0 ALTER TABLE #MyHead DROP COLUMN C2;
IF @C3Count = 0 ALTER TABLE #MyHead DROP COLUMN C3;
IF @C4Count = 0 ALTER TABLE #MyHead DROP COLUMN C4;
IF @C5Count = 0 ALTER TABLE #MyHead DROP COLUMN C5;
--===== Display the correct result
SELECT *
FROM #MyHead
;
Then the OP requested a dynamic solution, so I added this:
DECLARE @sql AS NVARCHAR(MAX);
SELECT @sql = 'SELECT ' +
STUFF(CASE WHEN PK > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('PK') ELSE '' END +
CASE WHEN C1 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C1') ELSE '' END +
CASE WHEN C2 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C2') ELSE '' END +
CASE WHEN C3 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C3') ELSE '' END +
CASE WHEN C4 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C4') ELSE '' END +
CASE WHEN C5 > 0 THEN ', ' + CHAR(13) + CHAR(10) + QUOTENAME('C5') ELSE '' END,1,3,'') + CHAR(13) + CHAR(10) +
'FROM dbo.SampleTable'
FROM (SELECT COUNT(PK), COUNT(C1), COUNT(C2), COUNT(C3), COUNT(C4), COUNT(C5)
FROM dbo.SampleTable) a(PK, C1, C2, C3, C4, C5);
EXECUTE sp_executesql @sql;
Both pieces of code return the required result-set of:
PK C1 C3 C5
----------- ---------- ---------- ----------
1 a b f
2 d e NULL
3 g h h
If you have another way of performing the same thing, I'd be interested in seeing it. Always good to learn 🙂
March 6, 2012 at 7:40 am
Well, I wrote a more generic solution to any user table.
create table TESTE (PK int, C1 char(1), C2 char(1), C3 char(1), C4 char(1), C5 char(1))
GO
insert into TESTE values
(1, 'a', NULL, 'b', NULL, 'f'),
(2, 'd', NULL, 'e', NULL, NULL),
(3, 'g', NULL, 'h', NULL, 'h')
GO
create procedure IsNullColumn(@tableName nvarchar(128),@columnName nvarchar(128), @count int OUTPUT)
as
begin
declare @statment nvarchar(1000), @params nvarchar(1000)
set @params = N'@count int OUTPUT'
set @statment = N'select @count = count(*) from '+@tableName+' where '+@columnName+' is not null'
exec sp_executesql @statment, @params, @count OUTPUT
end
GO
create procedure IsNullColumnTable(@tableName nvarchar(128))
as
begin
declare @statment nvarchar(1000), @params nvarchar(1000)
set @params = N'@tableName nvarchar(128)'
set @statment = N'select c.name from sys.syscolumns c join sys.sysobjects o on o.id = c.id where o.xtype = N''U'' and o.name = @tableName'
exec sp_executesql @statment, @params, @tableName
end
GO
create procedure ReturnNotNullTable(@tableName nvarchar(128))
as
begin
set nocount on
declare @ColumnsNames table(ColumnName nvarchar(128))
declare @columnName nvarchar(128)
insert into @ColumnsNames
exec IsNullColumnTable @tableName
declare @count int, @query nvarchar(2000)
set @query = ''
DECLARE ColumnsCursor cursor LOCAL FAST_FORWARD READ_ONLY for
select ColumnName from @ColumnsNames
OPEN ColumnsCursor
FETCH next from ColumnsCursor into @columnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec IsNullColumn @tableName, @columnName, @count OUTPUT
if (@count > 0)
begin
if (LEN(@query) > 0)
set @query = @query + ', '
set @query = @query + @columnName
end
FETCH next from ColumnsCursor into @columnName
END
close ColumnsCursor
deallocate ColumnsCursor
set @query = 'select ' + @query + ' from ' + @tableName
--select @query
set nocount off
EXEC sp_executesql @query
end
GO
exec ReturnNotNullTable N'TESTE'
GO
Just forgot to put a if (LEN(@query) > 0) to the case the table is empty
😉
March 6, 2012 at 7:50 am
I have a gut feeling that this requirement comes from not very well thought out design.
I cannot see why would you want to do what OP has asked for. Even so that is possible to achieve, this solution would complicate all things which consumes such "a dynamic" resultset...
It's doesn't look like done in SQL style. I would really love to see what kind of data OP would have in all of his C-columns. Is it of the same type?
March 6, 2012 at 8:01 am
Eugene Elutin (3/6/2012)
I have a gut feeling that this requirement comes from not very well thought out design.
Me too...
I cannot see why would you want to do what OP has asked for.
Why its funny 😀
Even so that is possible to achieve, this solution would complicate all things which consumes such "a dynamic" resultset...
Its OP call, he asked for it
It's doesn't look like done in SQL style. I would really love to see what kind of data OP would have in all of his C-columns. Is it of the same type?
My solution works for any (no empty) user table. :hehe:
March 6, 2012 at 8:24 am
jcb (3/6/2012)
Eugene Elutin (3/6/2012)
I have a gut feeling that this requirement comes from not very well thought out design.Me too...
I cannot see why would you want to do what OP has asked for.
Why its funny 😀
Even so that is possible to achieve, this solution would complicate all things which consumes such "a dynamic" resultset...
Its OP call, he asked for it
It's doesn't look like done in SQL style. I would really love to see what kind of data OP would have in all of his C-columns. Is it of the same type?
My solution works for any (no empty) user table. :hehe:
Your generic one is cool! Pack it, wrap it and sell it 😀
But, you will need to come up with some credible ideas how to make anyone to buy it...
:hehe:
Have one for you:
To Managers:
Want to get more budget for your project? Would like to employ more subordinates?
Want to be able easely convince your sponsors to approve the above?
Get the Dynamic Resulset Creator and you will see how the project will start to consume more resources at no time!
:w00t:
And another one:
Super flixibility! Get everything if anything or something if nothing...
:hehe:
March 6, 2012 at 8:39 am
Eugene Elutin (3/6/2012)
...Your generic one is cool! Pack it, wrap it and sell it 😀
I'll no sell it, I just like to code and make it for funny. You can copy and paste it if you like it. 😛
...
Get the Dynamic Resulset Creator and you will see how the project will start to consume more resources at no time!
...
No thanks, I prefer to rely in analysis and planning to save resources.
But we dont know the context, maybe the OP have a valid reason to do it this way, maybe not.
He's not paying enough to make we to care about it 😎
March 6, 2012 at 8:41 am
jcb (3/6/2012)
Well, I wrote a more generic solution to any user table.
create table TESTE (PK int, C1 char(1), C2 char(1), C3 char(1), C4 char(1), C5 char(1))
GO
insert into TESTE values
(1, 'a', NULL, 'b', NULL, 'f'),
(2, 'd', NULL, 'e', NULL, NULL),
(3, 'g', NULL, 'h', NULL, 'h')
GO
create procedure IsNullColumn(@tableName nvarchar(128),@columnName nvarchar(128), @count int OUTPUT)
as
begin
declare @statment nvarchar(1000), @params nvarchar(1000)
set @params = N'@count int OUTPUT'
set @statment = N'select @count = count(*) from '+@tableName+' where '+@columnName+' is not null'
exec sp_executesql @statment, @params, @count OUTPUT
end
GO
create procedure IsNullColumnTable(@tableName nvarchar(128))
as
begin
declare @statment nvarchar(1000), @params nvarchar(1000)
set @params = N'@tableName nvarchar(128)'
set @statment = N'select c.name from sys.syscolumns c join sys.sysobjects o on o.id = c.id where o.xtype = N''U'' and o.name = @tableName'
exec sp_executesql @statment, @params, @tableName
end
GO
create procedure ReturnNotNullTable(@tableName nvarchar(128))
as
begin
set nocount on
declare @ColumnsNames table(ColumnName nvarchar(128))
declare @columnName nvarchar(128)
insert into @ColumnsNames
exec IsNullColumnTable @tableName
declare @count int, @query nvarchar(2000)
set @query = ''
DECLARE ColumnsCursor cursor LOCAL FAST_FORWARD READ_ONLY for
select ColumnName from @ColumnsNames
OPEN ColumnsCursor
FETCH next from ColumnsCursor into @columnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec IsNullColumn @tableName, @columnName, @count OUTPUT
if (@count > 0)
begin
if (LEN(@query) > 0)
set @query = @query + ', '
set @query = @query + @columnName
end
FETCH next from ColumnsCursor into @columnName
END
close ColumnsCursor
deallocate ColumnsCursor
set @query = 'select ' + @query + ' from ' + @tableName
--select @query
set nocount off
EXEC sp_executesql @query
end
GO
exec ReturnNotNullTable N'TESTE'
GO
Just forgot to put a if (LEN(@query) > 0) to the case the table is empty
😉
Cool.
Try this non-cursor one instead and see if you get any joy 😀
DECLARE @TableName AS NVARCHAR(20) = 'work', @sql AS NVARCHAR(MAX);
WITH p1 AS (
SELECT sqlStmt, ROW_NUMBER() OVER(ORDER BY ord, (SELECT NULL)) AS rn, col, colname
FROM (SELECT 'SELECT COUNT(*) AS [all]' AS sqlStmt, 1 AS ord, '[all]' AS col, NULL AS colname
UNION ALL
SELECT sqlStmt + ' AS ' + QUOTENAME(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))), 2, QUOTENAME(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))),
colname
FROM (SELECT ', ' + CHAR(13) + CHAR(10) + 'COUNT('+name+')', name
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName) a(sqlStmt,colname)
UNION ALL
SELECT CHAR(13) + CHAR(10) + 'FROM ' + QUOTENAME(@TableName), 3, NULL, NULL) a),
p2(sqlStmt) AS (
SELECT 'DECLARE @sql AS NVARCHAR(MAX);' + CHAR(13) + CHAR(10)
UNION ALL
SELECT 'SELECT @sql = ' + CHAR(39) + 'SELECT '+ CHAR(39) +' +'+'STUFF('
UNION ALL
SELECT 'CASE WHEN '+col+' > 0 THEN '+CHAR(39)+','+CHAR(39)+' + CHAR(13) + CHAR(10) + '+CHAR(39)+colname+CHAR(39)+' ELSE '+CHAR(39)+CHAR(39)+' END +'
FROM p1
WHERE col IS NOT NULL AND col <> '[all]'
UNION ALL
SELECT CHAR(39)+CHAR(39)+',1,3,'+CHAR(39)+CHAR(39)+') + CHAR(13) + CHAR(10) + ' + CHAR(39) + 'FROM ' +
QUOTENAME(@TableName) + CHAR(39) + CHAR(13) + CHAR(10) + 'FROM ('
UNION ALL
SELECT sqlStmt
FROM p1
UNION ALL
SELECT ')a;'
UNION ALL
SELECT CHAR(13)+CHAR(10) + 'EXECUTE sp_executesql @sql;')
SELECT @sql = COALESCE(@SQL,'') + sqlStmt
FROM p2;
EXECUTE sp_executesql @sql;
March 6, 2012 at 8:53 am
Cadavre (3/6/2012)
Cool.Try this non-cursor one instead and see if you get any joy 😀
Pretty good! :w00t:
non-cursor was my initial goal but I give up just because its not possible to put a EXEC inside a function (I hate this kind of T-SQL limitation).
But your solutions is better thanks!:hehe:
March 6, 2012 at 4:26 pm
jcb (3/6/2012)
Well, I wrote a more generic solution to any user table.
create table TESTE (PK int, C1 char(1), C2 char(1), C3 char(1), C4 char(1), C5 char(1))
GO
insert into TESTE values
(1, 'a', NULL, 'b', NULL, 'f'),
(2, 'd', NULL, 'e', NULL, NULL),
(3, 'g', NULL, 'h', NULL, 'h')
GO
create procedure IsNullColumn(@tableName nvarchar(128),@columnName nvarchar(128), @count int OUTPUT)
as
begin
declare @statment nvarchar(1000), @params nvarchar(1000)
set @params = N'@count int OUTPUT'
set @statment = N'select @count = count(*) from '+@tableName+' where '+@columnName+' is not null'
exec sp_executesql @statment, @params, @count OUTPUT
end
GO
create procedure IsNullColumnTable(@tableName nvarchar(128))
as
begin
declare @statment nvarchar(1000), @params nvarchar(1000)
set @params = N'@tableName nvarchar(128)'
set @statment = N'select c.name from sys.syscolumns c join sys.sysobjects o on o.id = c.id where o.xtype = N''U'' and o.name = @tableName'
exec sp_executesql @statment, @params, @tableName
end
GO
create procedure ReturnNotNullTable(@tableName nvarchar(128))
as
begin
set nocount on
declare @ColumnsNames table(ColumnName nvarchar(128))
declare @columnName nvarchar(128)
insert into @ColumnsNames
exec IsNullColumnTable @tableName
declare @count int, @query nvarchar(2000)
set @query = ''
DECLARE ColumnsCursor cursor LOCAL FAST_FORWARD READ_ONLY for
select ColumnName from @ColumnsNames
OPEN ColumnsCursor
FETCH next from ColumnsCursor into @columnName
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec IsNullColumn @tableName, @columnName, @count OUTPUT
if (@count > 0)
begin
if (LEN(@query) > 0)
set @query = @query + ', '
set @query = @query + @columnName
end
FETCH next from ColumnsCursor into @columnName
END
close ColumnsCursor
deallocate ColumnsCursor
set @query = 'select ' + @query + ' from ' + @tableName
--select @query
set nocount off
EXEC sp_executesql @query
end
GO
exec ReturnNotNullTable N'TESTE'
GO
Just forgot to put a if (LEN(@query) > 0) to the case the table is empty
😉
That's precisely what I was going to tell Eugene... the use of sp_ExecuteSQL doesn't necessarily mean that it prevents SQL Injection when someone uses it incorrectly. If you concatenate table or column names from user input, even if you use sp_ExecuteSQL, you're opening yourself up for SQL Injection.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply