Stored Procedure or Trigger for Autogenerate Colunm
-
Hello All, Am creating a small Projects using Vb.Net and Sql server 2005, in that i want to get a input automatically for one column(Eno column), that column is a alphanumeric column, i want to store information automatically like below, if the Employeename is 'Anton' it will automatically fill the Eno as A001, if Employeename is 'Brail' then it will fill 'Boo1' if again Employeename is 'Berg' then 'b002' and so on. can i get that by adding a trigger or stored Procedure, if so then tell simply code examle for this, this very urgent for me, so Pleeeeeeeeeeeeease help me. Thanks Riz
-
Hello All, Am creating a small Projects using Vb.Net and Sql server 2005, in that i want to get a input automatically for one column(Eno column), that column is a alphanumeric column, i want to store information automatically like below, if the Employeename is 'Anton' it will automatically fill the Eno as A001, if Employeename is 'Brail' then it will fill 'Boo1' if again Employeename is 'Berg' then 'b002' and so on. can i get that by adding a trigger or stored Procedure, if so then tell simply code examle for this, this very urgent for me, so Pleeeeeeeeeeeeease help me. Thanks Riz
I am going to assume you are trying to create a key field for your employee, if so then: DO NOT ADD INTELLIGENCE TO A PRIMARY ID/KEY FIELD, you are doing this by adding the first character of the surname. This is wrong, it should simply be the record identifier, use the surname field for search/sorting NOT the ID field. Generating an employee number for human consumption (timecards or something) is a different matter. I would put it in the inserting stored procedure (I never use triggers) Before the insert get the count of existing surnames with the 1st char Select @N = (count(*) + 1) From Employee where Surname like 'A%' (this fails when the employee changes surnames - gets married and is the reason for the initial comment) or Select top 1 employee where ENO like 'A%' order by ENO desc Add the text to the ENO field
Never underestimate the power of human stupidity RAH
-
I am going to assume you are trying to create a key field for your employee, if so then: DO NOT ADD INTELLIGENCE TO A PRIMARY ID/KEY FIELD, you are doing this by adding the first character of the surname. This is wrong, it should simply be the record identifier, use the surname field for search/sorting NOT the ID field. Generating an employee number for human consumption (timecards or something) is a different matter. I would put it in the inserting stored procedure (I never use triggers) Before the insert get the count of existing surnames with the 1st char Select @N = (count(*) + 1) From Employee where Surname like 'A%' (this fails when the employee changes surnames - gets married and is the reason for the initial comment) or Select top 1 employee where ENO like 'A%' order by ENO desc Add the text to the ENO field
Never underestimate the power of human stupidity RAH
-
I am going to assume you are trying to create a key field for your employee, if so then: DO NOT ADD INTELLIGENCE TO A PRIMARY ID/KEY FIELD, you are doing this by adding the first character of the surname. This is wrong, it should simply be the record identifier, use the surname field for search/sorting NOT the ID field. Generating an employee number for human consumption (timecards or something) is a different matter. I would put it in the inserting stored procedure (I never use triggers) Before the insert get the count of existing surnames with the 1st char Select @N = (count(*) + 1) From Employee where Surname like 'A%' (this fails when the employee changes surnames - gets married and is the reason for the initial comment) or Select top 1 employee where ENO like 'A%' order by ENO desc Add the text to the ENO field
Never underestimate the power of human stupidity RAH
Hello All, Below Code generates Error in if, Please let me know whats wrong in this create procedure refno_stock as begin declare @stockrefno as varchar(50), @category as varchar(50) if @category = 'Desktop' begin select ( count(*)+1) as Stockrefno from stockmaster where category like'd%'; end if if @category = 'Laptop' begin select ( count(*)+1) as Stockrefno from stockmaster where category like'L%'; end if if @category = 'HDD' begin select ( count(*)+1) as Stockrefno from stockmaster where category like'H%'; end if
-
Hello All, Below Code generates Error in if, Please let me know whats wrong in this create procedure refno_stock as begin declare @stockrefno as varchar(50), @category as varchar(50) if @category = 'Desktop' begin select ( count(*)+1) as Stockrefno from stockmaster where category like'd%'; end if if @category = 'Laptop' begin select ( count(*)+1) as Stockrefno from stockmaster where category like'L%'; end if if @category = 'HDD' begin select ( count(*)+1) as Stockrefno from stockmaster where category like'H%'; end if
you use END instead of END IF
Never underestimate the power of human stupidity RAH
-
you use END instead of END IF
Never underestimate the power of human stupidity RAH