I work for a small Telephone Long D 2

[jeroocko]

I work for a small Telephone Long Distance Provider, I writting a SP that will return the long distance rate for a destination telephone number. We provide this service to different countries so we have different Rate Tables for each country. I'm trying to write a Dynamic SQL Statement that will select the correct rate given the callerID and the destination number. Looks something like this:

CREATE PROCEDURE GetRate (
@PhoneNumberDailed AS VARCHAR(20),
@CallerID AS VARCHAR(20),
@ChargePerCall AS FLOAT OUTPUT,
@ChargePerMinute AS FLOAT OUTPUT,
@MinimumCharge AS INT OUTPUT)
AS
.
. (Code that identifies what table to get the rate from.)
. SET @TableName = 'Country1' (for example)
.
BEGIN
decalre @query = varchar(2000)
SET @Query = 'SELECT ChargePerCall, ChargePerMinute, MinimumCharge FROM '+@TableName+' WHERE CountryCode+AreaCode+LocalNumber = '+@PhoneNumberDailed
EXECUTE (@Query)
END
.
.

This works fine except that I have to set my OUTPUT variables, So now it looks like this:


CREATE PROCEDURE GetRate
(
@PhoneNumberDailed AS VARCHAR(20),
@CallerID AS VARCHAR(20),
@ChargePerCall AS FLOAT OUTPUT,
@ChargePerMInute AS FLOAT OUTPUT,
@MinimumCharge AS INT OUTPUT
)AS

.
. (Code that identifies what table to get the rate from.)
. SET @TableName = 'table' (for example)
.
SET @Query = 'SELECT @ChargePerCall = ChargePerCall, @ChargePerMinute = ChargePerMinute, @MinimumCharge = MinimumCharge FROM '+@TableName+' WHERE CountryCode+AreaCode+LocalNumber = '+@PhoneNumberDailed
EXECUTE (@Query)
END
.
..

But because my I have variables inside a VARCHAR string I get this error : "Must Declare @ChargePerCall"
I can't concat my OUTPUT vars because they are of a different datatypes (INT and FLOAT), Hence my problem.

I need a way to set my OUTPUT variables using the appropriate Rate Table from the previous code. Can anyone help me?

THANKS.

 

[JNC73]

Hi Jeroocko,

I don't know if this is a possibility for you but it worked out fine for me.

When I create a SP and I need to give more then one variable back to the calling program (not an SP) then I make sure that the last command in a SP is a select with all the relevant variables with unique names. In this way I don't need a variable and therefor don't have to define one with a specific kind of Datatype.

For instance.

SELECT ChargePerCall = ChargePerCall, ChargePerMinute = ChargePerMinute, MinimumCharge = MinimumCharge FROM '+@TableName+' WHERE CountryCode+AreaCode+LocalNumber = '+ @PhoneNumberDailed
EXECUTE (@Query)

This must be the last command in the SP.

Another solution is the code you allready have but insert the result in a Temp table. At the end of the SP select the values from the Temp table.

Hope this helps,


JNC73

 

[jeroocko]

Good Idea!

Thanks JNC73 I will try this one.

Jeroocko