550+ columns in a table and limited input parameters to stored proc

Transact-SQL
1

UFS1.PNG1201×32 3.68 KB
UFS2.PNG1299×19 3.25 KB
UFS3.PNG1307×21 3.17 KB

Dear team,

The above attached are the few columns snap shot for your reference

I have a table with 550+ columns (volume up, volume down, mute,channel up, channel down, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10+, blue, white, yellow, red, green, brightness, brightness down, brightness up, disc, disc 1 , disc 2 , disc 3, play, play 1, pause, pause 1, play/pause, rewind, fast forward, skip down, skip up and so on..............)

The project aim is to capture the functions exits for a remote control (different brads and models across globe)in to sql database.

Each remote may have minimum 5 functions and maximum 150 functions.

In the front end tool i will select the the functions(from drop down list) available for that particular remote(AA0001) so that after i complete the task in from end the functions selected from drop down list becomes the input parameters.

Then the selected function will be marked as 1 and rest other(non selected functions in front end tool) marked as default 0.

Please let me know is there any way to pass only limited no of input parameters to stored procedure and non passed parameters are default 0.

Looking for ward for your help.

Regards
Tang

2

If you create a procedure with default values for the parameters like this:

CREATE PROCEDURE MySProc
    @VolumnUp int = 0,
    @VolumnDown int = 0,
    ...

you can call it with named parameters like this:

EXEC MySProc @VolumnDown int = 1

and you only need to specify some of the parameters, the rest (within the Sproc) will initialise with their default values.

If a parameter is NOT defined with a default value it must be included in the EXEC statement.

3

550 columns ina table is a strong indicator that something went really wrong with your database design. I suggest that you revisit your design before proceeding with more development.

4

thanks fore your response,

there is no other way to proceed.

5

Child Table of "attributes" of a remote control?

e.g. AttributeID = 1 for volume up, 2 = volume down and so on. VERY EASY to add an additional attribute in the future - just use the next available ID number (personally I would have a lookup-table of the ID values, with description, and use that to validate that the ID value was valid (and to provide a descriptive name for it, in place of the ID number on reports etc.))

So you wind up with tables:

A RemoteHeader table - One row for each Remote Control with, say, ID, Name etc.

Then a RemoteAttribute table - RemoteID, AttributeID and Value. Zero, one, or many rows PER RemoteHeader row.

Then, ideally, an AttributeDefinition table with AttributeID and Name (could also contain Min and Max values, e.g. if you want to store a range of values for that Attribute, rather than just 1 / 0. Silly example, but "Max Volume Setting" for example, could be restricted to 1 - 99)

6

thanks for your reply..

can u please explain in detail.. with sample

7

We can define a table valued parameter that can contains 550 + columns.

8

create table #devicetypes(devicetypeid int identity(1, 1) , devicetypename varchar(50))
insert into #devicetypes
select 'TV Remote Control'
union
select 'telepathic control'

create table #manufacturer(manufacturerid int identity(1, 1) , manufacturername varchar(50))
insert into #manufacturer
select 'Samsung'
union
select 'LG'
union
select 'Organizm'

create table #devices(deviceid int identity(1, 1) , devicename varchar(50), devicetypeid int, manufacturerid int)
insert into #devices
select 'iRemote', 1, 1
union
select 'Chimplants - get it?', 2, 3

create table #attributes(attributeid int identity(1, 1) , attributename varchar(50))
insert into #attributes
select 'Volume up'
union
select 'Volume down'
union
select 'Mute'
union
select 'channel up'
union
select 'Wake-on-low brain activity'
union
select 'Use the force'

create table #deviceattributes(deviceid int, attributeid int)

insert into #deviceattributes
select 1, 1
union
select 1, 2
union
select 1, 3
union
select 2, 5

select d.devicename, dt.devicetypename, m.manufacturername, att.attributename
from #devices d
join #devicetypes dt on d.devicetypeid = dt.devicetypeid
join #manufacturer m on m.manufacturerid = d.manufacturerid
join #deviceattributes da on da.deviceid = d.deviceid
join #attributes att on att.attributeid = da.attributeid

drop table #devices
drop table #devicetypes
drop table #manufacturer
drop table #deviceattributes
drop table #attributes

1 Like
9

I docked a mark for using "SELECT *" :heart_eyes:

Could you send me a link to an online retailer selling Organizm's telepathic controller using Chimplants pls - I could definitely use one of them !!

1 Like
10

P.S. You have too much spare time ... :slight_smile:

11

Chimplants made me do it, it is wifi enabled and my coworker or NSA has full control of it now. NSA do you hear me, stop it!!!

1 Like