CODEWITHSUNDEEP
functionsql-server-2008-r2parameter-passing
NonProgrammer
NonProgrammer

Reputation: 1387

Passing parameter to a table-valued function

I am trying to pass a parameter value to a table-valued function which has four parameters and "returns table". However, I receive following error when I pass a parameter value to one of its varchar parameter:

Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to bigint.

declare @Scenario1 as varchar(30)
set @Scenario1 = '2017_B01'

select *
From      [dbo].[fn_GetAEAssumptionFacts](@Scenario1,null,null,null) fng

Glimpse at function:

CREATE FUNCTION [dbo].[fn_GetAEAssumptionFacts]

(
     @pScenarioName  varchar(500) = NULL
    ,@pBuildingID varchar(500) = NULL
    ,@pLeaseID    varchar(500) = NULL
    ,@pTenantName   varchar(500) = NULL
)

RETURNS TABLE
AS
RETURN

select 
.....
from ae11.dbo.rvw_FinancialLineItems fli
....
INNER JOIN ae11.dbo.rvw_Scenarios s on s.Id = pas.ScenarioId
left join 
    (select 
        externalID, 
        PropertyAssetId, 
        LeaseID, 
        BeginDate 
    from ae11.dbo.ivw_Leases 
    WHERE PropertyAssetID IN 
    (select ID from AE11.dbo.PropertyAssets where scenarioID = 
    (CASE WHEN isnull(@pScenarioName, '') = '' 
                    THEN (select  ID from AEX.[dbo].[ConfigurationFieldTable]
                    where [Type] = 'Lease Connect Current Scenario' )
                ELSE @pScenarioName
              END)
    )
    ) lea 
ON lea.LeaseID = uni.ExternalID
AND lea.PropertyAssetID = uni.PropertyAssetId               

where 1=1
......
  AND s.id = (CASE WHEN isnull(@pScenarioName, '') = '' 
                    THEN (select  ID from AEX.[dbo].[ConfigurationFieldTable]
                    where [Type] = 'Lease Connect Current Scenario' )
                ELSE @pScenarioName
              END)

Upvotes: 0

Views: 107

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Here

 (CASE WHEN isnull(@pScenarioName, '') = '' 
                THEN (select  ID from AEX.[dbo].[ConfigurationFieldTable]
                where [Type] = 'Lease Connect Current Scenario' )
            ELSE @pScenarioName
 END)

You are taking a value depending on @ScenarioName. This will either be the result of select ID from AEX.[dbo].[ConfigurationFieldTable] WHERE... or the content of @ScenarioName.

I assume, that this ID is a bigint, while your @SenarioName is a string. And the s.ID you want to compare it against - I don't know...

But - to be honest - my magic crystall ball is out for cleaning and the information you provide is not enough.

Upvotes: 1

Related Questions