>Is there a way to declare a public variable in SQL2005
>
>I'm storing some formulas in a table to calculate provions, some of these formulas contains variables (i.e. @Var1, @var2, ...), I'm excuting these formulas in a stored procedure as follows
>
>set @cmd='FormulaToExecute'
>exec (@cmd)
>
>System is generating an error against every variable used inm the formula expression (Must declare the scalar variable "@Var1")


Victoria,

There is no concept of PUBLIC variables in T-SQL. Variables declared within Function, Stored Procedure or Batch have LOCAL scope, i.e. not visible from outside that code.

Maybe you should consider sending @Var1 and @Var2 as parameters to the stored procedure. Since you are using dynamic sql you should use SP_EXECUTESQL instead of EXECUTE in order to pass parameters to calling stored procedure.

Another alternative would be to store the values into GLOBAL Temporary table (prefix global temporary table names with a double number sign ##table_name) and query that table from the SP.


Regards,

Zoran