I have a sql server function that queries a table to get a value for a report. The function looks like this.
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[benchmark]
(
-- Add the parameters for the function here
@strlob varchar(25),
@typeid int)
RETURNS varchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @bchmark varchar(25)
Declare @tmpstr varchar(25)
--declare @selectstr varchar(254)
set @tmpstr = Case @typeid
when 1 Then '2,16,1'
when 2 then '10,15,12'
When 3 then '3,4'
when 4 then '9,14'
when 5 then '11,13'
end
-- Add the T-SQL statements to compute the return value here
--set @selectstr = 'SELECT Benchmark from hvvmgrpt.dbo.tbl_benchmarks2 where LOB = @strlob and ID In (' + @tmpstr + ')'
set @bchmark = (SELECT Benchmark from hvvmgrpt.dbo.tbl_benchmarks2 where LOB = @strlob and ID In ( @tmpstr ))
-- Return the result of the function
RETURN @bchmark
END
The problem is in the end of the set statement. and ID in ( @tmpstr)) The sql looks fine but instead of looking at the numbers for example when @typeid = 3 then then I'm looking for the ID number of either 3 or 4. Which works perfectly when I run the query in ssms but is looking at the 3,4 as a string instead of 2 numbers. since tempvar is a string how do I fix this??