Stored procedure with named parameter and calculation

Question

I'm calling a stored procedure with named parameter.

exec MySP @name = 'binesh', @amount = @amt, @date = @date

It's working fine for me.

But when I'm trying

exec MySP2 @name = 'binesh', @amount = -@amt, @date = @date

or

exec MySP3 @name = 'binesh', @amount = @amt, @date = convert(varchar(25), @date, 131)

I get a syntax error.

Is this mandatory that I need to create separate variables for each (common sense tells it won't be the way). So what is the syntax for this?

Thanks all

Binesh

Solution 1

You can not construct input "in-line" for stored procedures. You must resolve the inputs prior to using them.

For example, you need to do something like this (as well as resolve the other parameters) ...

declare
    @date varchar(25) = convert(varchar(25), @date, 131);

exec MySP3 @name = 'binesh', @amount = @amt, @date = @date;

Solution 2

I think because of procedure data type you can't do this. I guess @amount is int and @date varchar(25) in procedure parameter. if @date is varchar you can not directly put @date=convert(varchar(25),@date,131). Use same type variable to convert.My suggestion is

declare @amt INT= 100; --some value
set @amt = @amt * -1;
declare @date DATE = getdate(); -- some date
declare @date2 VARCHAR(25);
set @date2 = convert(varchar(25),@date,131); --convert to varchar
exec MySP3 @name='binesh', @amount = @amt,@date=@date2

Comments

[Binesh Nambiar C]

The date in the sp is type of varchar only & -amt should take in what ever case right?

[Myo Myint Aung]

yes, you can adjust with local variable like this declare @amt INT= 100; set [@amt] = [@amt] * -1