March 8, 2012 at 4:49 am
Misunderstoods ill happen.
Unfortunately humans dont speak in Context-Free Grammar (and that's why I prefer to work with computers :-P)
By I dont considered this option in my previous post.
And I'm no talking about signal interference (You must work with what you got, and complain with anyone give it to you)
Im talking about non trivial business logic.
If the client is working "the wrong way" you can make him to change (it happened to me only once and I'm luck the client listened).
But there are real and valid non trivial requirements.
(And there are trivial and non valid also)
Anyway is how to implement where lies the solutions in many cases (considering its a valid requirement).
So the questions are:
1. What you do when you get a bizarre job?
2. How do you validate it?
3. How do you do it (considering its valid)?
March 8, 2012 at 10:42 am
jcb (3/8/2012)
Anyway is how to implement where lies the solutions in many cases (considering its a valid requirement).So the questions are:
1. What you do when you get a bizarre job?
2. How do you validate it?
3. How do you do it (considering its valid)?
Those are all good questions, jcb. The first step to resolve them is to recognize that, even when we prefer to work with computers, the answers to #1 and #2 are going to be resolved by working with humans <s>. No way around it.
That said, I'll answer them.
1. What I do when I get a bizarre job?
I start by asking a lot of questions. If there is a PM or BA who's not doing their job I will feed them the correct questions to ask, and even (if they're really green) some useful strategies for asking them and getting real responses.
It helps if you've built up a lot of trust first, so that people know you're not prevaricating; you're comfortable that you can do whatever is needed if necessary, but you want to ensure that it *is* necessary. You're not interested in (a) wasting your time (b) wasting somebody else's money and/or (c) coming out with a solution that doesn't really solve the underlying business problem.
That's key; they have to know that, if I ever do say "no", it's not based on fear or other unworthy motives.
2. How do I validate it?
See above.
FWIW... I realize, jcb, that you are postulating a case where you want to take it as read that all this has already been dealt with and you're assured that the bizarre requirement is real and necessary. But I still have to say these things and personally hear some answers. Even if the bizarre requirement is real and necessary, I have to internalize it -- or (c) might still occur.
I think of the bizarre requirement as the presenting symptom, IOW. It would be unwise to treat it without receiving an appropriate diagnosis of the actual illness.
Maybe this is all already documented and available to me. In that case, I start by reviewing all the documents -- after which, if the PM or BA is any good, I have very *few* questions left! At that point, I sometimes apologize for my need to play devil's advocate, since they're all primed to go already and don't want to slow down. But they don't want (c) to happen and more than I do, and remember? they trust me.
Maybe it's not documented or available at all. Maybe nobody did their own homework. Then we have to start at the beginning. Not fun for anybody but I can usually document very quickly the risks of doing otherwise (see (a), (b), and (c) above).
If this is not an acceptable method of moving forward to anybody, I really will walk away, because chances are there is something really wrong at the basis of this requirement that nobody is willing to explore and/or defend.
3. How do I do it (considering its valid)?
I operate on the principle of GSD (get sh*t done -- see http://spacefold.com/lisa/search.aspx?q=gsd). That means:
* -- I'm going to work my butt off to fulfill the requirement to both its technical letter *and* the business intention.
* -- I'm going to call in any resources and technologies required to do it. If it's truly bizarre and they've recognized it as something they can't live without, they should also recognize the cost.
* -- From an implementation standpoint, I'm not particularly interested in elegance if an elegant solution can't be found for this "bizarre" requirement (although usually no matter how bizarre something is, it only presents that way and if I can't find elegance it's my limitation -- somewhere out there is a better way -- see next point)
* -- From an architectural standpoint, I'm still going to drive towards elegance wherever possible. That means: I'll design in such a way that the requirement and its fulfillment are as encapsulated as possible so the implementation can be switched out when the requirement goes away, obviated by seemingly unrelated business changes, or a more elegant method of resolving it becomes available.
* -- I will document the cr*p out of what I've done and why.
Hope this answers your question(s).
>L<
March 9, 2012 at 11:23 am
Lisa Slater Nicholls (3/8/2012)
...Hope this answers your question(s).
>L<
I started not seeking answers more than new questions (I'm afraid we hjjacked this topic) but these are good fair points and nicely writen.
May 6, 2012 at 11:42 am
Hi,
I know you did say you don’t want an answer with dynamic statement for fear of SQL injection. As I can’t think of any way to select the empty columns without using them I therefore give you an answer using quite a few dynamic statements. Get the job done first and then we shall later learn some ways to overcome the SQL injection.
In the first part I recreate your given table in the TestDB with your sample values inserted.
In the second part I create a simple stored procedure with the table name as the input parameter. The rest are handled dynamically giving the results you have wanted.
Perhaps others who are more experienced can point us to ways to overcome SQL injection using parameterized dynamic statement or other methods.
Please note that the input parameter is just the table name 'gt'. If we put in 'dbo.gt' then it won't work. It is because I made use of Information_Schema.Columns which can only take the plain table name as input.
Thanks and regards.
Part 1: Re-creating the table with sample data.
USE [TestDB]
GO
Drop Table [dbo].[gt]
go
CREATE TABLE [dbo].[gt](
[PkC1] [int] IDENTITY(1,1) Primary Key NOT NULL,
[C2] [varchar](100) NULL,
[C3] [varchar](100) NULL,
[C4] [varchar](100) NULL,
[C5] [varchar](100) NULL,
[C6] [varchar](100) NULL
)
Go
Insert into dbo.gt (C2,C3,C4,C5,C6)
Values ('a',null,'b',null,'f')
, ('d',null,'e',null,null)
, ('g',null,'h',null,'k')
Go
/*
Select * from gt;
PkC1C2C3C4C5C6
1aNULLbNULLf
2dNULLeNULLNULL
3gNULLhNULLk
*/
Part 2: Solution using 2 temporary tables #t1, #t2, and dynamic SQL Statements.
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[SelectNonEmptyColumns] Script Date: 5/7/2012 12:56:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* TESTING
Declare @tbl varchar(100)
Set @tbl= 'gt'
EXEC SelectNonEmptyColumns @tbl
*/
Create procedure [dbo].[SelectNonEmptyColumns]
(
@tbl varchar(100)
)
AS
SET NOCOUNT ON
Declare @sCol varchar(2000), @CT int, @sql nvarchar(max), @mycol varchar(100)
Create Table #t1
(rNo int identity(1,1)
,ColName varchar(100)
,qtyNull int
)
Create Table #t2
(rNo int identity(1,1)
,ColName varchar(100)
)
Set @sql = N'
Insert into #t1 (ColName)
(
Select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME= ''' + @tbl + '''
)
'
Exec (@sql)
Set @CT=0
While @CT<(select COUNT(*) from #t1)
Begin
Set @CT=@ct+1
Set @mycol=(Select t.ColName from #t1 t where t.rNo=@ct)
Set @sql=N'
Declare @myqty int
Set @myqty=
(Select sum(case when '+ @MYCOL +' is null then 1 else 0 end) from ' + @tbl + ' )
Update t
set qtyNull=@myqty
from #t1 t
where t.rNo= ' + convert(varchar(5),@ct) + '
'
exec(@sql)
End
Set @sql = N'
insert into #t2 (ColName)
(SELECT t.ColName
from #t1 t
where qtyNull<(select COUNT(*) from ' + @tbl + ' )
)
'
Exec (@sql)
If (select COUNT(*) from #t2) >0
Begin
Set @CT=0
Set @sCol=''
While @CT<(Select COUNT(*) from #t2)
Begin
Set @CT=@ct+1
If @CT=1
Set @sCol=@sCol + (Select t.ColName from #t2 t where t.rNo=@ct)
Else
Set @sCol=@sCol+','+(Select t.ColName from #t2 t where t.rNo=@ct)
End
Set @sql = N'
Select '+ @sCol + ' from ' + @tbl + ' '
Exec(@sql)
End
Drop Table #t1
DROP TABLE #t2
/* Results */
Exec [dbo].[SelectNonEmptyColumns] 'gt'
PkC1C2C4C6
1abf
2deNULL
3ghk
May 6, 2012 at 10:04 pm
I have done some minor alterations to the stored procedure:
1.Instead of updating table #t1 and then insert into #t2, the new one directly inserts the chosen columns into #t2.
2.It made use of Count(ColName) instead of SUM(Col is null).
Thanks and regards.
Altered stored procedure attached below:
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[SelectNonEmptyColumns] Script Date: 5/7/2012 11:56:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* TESTING
Declare @tbl varchar(100)
Set @tbl= 'gt'
EXEC SelectNonEmptyColumns @tbl
*/
ALTER procedure [dbo].[SelectNonEmptyColumns]
(
@tbl varchar(100)
)
AS
SET NOCOUNT ON
/* step 0 prepare */
Declare @sCol varchar(2000), @CT int, @sql nvarchar(max), @mycol varchar(100)
Create Table #t1
(rNo int identity(1,1)
,ColName varchar(100)
)
Create Table #t2
(rNo int identity(1,1)
,ColName varchar(100)
)
/* step 1 get the columns from the given table */
Set @sql = N'
Insert into #t1 (ColName)
(
Select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME= ''' + @tbl + '''
)
'
Exec (@sql)
/* step 2 get all columns having some data entries */
Set @CT=0
While @CT<(select COUNT(*) from #t1)
Begin
Set @CT+=1
Set @mycol=(Select t.ColName from #t1 t where t.rNo=@ct)
Set @sql=N'
if (Select COUNT( '+ @mycol +') from ' + @tbl + ')>0
Begin
insert into #t2 (ColName)
SELECT '''+ @mycol +'''
End
'
exec(@sql)
End
/* step 3 prepare the string for chosen columns and the select statement */
If (select COUNT(*) from #t2) >0
Begin
Set @CT=0
Set @sCol=''
/* step 3.1 prepare the string for chosen columns */
While @CT<(Select COUNT(*) from #t2)
Begin
Set @CT+=1
If @CT=1
Set @sCol=@sCol + (Select t.ColName from #t2 t where t.rNo=@ct)
Else
Set @sCol=@sCol+','+(Select t.ColName from #t2 t where t.rNo=@ct)
End
/* step 3.2 prepare the select statement for chosen columns and execute*/
Set @sql = N'
Select '+ @sCol + ' from ' + @tbl + ' '
Exec(@sql)
End
Else
Select 'No data available'
Drop Table #t1
DROP TABLE #t2
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply