SQL Server Functioln problem

[RayMilhon]

I have a sql server function that queries a table to get a value for a report. The function looks like this.

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??

[June7]

Please edit your post so the narrative below code is not between CODE tags. And when you say "tempvar", did you mean "tmpstr"?

bchmark is declared as a varchar(25) - how can you set it to a recordset or a string of more than 25 characters, whichever that set is attempting?

[RayMilhon]

Sorry thought I had put that after the code tag. yes tmpstr it's not the value being returned is < 25 characters. It kept erroring and I was trying to figure out what the problem was so I made some changes to return the query string instead of the value and forgot to make sure I change everything back. Getting a little frustrated and now making stupid mistakes. I'm going to work on something else and will look at this again tomorrow. Sorry

[NoellaG]

Try something like:

select @bnchmark =  Benchmark from hvvmgrpt.dbo.tbl_benchmarks2  cross apply string_split(@tmpstr,',') where ID = value and LOB = @strlob

remark: if the query returns more than 1 result, your function will return the first found value

[RayMilhon]

Try something like:

select @bnchmark =  Benchmark from hvvmgrpt.dbo.tbl_benchmarks2  cross apply string_split(@tmpstr,',') where ID = value and LOB = @strlob

remark: if the query returns more than 1 result, your function will return the first found value

Nope get this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'string_split'.

Completion time: 2024-04-25T09:25:12.5968019-07:00

[RayMilhon]

answered a little to quickly. Looked up string_split and it requires a compatibility levvel of 130. So I checked mine and it was set to 100 ran the following query

ALTER DATABASE AdventureWorks2022  
SET COMPATIBILITY_LEVEL = 130;  
GO

now the function works thank you