CODEWITHSUNDEEP
sqlsql-server
Hamza
Hamza

Reputation: 17

Parameters input for a SQL stored procedure without the @ sign

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!

Upvotes: 0

Views: 841

Answers (2)

Francisco Sevilla
Francisco Sevilla

Reputation: 104

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'

Upvotes: 0

VBoka
VBoka

Reputation: 9083

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

Upvotes: 3

Related Questions