I have a function that extracts the first "word" in a string and checks if this word appears in another table. If so it returns string else an empty string . But it is always returning an empty string. To make life a little more challenging SSMS V18 has removed the debugger. I have tried executing individual lines in a query window and they appear to work ok. any help appreciated:
I have tried changing all the varchar(max) types to varchar(1000) but that makes no difference.
Code:
ALTER FUNCTION [dbo].[GetBudgetCode]
(
@strInput as varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @strOutput as varchar(max), @strInprocess as varchar(max)
-- Add the T-SQL statements to compute the return value here
SET @strInprocess = rtrim(LEFT(@strinput,CHARINDEX(' ',@strinput)))
SET @strOutput = ''
IF EXISTS (SELECT BL_ID FROM dbo.tblBudgetlines WHERE BL_ID = @strInprocess)
BEGIN
SET @strOutput = @strInprocess
END
-- Return the result of the function
RETURN @strOutput
END
I have tried changing all the varchar(max) types to varchar(1000) but that makes no difference.