Stored Procedure for Approval

Question

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

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)