CODEWITHSUNDEEP
javasql-serverstored-procedures
Joakim M
Joakim M

Reputation: 1803

SQL parameter order

I am calling a MS SQL SP and have just added a new parameter to the SP. Looking something like this:

ALTER PROCEDURE yadayadaTable
(
    @Id int,
    @Date date,
    @Message nvarchar(255),
    @Output int OUTPUT,
    @fooValue int = 1
)

However this stopped the SP calls from working. Is the order of the sp parameters crucial? Hence,

Is it the output parameter that needs to be last?

Edit: The added parameter is the last one (fooValue) and I am calling it like this in java (dont know if it makes sense to you :))

ohOk = ohOk.set(mySqlConn.executeProcedure("dbo.yadayadaTable",
            "@Id:IN=pd;
            @Date:IN=pDate;
            @Message:IN=pNote;
            @Output:INOUT=pOutPut;
            @fooValue:IN=pfooValue",
            _internal_parameter_table,
            _internal_return_parameter_table,
            "proc1402661085026055"));

Thanks!

Upvotes: 0

Views: 1952

Answers (1)

sarin
sarin

Reputation: 5307

It depends how you call it.

if you call the stored procedure from T-SQL you can :

DECLARE @output int
EXEC yadayadaTable 1, '2015-10-01', 'A Message', @output OUTPUT, 2
or
EXEC yadayadaTable 1, '2015-10-01', 'A Message', @output OUTPUT
or
EXEC yadayadaTable @Output=@output OUTPUT, @Date='2015-10-01', @Id=1, @Message='A Message'

notice that if you don't specify the parameter names you have to pass the values in the order that the parameters are defined in the stored procedure.

if the OUTPUT parameter is defined in the stored procedure you need to pass one in from the calling method.

If you are calling this from server side code in c# for instance, at the point you create your command object to run your stored procedure, you will add your parameters and specify the names of them. The .net framework will generate the last example for you.

Best practice, always specify the parameter names. That way, changes you make to your SP's parameters are less likely to break existing code that uses it.

More help:

Upvotes: 3

Related Questions