Microsoft SQL Server 2005
Microsoft SQL Server 2005

Sql Server column vakue insert

Hi,

In my sql server table these are the columns:

ID CategoryID Description code
Q101 cat1 VLC - 001 Example1 VLC - 001
Q102 cat1 VLC - 002 Example1 VLC - 002
Q103 cat2 PAY - 001 Example1 PAY - 001

When the user input data into this table:
(1) I wanted to generate this code depending on the CategoryID i.e. For Cat1 VLC - 001, VLC -002 and so forth
(2) For each row for the description I need to prefix the respective code for each row they insert into table.

This data input not through a application or anything like that but they will do it in the management studio so I wanted to do this in table level in the sql server. Can I create a function and attach to this column so every time the user add a new row and will use this function to generate next available code for that row and also prefix this description as well.

Any help would very greatly appreciated....

ube100Asked: 2011-10-01

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 6

billfusionCommented: 2011-10-01

The easiest would be to only allow the categoryID to be entered by the user then use calculated values to insert into the rest of the columns. Your best bet is to use a stored procedure that you pass it the ID and category id. The procedure then sets up the proper strings either based on a lookup table, if you have many category type, or hard coded in the procedure. The procedure then insert the proper values in the table. The stored procedure also need to lookup the max number of records already in a table to find the correct value to append.

Calculate the Code first, then use the code to enter into the description.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

ube100Author Commented: 2011-10-01

This is not from a application but this is rather from a data entry by an admin staff using sql server management studio so can I still be able to call the stored procedure? If yes then how?

LVL 6

billfusionCommented: 2011-10-01

In that case triggers may be more appropriate. You can find a short description about creating triggers here:
http://msdn.microsoft.com/en-us/library/ms189799.aspx

LVL 6

billfusionCommented: 2011-10-01

A better introduction about triggers can be found here:
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

LVL 6

billfusionCommented: 2011-10-01

Can you include a sample of the code that you're trying to get working?

LVL 6

billfusionCommented: 2011-10-01

The last comment was an accident. Please disregard it.

ube100Author Commented: 2011-10-01

These are the two tables in question:

CREATE TABLE [sysdba].[CATEGORY](
      [CATEGORYID] [char](12) NOT NULL,
      [CREATEUSER] [char](12) NULL,
      [CREATEDATE] [datetime] NULL,
      [MODIFYUSER] [char](12) NULL,
      [MODIFYDATE] [datetime] NULL,
      [CATEGORYNAME] [varchar](55) NULL
) ON [PRIMARY]

CREATE TABLE [sysdba].[CALLOUTCOMES](
      [CALLOUTCOMESID] [char](12) NOT NULL,
      [CATEGORYID] [char](12) NOT NULL,
      [CREATEUSER] [char](12) NULL,
      [CREATEDATE] [datetime] NULL,
      [MODIFYUSER] [char](12) NULL,
      [MODIFYDATE] [datetime] NULL,
      [OUTCOMES] [varchar](80) NULL,
      [CALLOUTCOMECODE] [varchar](32) NULL
) ON [PRIMARY]

When they adding a new row to CALLOUTCOMES table they will use the categoryid from the category table and depending on the category we need to insert a appropriate code for CALLOUTCOMECODE column and then append that code as a prefix for the description which user entered. i.e. VLC - 001 Example1 VLC - 001

If we have to do this via a trigger then how we go about it please?

LVL 6

billfusionCommented: 2011-10-01

Can you list some row from Category, and what the user will enter in calloutcome? is the value VLC-001 retrieved from category? Or is it part of that code that's retrieved from category?

ube100Author Commented: 2011-10-02

Category:

Q6UJ9A0RCBTK      VLC Inquires
Q6UJ9A0RCBTL Payments

Calloutcomes:

Q6UJ9A0RCBU9      Q6UJ9A0RCBTK      U6UJ9A0000AF      2011-09-12 12:07:17.473      U6UJ9A0000AF      2011-09-12 12:07:17.473      VLC - 001 Navigation of te website      VLC - 001

Q6UJ9A0RCBUR      Q6UJ9A0RCBTL      U6UJ9A0000AF      2011-09-12 12:38:21.020      U6UJ9A0000AF      2011-09-12 12:38:21.020      PAY - 001 Monthly instalment paid      PAY - 001

VLC will represent VLC Inquiry category and anything come under that category will be VLC -001 for the first one and VLC - 002 for the 2nd one and so forth.