8

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Binesh Nambiar C
  • 152
  • 1
  • 2
  • 9
  • What is the syntax error you are getting and how are the variables declared in each sp? – Sam Jun 20 '16 at 02:25
  • If you're getting an error with -1000, it's likely because your query doesn't handle negative numbers for some reason other than input. – ZLK Jun 20 '16 at 02:26
  • I'm calling in query window like this. Its not -1000. its -@amt. Sorry. Updating the question – Binesh Nambiar C Jun 20 '16 at 02:32
  • In that case, your issue with the @amt example is that you can't use +/- (unary operators) in your parameters. So yes, you would need a separate variable. The difference between '-1000' and '-@variable' is that -1000 isn't performing an operation, it's just a number. – ZLK Jun 20 '16 at 02:46

2 Answers2

8

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;
square_particle
  • 526
  • 2
  • 7
3

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
Myo Myint Aung
  • 147
  • 1
  • 3