Re: Querying a database

Dear Dmitriy Antonov,

I have run your suggestion and have the following queries, if I may:

A)
(I made one small adjustment to your first suggestion in order to exclude
the Id column from the query, as follows:

If @Cols <> 'Id'
Set @qry='Select Count(*) From SPF Where (' + @qry + ')>1'

--Print @qry
Execute(@qry)
)
I executed the query on a table of 30 columns (adjusted the count as
necessary) using columns labelled 1, 2, and 3 . It returned a value of 34,
however, the answer should have been 3. Three is the number of times that one
simultaneously appears in each of the aforementioned columns ie . I only
wish to count the number or times that '1' appears in the nominted columns
simultaneously, whether I am checking two columns at a time or three columns
( or even 4 columns....which is why I thought that the query should be built
in managed code(VB .net) using SQL CLR ?????).

B)When I tried to use a table with 38 columns , I received the following
error:

Msg 217, Level 16, State 1, Procedure spMyTest2, Line 45
Maximum stored procedure, function, trigger, or view nesting level exceeded
(limit 32).

even after ensuring that I adjusted the count correctly....

C) This procedure appears to only return one value based on inputting the
paramters as indicated. For a table that contains 38 columns, there should be
8436 values. Are you able to give me a clue as to how to return a list of all
the combinations, not just one?

Any assistance would be appreciated.
Thank you
Onecorp

"Dmitriy Antonov" wrote:


"onecorp" <onecorp@xxxxxxxxxxxxxxxx> wrote in message
news:1F1FA66F-4D56-4493-A857-4E6E7761CC56@xxxxxxxxxxxxxxxx
Thank you Dmitriy Antonov for your prompt reply:
Pardon my ignorance, but I am a beginner at this.
Is this what you meant?

Use My Database
GO

/* @Cols - comma separated list of column numbers to be checked (e.g.
'1,15,22')
can be any number of columns. Repetitive numbers - no problems. Numbers
beyond given range will be ignored*/
Alter Procedure spMyTest
@Cols VarChar(30)
As
Set Nocount On

--you

--Set @Cols='1,3,15'

Set
@Cols='[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]'+@Cols+','

Declare @cnt int
Declare @sCnt VarChar(2)
Declare @qry VarChar(500)
set @qry=''
Set @cnt=1

--you may adjust upper bound (max column's name) here if necessary
While @cnt<31 Begin
set @sCnt=Cast(@cnt As VarChar)
If CharIndex(','+@sCnt+',',@Cols)>0
Set @qry=@qry + '+IsNull([' + @sCnt +'],0)'
Set @cnt=@cnt+1
End

If @qry<>'' Begin
Set @qry=Substring(@qry,2,Len(@qry)-1)
End
--Else
--should return here - invalid argument
--End

Set @qry='Select
Count([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])
From MWFN Where (' + @qry + ')>1'

Print @qry
Execute(@qry)

Set Nocount Off
GO

?

Onecorp



No. It's not gonna work - wrong syntax. The code I submitted didn't require
any significant changes except for table name, which I didn't know - why
you've changed it so significantly?
Some comments, that I left are optional tips, which you can ignore. Couple
comments were left, because I used them for testing - you can remove them at
all.

One erroneous comment

--you

can be either replaced with

--you can check whether @Cols contains data and if not just return here

or removed at all.

Any other changes were not anticipated [by me] - and, what you did, doesn't
make any sense for me.

After you create sp you can call it from QA as following (you can use any
set of columns):

exec spMyTest '1,2,10'

This will check whether, at least, two columns among [1], [2] and [10] have
a value of 1. This is how I understand you task.

If you want to check ALL columns - whether at least two of them have a value
of 1, then you can simplify procedure (no params required):

Create Procedure spMyTest2 As
Set Nocount On

Declare @i int,@iMax int
Declare @qry VarChar(1000)

Set @iMax=(SELECT count(column_name) from Information_schema.columns where
table_name ='MWFN')
Set @iMax=@iMax-1 --ignore 1st column

set @qry=''
Set @i=1
While @i<@iMax Begin
Set @qry=@qry + '+IsNull(['+Cast(@i As VarChar)+'],0)'
Set @i=@i+1
End

Set @qry=Substring(@qry,2,Len(@qry)-1)

Set @qry='Select Count(*) From MWFN Where (' + @qry + ')>1'
Execute(@qry)

Set Nocount Off
GO


You just call it:
exec spMyTest2

If none of those two variants work for you then you should clarify your
specification. Again, here you can discuss SQL (still somewhat off-topic)
but you will not likely to recieve any help with .NET.

Dmitriy.




.

Relevant Pages

  • re: Querying a database
    ... Alter Procedure spMyTest ... Declare @sCnt VarChar ... Declare @qry VarChar ... Set Nocount Off ...
    (microsoft.public.vb.database)
  • Re: Querying a database
    ... Alter Procedure spMyTest ... Declare @sCnt VarChar ... Declare @qry VarChar ... Set Nocount Off ...
    (microsoft.public.vb.database)
Loading