Stored Procedure for Approval

• SQL-Learner

  SSCommitted

  Points: 1537

  More actions

  December 16, 2016 at 1:59 pm

  #401220

  Hi,

  I have a table

  CREATE TABLE [dbo].[Table](

  [EmployeeID] [int] NOT NULL,

  PartnerID] [int] NOT NULL,

  [Approved] [bit] NULL,

  [ApprovedBy] [nvarchar](255) NULL)

  Insert into dbo.Table values (10111, 20111 , 0 ,Null)

  Now I need to write a stored procedure for .net application. It they accept the Employee application then Approved should become 1 and they reject the employee application the value should not change. It should remain 0

• Jeff Moden

  SSC Guru

  Points: 1004361

  More actions

  December 16, 2016 at 2:21 pm

  #1918494

  SQL-Learner (12/16/2016)

  ---

  Hi,

  I have a table

  CREATE TABLE [dbo].[Table](

  [EmployeeID] [int] NOT NULL,

  PartnerID] [int] NOT NULL,

  [Approved] [bit] NULL,

  [ApprovedBy] [nvarchar](255) NULL)

  Insert into dbo.Table values (10111, 20111 , 0 ,Null)

  Now I need to write a stored procedure for .net application. It they accept the Employee application then Approved should become 1 and they reject the employee application the value should not change. It should remain 0

  1. As you're already finding out, the BIT column that you call "Approved" is already raising hell with your code. Get rid of the Approved column. Having a non-NULL value in the ApprovedBy column is indication that the row is approved rendering the Approved column redundant and unnecessary.

  2. Add an ApprovedDate column. You'll find cause to use it in the future.

  3. I don't know if it was just an experiment but the table should be called something other than just "TABLE".

  4. You need to identify a Primary Key, which could be EmployeeID and PartnerID and should probably be the clustered index, as well.

  5. NVARCHAR(255) is probably serious overkill for width. Depending on where in the world you're working, NVARCHAR might be overkill, as well. It is NOT "pre-optimization" to right-size columns. Remember that indexes can be a max of 900 bytes (450 NVARCHAR) and that you'll need to use the same datatype for ApprovedBy everywhere to avoid implicit conversions. Also, there should be a foreign key for the "ApprovedBy" person and that means you need to check the datatype there, as well. Further, ApprovedBy should probably be an INT with an FK reference back to the Employee table.

  6. If the PartnerID refers to an employee, it should be named PartnerEmployeeID and also have an FK back to the employee table.

  --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)