Numerical Parameter without field 1

[briesen]

Is it possible to have an SQL parameter in which is just asks for a numerical value to input into a formula? It would be a non-existing field that would just import whichever value I wanted.

Everything I've seen for SQL parameters is that you input a field and it asks you which value to use for the field. I simply want a random numeric value.

For example:

SELECT
vtr."Billing Date"
vtr."Amount"
vtr."AP"/(1+{PARAMETER})
FROM vtr
WHERE
vtr.Customer='AAA'

I would then input .12 when it asked and the formula would generate.

Is this doable?

 

[markros]

Sure, you can use the parameter in such content without a problem. Just make sure to prevent dividing by 0, e.g. may be write this as

case when 1+ @Param = 0 then 0 else AP/ (1 + @Param) end as MyComplexExpression

 

[briesen]

How do I actually create the parameter? Never done this before...

 

[markros]

You need to request for the parameter in your front-end. What front-end you're using?

For the tests you can create it as a stored procedure and declare your parameter before calling it.

In T-SQL itself there is no InputBox function to request for the parameter.

 

[briesen]

I'm just using Microsoft Query. I decided to scratch the inputbox idea, and simply declare and set a variable.

I did it successfully, but when I go back to edit the SQL again, all the code has dissapeared. Do you know why this is?

All I added is below and then I put @Param in the SELECT area:

DECLARE @Param decimal
SET @Param= .12

 

[markros]

Sorry, I'm not sure why did it disappear. May be you need to save the sql file first. I'm using SQL Server directly (SSMS) to write / test such things.

 

[gmmastros]

DECLARE @Param decimal
SET @Param= .12

That's not good enough. The decimal data type CAN have precision and scale settings. When you don't specify the precision and scale, (18,0) are used (which is roughly equivalent to an int).

Do this:

DECLARE @Param decimal[!](20,10)[/!]
SET @Param= .12

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom