Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Passing Expressions to Stored Procedure Parameters

By: FYIcenter.com

(Continued from previous topic...)

Can You Pass Expressions to Stored Procedure Parameters?

Can you pass expressions to stored procedure parameters? The answer is no.

When executing stored procedures, all input values must be entered as data literals, which can be specified within single quotes ('), or without them if they cause no confusion. The tutorial exercise below shows you how input values should be specified:

CREATE PROCEDURE area_of_circle @radius REAL
   PRINT 'Radius = ' + STR(@radius,9,3);
   PRINT 'Area = ' + STR(3.14*@radius*@radius,9,3);

-- Input value without quotes
EXEC area_of_circle 1.5;
Radius =     1.500
Area =     7.065

-- Input value with quotes 
EXEC area_of_circle '1.5';
Radius =     1.500
Area =     7.065

-- Expressions are not allowed
EXEC area_of_circle 1.0+0.5;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.

(Continued on next topic...)

  1. What Are Stored Procedures?
  2. How To Create a Simple Stored Procedure?
  3. How To Execute a Stored Procedure?
  4. How To List All Stored Procedures in the Current Database?
  5. How To Drop an Existing Stored Procedure?
  6. How To Create a Stored Procedure with a Statement Block?
  7. How To End a Stored Procedure Properly?
  8. How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
  9. How To Get the Definition of a Stored Procedure Back?
  10. How To Modify an Existing Stored Procedure?
  11. How To Create Stored Procedures with Parameters?
  12. How To Provide Values to Stored Procedure Parameters?
  13. What Are the Advantages of Passing Name-Value Pairs as Parameters?
  14. Can You Pass Expressions to Stored Procedure Parameters?
  15. How To Provide Default Values to Stored Procedure Parameters?
  16. How To Define Output Parameters in Stored Procedures?
  17. How To Receive Output Values from Stored Procedures?
  18. How To Create a Local Temporary Stored Procedure?
  19. Can Another User Execute Your Local Temporary Stored Procedures?

Related Articles:


Other Tutorials/FAQs:


Related Resources:


Selected Jobs: