Recommended Implementation

Question

Recommended Implementation

NikosV

SSCrazy

Points: 2111
More actions
July 22, 2018 at 3:58 am

#354930

I have two tables. Table A and Table B.
Table A is the main table with all my main data and Table B contains three columns.
Table B Columns
CategoryId, FieldName, IsActive
I basically want to say this but I’m not sure how.
If B.CategoryId = 1
Make B.IsActive True
Where B.FieldName = ‘PolicyNumber’
Then Select XYZ from A.TableA
Into TempTable
Then I’ll do what i want with the temp table.
(CategoryId is selected by a user so it’ll be a parameter).
Same goes for Category 2.
If B.CategoryId = 2
Make B.IsActive True
Where B.FieldName = ‘Branch’
Then Select ABC from A.TableA
Into TempTable
Selected columns are not standard, based on the Category number selected The columns change. All Columns are in Table A. I just want to view particular columns from Table A based on the CategoryId selected from Table B.
Does that make sense?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5296 More actions · Answer 1

What relates the data in TableA to TableB? What do you expect to happen to the value of IsActive on rows in TableB other than the one you're wanting to update? What is the purpose of FieldName and can there be more than one value per categoryID?

As a design it seems a bit odd and I suspect if it was clearer what you were really trying to achieve it would be a lot easier to suggest a better solution.

You could probably literally write what you're suggesting as:

If @CategoryID = 1
Begin
Update TableB
Set IsActive = 1
Where FieldName = 'PolicyNumber'

Select ABC From TableA
End

But that seems like a really clunky way to code anything in SQL

NikosV SSCrazy Points: 2111 More actions · Answer 2

Hi. I was having some difficulties with this site so i posted on stack overflow. Am i allowed fo give you the link?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

NikosV - Sunday, July 22, 2018 7:01 AM
Hi. I was having some difficulties with this site so i posted on stack overflow. Am i allowed fo give you the link?

Sure... anything that will help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NikosV SSCrazy Points: 2111 More actions · Answer 4

Here you go.

https://stackoverflow.com/questions/51464758/retrieve-show-columns-based-on-user-input

Had to go to Stack Overflow. Posting on here via mobile was really difficult.

Thanks

andycadley SSCertifiable Points: 5296 More actions · Answer 5

Once you've set IsActive, it's just a spot of dynamic SQL such as:


Declare @sql VarChar(Max) =(
Select 'Select ' + String_Agg(FieldName,',') + ' From #Main' From #Activation Where IsActive = 1
)

Exec(@SQL);

Note I've used String_Agg for brevity, but your favourite string aggregator technique will do on SQL2016 or lower.

I'd still question the design though. What happens if two users try to use the system at the same time?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

NikosV - Sunday, July 22, 2018 7:36 AM
Here you go. https://stackoverflow.com/questions/51464758/retrieve-show-columns-based-on-user-inputHad to go to Stack Overflow. Posting on here via mobile was really difficult. Thanks

Having looked at that post (great post, BTW... too bad they don't make it easier to do such things from this forum), let me see if I can simplify your request without any assumptions on how to do it.

If a customer selects a particular "Category", you want to use the IsActive column of the "FieldActivationTable" to control what is visible to them in the "MainTable". Is that correct?

The confusing part of this is what you said in the last paragraph of your post...

I can't help but think there's a better approach to this, but this approach has been given to me by somebody else to implement, I can't go against this. Personally, I would have preferred that when the user selects one, select columnA and columnB, when 2, select columnC and columnD, without the IsActive column. But hey instructions are instructions. There's obviously a reason for this.

That makes is sound like they don't actually care about the IsActive column being used to control what can be seen in the MainTable. Could you clarify a bit? Or is the real purpose of this whole thing is to SET the IsActive column based on user choices?

Like I said, I'm a bit confused although I've admittedly only done a quick read of that other post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NikosV SSCrazy Points: 2111 More actions · Answer 7

You’re right. I haven’t got a clue. The design is crap I can tell even as a junior dev thats something isnt right.

Thanks. I’ll try it out see what happens. About the multiple users? No clue. Good question.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

I also missed Andy's post above... does his method solve the problem for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NikosV SSCrazy Points: 2111 More actions · Answer 9

Not sure I’ll try it in a week when I get back into the office so for now I’m turning notifications off

And I’ll get back.

The IsActive column is something they want, I just don’t think it’s needed.

Thanks for all your help guys I’ve saved this link (cant find a way to see from my account what I’ve posted so I saved the link)

And I’ll let everyone know what happened.

Thanks again guys.