Division inside Insert stored procedure T-SQL

Question

I'm new to T-SQL and I was wondering if it's possible to do something like this

CREATE PROCEDURE [SISACT].[new_activo_fijo]
  @activo VARCHAR(8) ,
  @descripcion VARCHAR(60) ,
  @utiliza_serial BIT,
  *@serial VARCHAR(20) = NULL,*
  @fecha_adquisicion DATE,
  @referencia VARCHAR(10),
  @costo_adquisicion FLOAT,
  @vida_util INT,
  **@saldo_a_depreciar FLOAT = @costo_adquisicion / @vida_util,**
  @ultimo_periodo CHAR(6),
  *@periodo_saldo_cero CHAR(6) = NULL,*
  @fecha_incorporacion DATE,
  @fecha_desincorporacion DATE,
  @proveedor VARCHAR(8),
  @situacion INT
AS
INSERT INTO [SISACT].[ACTIVOS_FIJOS] (
activo, descripcion, utiliza_serial, serial, fecha_adquisicion, 
referencia, costo_adquisicion, vida_util, saldo_a_depreciar,
ultimo_periodo, periodo_saldo_cero, fecha_incorporacion,
fecha_desincorporacion, proveedor, situacion
)
VALUES(
  @activo, @descripcion, @utiliza_serial, @serial, @fecha_adquisicion,
  @referencia, @costo_adquisicion, @vida_util, @saldo_a_depreciar, 
  @ultimo_periodo, @periodo_saldo_cero,  @fecha_incorporacion,
  @fecha_desincorporacion, @proveedor, @situacion
)

Also, is it possible to initialize a parameter to NULL like I did just in case nothing sends from the form or should I do that when I'm sending the parameters?

I'm sorry if I'm asking something really basic, I'd appreciate the help.

Just in case, I'm using PHP and ODBC

Answer 1

Based on experimentation, here's what I found:

1. Can you do this: @saldo_a_depreciar FLOAT = @costo_adquisicion / @vida_util. Answer: no, SQL Server (2012 in my case) says Incorrect syntax near '/'. Wrapping it in parentheses (the last resort of the desperate :-) didn't help either.

2. Can you do this: @periodo_saldo_cero CHAR(6) = NULL. Answer: yes, and if @periodo_saldo_cero isn't passed it defaults to NULL.

As for your @saldo_a_depreciar column: is it always defined as @costo_adquisicion / @vida_util? If so you should calculate it as needed rather than storing it.

If @saldo_a_depreciar really is a value that should be stored rather than calculated, you just need to push the calculation into the procedure code rather than the parameter list:

CREATE PROCEDURE [SISACT].[new_activo_fijo]
  @activo VARCHAR(8) ,
  @descripcion VARCHAR(60) ,
  @utiliza_serial BIT,
  @serial VARCHAR(20) = NULL,
  @fecha_adquisicion DATE,
  @referencia VARCHAR(10),
  @costo_adquisicion FLOAT,
  @vida_util INT,
  @saldo_a_depreciar FLOAT = NULL
  @ultimo_periodo CHAR(6),
  @periodo_saldo_cero CHAR(6) = NULL,
  @fecha_incorporacion DATE,
  @fecha_desincorporacion DATE,
  @proveedor VARCHAR(8),
  @situacion INT
AS
  IF @saldo_a_depreciar IS NULL SET @saldo_a_depreciar = @costo_adquisicion / @vida_util
  INSERT INTO ... (and the rest of your procedure)

This will calculate @saldo_a_depreciar in two cases: (1) if you don't pass the parameter and (2) if you pass the parameter as null.