How to keep a variable in sql stored procedure constant and unchanged in every call to the stored procedure

Question

I have a stored procedure up_InsertEmployees. I have a functionality where I am uploading a batch of employee details into the web application. This functionality inserts the employees details into the DB using the above mentioned stored procedure.

The stored procedure goes something like this

create procedure 'sp_InsertEmployees'
    (@EmployeeUN,
     @FirstName,
     @LastName,
     @City, @Ref)
BEGIN
    declare @BatchRef varchar(20) 

    set @BatchRef = @Ref+GetUTCDate()

    Insert into Employee(EmployeeUN, FirstName, LastName, City, BatchRef) 
    Values(@EmployeeUN, @FirstName, @LastName, @City, @BatchRef)
END

Here the column Ref holds the reference of the batch upload that I have performed. The value BatchRef has to be the same for all the employees of that particular batch. Since I am using GetUTCDate() the value of BatchRef might change with every employee that is being inserted. Can somebody please let me know how I can calculate the value of BatchRef when the first employee is being inserted and then keep it constant there on? I want this to be done in SQL only and not in the code. And also I want the datetime value to be in BatchRef so that each batch values are unique.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 19 '15 at 05:48
You should return a variable and everytime you run this stored procedure, just return that value and send it back into the stored procedure. It can easily be used as 'Select @Var' or use OutPut Variable or any other for the output. — Sorrel Vesper, Feb 19 '15 at 05:57
build the @BatchRef outside the proc as string and send it to the proc as a param — Dudi Konfino, Feb 19 '15 at 08:39

score 0 · Answer 1 · edited May 23 '17 at 11:43

The best way to keep a consistent BatchRef value across multiple rows being inserted is to insert all of the rows from that batch at the same time ;-). And doing so will also have the benefit of being quite a bit more efficient :-).

Assuming you are using SQL Server 2008 or newer, this can be accomplish via Table-Valued Parameters (TVPs). I have detailed the approach in my answer to the following question:

Pass Dictionary<string,int> to Stored Procedure T-SQL

For this particular use-case, the stored procedure would look something like the following:

CREATE PROCEDURE InsertEmployees
(
   @EmployeeBatch   dbo.EmployeeData READONLY,
   @Ref             VARCHAR(50)
)
BEGIN
    DECLARE @BatchRef VARCHAR(50);

    SET @BatchRef = @Ref + CONVERT(VARCHAR(30), GetUTCDate(), 121);

    INSERT INTO Employee (EmployeeUN, FirstName, LastName, City, BatchRef)
      SELECT eb.EmployeeUN, eb.FirstName, eb.LastName, eb.City, @BatchRef
      FROM   @EmployeeBatch eb;
END

Anyone stuck on SQL Server 2005 can accomplish the same basic concept using XML. It won't be as efficient as the entire XML document needs to be created in the app code and then parsed into a temp table in the stored procedure, but it is still a lot faster than doing row-by-row operations.

Firstly thank you for answering and the solution seems to be a great one. But this is an existing sp for a project that I am working on and following your approach involves code change too in a lot of places. In this case individual records are being passed to the sp one at a time and hence i cannot follow the above approach. Any other solutions are welcome. Thanks — Akshatha, Feb 19 '15 at 07:44
@AkshathaPatil Understood. So is `@Ref` being passed in unique for each batch already? Or is that value ever reused? — Solomon Rutzky, Feb 19 '15 at 14:08
It is being passed in unique for each batch. It is just used to store in the db and used for differentiationg between each upload. — Akshatha, Feb 20 '15 at 10:12

score 0 · Answer 2 · answered Feb 20 '15 at 13:50

Given:

@Ref is unique per each batch and never reused
@Ref needs to stay unique per each batch, not unique per each Employee that is uploaded
There is no option to alter the upload process from one call per Employee to doing the entire batch as a single set
You want the time that the batch started (i.e. when the first Employee in each batch is uploaded)

Then:

Don't combine @Ref with GETUTCDATE(), but instead track the start time in another table.

CREATE TABLE dbo.EmployeeBatchStartTime
(
  BatchRef  VARCHAR(50) NOT NULL
          CONSTRAINT [PK_EmployeeBatchStartTime] PRIMARY KEY,
  StartTime DATETIME NOT NULL
          CONSTRAINT [DF_EmployeeBatchStartTime_StartTime] DEFAULT (GETUTCDATE())
);

Then you can check to see if a row for the passed-in value of @Ref exists in that table and if not, insert it, else skip it.

CREATE PROCEDURE InsertEmployee
(
   @EmployeeUN   DataType,
   @FirstName    DataType,
   @LastName     DataType,
   @City         DataType,
   @Ref          VARCHAR(50)
)
AS
SET NOCOUNT ON;

IF (NOT EXISTS(
      SELECT *
      FROM   dbo.EmployeeBatchStartTime
      WHERE  BatchRef = @Ref
              )
   )
BEGIN
  INSERT INTO dbo.EmployeeBatchStartTime (BatchRef)
  VALUES (@Ref);
END;

INSERT INTO Employee (EmployeeUN, FirstName, LastName, City, BatchRef)
VALUES (@EmployeeUN, @FirstName, @LastName, @City, @BatchRef);

This not only lets you keep the value of BatchRef clean and usable (instead of combining a DATETIME value into it) but also gives you a DATETIME value that is usable without any error-prone text parsing or conversion from a string into a DATETIME datatype. This means that you can even add an index, if need be, to the StartTime field in the EmployeeBatchStartTime which will allow you to JOIN to that table on BatchRef and then use StartTime in an ORDER BY and it will be rather efficient :). AND, this requires no change at all to the existing app code :) :).

How to keep a variable in sql stored procedure constant and unchanged in every call to the stored procedure

2 Answers2