Link to home
Start Free TrialLog in
Avatar of Ali Shah
Ali ShahFlag for United Kingdom of Great Britain and Northern Ireland

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?

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

Open in new window


and in my select statement i am using the function as
select * from tblPatient where ServiceCode in (dbo.ret_ServiceCode(1))

Open in new window


can you please help
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

It only returns a value, right?
Then use '=' instead of IN:
select * from tblPatient where ServiceCode = dbo.ret_ServiceCode(1)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Ali Shah

ASKER

Ah Thanks a lot. That works perfectly.
Kindest Regards