Ali Shah
asked on
How to use a return Varchar value from a function in where clause
Hi there
,
I have a function which returns a string i want to use this in where clause to return the serviceCode A, C or both. I trying to do it as follows. But doesn't return any data when i use it in select statement. Any idean why? or i would appreciate if you could please tell me the better way of doing it?
and in my select statement i am using the function as
can you please help
,
I have a function which returns a string i want to use this in where clause to return the serviceCode A, C or both. I trying to do it as follows. But doesn't return any data when i use it in select statement. Any idean why? or i would appreciate if you could please tell me the better way of doing it?
ALTER FUNCTION [dbo].[ret_ServiceCode]
(
-- Add the parameters for the function here
@ServiceCode int
)
RETURNS nvarchar(200)
AS
BEGIN
-- Declare the return variable here
Declare @SerCode nvarchar(200)
-- Add the T-SQL statements to compute the return value here
If @ServiceCode = 1
Begin
set @SerCode = 'Select ServiceCode from tblServiceCodes Where ServiceCode = A'
End
Else If @ServiceCode = 2
Begin
set @SerCode ='Select ServiceCode from tblServiceCodes Where ServiceCode = C'
End
Else If @ServiceCode = 3
Begin
set @SerCode ='Select ServiceCode from tblServiceCodes Where ServiceCode in(A,C)'
End
-- Return the result of the function
RETURN @SerCode
END
and in my select statement i am using the function as
select * from tblPatient where ServiceCode in (dbo.ret_ServiceCode(1))
can you please help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The error is that you are returning a string containing a query, instead of the possible results.
Take a look on this documentation:
https://msdn.microsoft.com/en-us/library/ms188001.aspx
Take a look on this documentation:
https://msdn.microsoft.com/en-us/library/ms188001.aspx
ASKER
Ah Thanks a lot. That works perfectly.
Kindest Regards
Kindest Regards
Then use '=' instead of IN:
Open in new window