Parameters input for a SQL stored procedure without the @ sign

Question

I created a stored procedure named calculateLLPA that takes different parameters. When I type EXEC calculateLLPA, Intellisense suggest that I enter first '@variable1name VALUE, @variable2name VALUE'etc. What I am trying to accomplish is that SQL doesn't need to require @variable1name as a parameter, but instead just the value, and if a value needs to be defaulted, then added a comma right next to it. Example: calculateLLPA(30,60,,,67) the empty fields in between commas will take the default value instead of calculateLLPA(@year 30, @month 60, @minute 67) the empty fields in between commas will take the default value Here is how my stored proc looks:

CREATE PROCEDURE calculateLLPA (
@year VARCHAR(4)
@month VARCHAR(2)
@second VARCHAR(2) = '5'
@time VARCHAR(5) =  '4'
@minute VARCHAR(2)
)

Thanks for your help!

Why would you want to do this? You should be using the parameter names anyway. It makes your code far less confusing. Not to mention if you call a procedure without naming the parameters and the list of parameters changes....your code is silently broken. — Sean Lange
– Sean Lange, Commented Jan 24, 2020 at 22:41
I agree with you. But it's part of a project for work and that's how they want it to be setup. Do you think there is a way? I thought I saw some stored proc/functions that take parameters directly without naming which parameter we're defining — Hamza
– Hamza, Commented Jan 24, 2020 at 22:44

VBoka · Accepted Answer · 2020-01-24 22:50:54Z

3

I believe you are looking for something like this:

exec calculateLLPA '2019', '05', default, default, '20'

And if a created procedure has all parameters defined like @time VARCHAR(5) = '4' you can call it like this:

exec calculateLLPA

edited Jan 24, 2020 at 22:50

answered Jan 24, 2020 at 22:47

VBoka

9,0933 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sean Lange Over a year ago

This is indeed how you would do it....but wow that is a brittle way to deal with parameters.

VBoka Over a year ago

@SeanLange, I agree. Not recommended. But when asked ... :)

Francisco Sevilla · Accepted Answer · 2020-01-24 23:14:02Z

0

You can exclude those variables if the value is set by default:

CREATE PROCEDURE calculateLLPA (
    @year VARCHAR(4) = NULL
    ,@month VARCHAR(2) = NULL
    ,@second VARCHAR(2) = NULL
    ,@time VARCHAR(5) = NULL
    ,@minute VARCHAR(2) = NULL 

AS 
BEGIN
IF @second IS NULL
    SET @second = '5'

IF @time IS NULL
    SET @@time = '4'



--Calculate LLPA logic
END

)

And if you want to use default values just execute it:

EXEC calculateLLPA '2019', '05', NULL, NULL, '20'

Otherwise:

EXEC calculateLLPA '2019', '05', '5', '4', '20'

edited Jan 24, 2020 at 23:14

answered Jan 24, 2020 at 22:54

Francisco Sevilla

1141 silver badge7 bronze badges

3 Comments

VBoka Over a year ago

And how will you send the parameter to the procedure when you want to change second and time ?

Francisco Sevilla Over a year ago

You're alright, surfing SO found this answer , I think the variable must be declared as NULL and set the value after in case it applies.

Hamza Over a year ago

Is there a way for me to not type 'NULL'? In other words, instead of this: EXEC calculateLLPA '2019', '05', NULL, NULL, '20 Do this EXEC calculateLLPA '2019', '05', , , '20

Collectives™ on Stack Overflow

Parameters input for a SQL stored procedure without the @ sign

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related