[Sql] highly customized auto generated column.

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

, +

Hi,

I have a project that brings me to work on SQL Server. A functionality is the next one:

On a serial number insertion, the SQL Server has to return N number of MAC addresses.
Those MAC addresses values are depending on the serial number value.

For a given serial number the returned N MAC addresses have to be in a range (or union of several unions).

Moreover those MAC adresses have to be consecutive for a request. It's important to know that several sessions could be opened at the same time, so multiple request can occur at the same time.

Could you please help me to determine with which objects I should work. Transactions, functions, triggers, stored procedure ?

Thank you all.

What I have tried:

I searched for a lot of similar problems, but I don't manage to find something comparable.

Posted 6-Jun-16 5:35am

DimitriStPi

Updated 6-Jun-16 5:52am

Add a Solution

Comments

RedDk 6-Jun-16 11:52am

Given that you have MAC addresses, this idea looks to be a "fill-in-the-blanks" problem. Given A, B, W, Z, tabulate such that C, D, E ...V and X & Y are present. Yes?

DimitriStPi 6-Jun-16 11:55am

The idea would be to create dynamically the MACS as an answer to a serial number insertion. But it isn't maybe the good mean to do it.

RedDk 6-Jun-16 12:00pm

There are any number of ways to "serialize" the missing MAC addresses. Leverage AVERAGE; target the built-in math function AVG() and output that value ... The thing is, TSQL does tables extremely easily, but you as the user must set up the dimensions. I'm still not sure I understand how this isn't "fill-in-the-blanks".

[EDIT]
It's best if you show some code now.
[END EDIT]

DimitriStPi 6-Jun-16 12:04pm

There are surely many ways to do it, but I don't know it dear Red ! :)
I didn't understand the average thing in your comment.
Explain me the way you see my problem as a "fill-in-the-blanks" please !

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-06-06T05:42:00

Solution 1

Based on the title of your question ([Sql] highly customized auto generated column) I believe that you have a wrong approach. If you need a set of MAC addresses with single call you cannot bind the logic into a single row. You need to create for example a function or stored procedure to create the set of needed addresses and store those wherever they need to be.

Since several statement are probably involved you should wrap the whole operation inside a transaction in order to ensure movement from one consistent state to another.

What comes to the logic inside the procedure or function, that's up to the exact requirements, but I suggest breaking the problem into smaller pieces and trying to solve them one-by-one.

Posted 6-Jun-16 5:42am

Wendelius

Comments

DimitriStPi 6-Jun-16 11:53am

Hi,

Thank you for giving a first answer to my problem.

As you said, I should break the problem in smaller pieces, and here comes my main problem. I think I don't know enough the features of SQL Server. That's why I ask for some help to be guided to the actual features.

My pieces would be :
- Parametrized request (for the number of needed MACS)
- MAC creation within range and consecutive (with management of the unions limits)
- Link between serial - range of MACS
- Management of the multi session side (locks?)
- Return of the sets

Wendelius 6-Jun-16 12:12pm

What comes to the parameterized request, please take time to go through CREATE PROCEDURE (Transact-SQL)[^].

The consecutive numbering could possible be achieved by preventing simultaneous number reservertions. This would require locking. For example if you add generated numbers into a table you can lock the table until the numbers have been delivered. However, keep in mind that this will cause a queue

And returning a set of data is basic functionality of a stored procedure so this comes back to the first bullet.

What comes to links between serial - range of MACS, this is something I didn't understand.

DimitriStPi 7-Jun-16 3:10am

Thank you for your reply. I'll read what you linked.

The meaning of Link between serial - range of MACS is the following : for a given serial number, the possible attributed MACS are in a certain range, for another given serial number, the possible attributed MACS are in another range. The MACS are conditioned to the serial number value.