Skip to content
Advertisement

SQL Stored Procedure Parameters Being Assigned to Variables

I’m sure there is some sound logic/best practice but I’d like it explained to me if possible. Looking at SQL stored procedures written by a previous employee he always declares 1 variable for each parameter and sets the variable equal to the parameter value and then uses the variable throughout the remainder of the stored procedure. What is the added value in not just using the parameter vs assigning to a variable?

ALTER procedure [dbo].[sp_shipping]
(
@p_date varchar(10),
@p_status varchar(10),
@p_cust nvarchar(20)
)
as

set nocount on

declare @v_date datetime,
        @v_status varchar(10),
        @v_cust nvarchar(20)

select @v_date = convert(datetime,@p_date),
       @v_status = @p_status,
       @v_cust = @p_cust

Advertisement

Answer

It is likely just a preference/style but it is worth noting that it does have an impact on the @p_date variable,

select @v_date = convert(datetime,@p_date)

This will throw an error if it is unable to convert the parameter to a datetime and it stores the result of the conversion so you don’t have to repeat it later in the stored procedure.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement