scalar function not working and can't debug (1 Viewer)

Zedster

Registered User.
Local time
Today, 21:50
Joined
Jul 2, 2019
Messages
168
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:

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.
 

sonic8

AWF VIP
Local time
Today, 22:50
Joined
Oct 27, 2015
Messages
998
Focus on this part:
Code:
SET @strInprocess = rtrim(LEFT(@strinput,CHARINDEX(' ',@strinput)))
Are you aware that this will set @strInprocess to an empty string if there is no space in @strinput?
 

Zedster

Registered User.
Local time
Today, 21:50
Joined
Jul 2, 2019
Messages
168
Focus on this part:
Code:
SET @strInprocess = rtrim(LEFT(@strinput,CHARINDEX(' ',@strinput)))
Are you aware that this will set @strInprocess to an empty string if there is no space in @strinput?

Thanks, I had narrowed it down to that line myself by debugging the hard way (replacing variables with fixed values one at a time). No I hadn't realised that if there were no space it would return an empty string. 99% of the strings will have a space and the ones I have tested have a space but I will have to correct for the 1%. Thanks for spotting that.

But I don't know what is wrong with the line. If I take the line out of the function and just enter in the query window like below, it works fine returning 'Hello':

Code:
SELECT rtrim(LEFT('Hello Moto',CHARINDEX(' ','Hello Moto')))

Lack of a debugger is a pain.
 

sonic8

AWF VIP
Local time
Today, 22:50
Joined
Oct 27, 2015
Messages
998
But I don't know what is wrong with the line. If I take the line out of the function and just enter in the query window like below, it works fine returning 'Hello':

Code:
SELECT rtrim(LEFT('Hello Moto',CHARINDEX(' ','Hello Moto')))
Lack of a debugger is a pain.
It is! - That was a stupid decision by Microsoft.

With the input of 'Hello Moto', your function should work if there is an BL_ID 'Hello' in tblBudgetlines. If you just input 'Hello' it will search for ''.
 

Zedster

Registered User.
Local time
Today, 21:50
Joined
Jul 2, 2019
Messages
168
With the input of 'Hello Moto', your function should work if there is an BL_ID 'Hello' in tblBudgetlines. If you just input 'Hello' it will search for ''.

You are right, it now works, thanks. I have modified it to catch for no spaces:

Code:
ALTER FUNCTION [dbo].[GetBudgetCode] 
(
	@strInput as varchar(1000)
)
RETURNS varchar(max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @strOutput as varchar(1000), @strInprocess as varchar(1000), @intSpacePosition as integer

	-- Add the T-SQL statements to compute the return value here
	SET @intSpacePosition = CHARINDEX(' ',@strinput)
	SET @strOutput = ''

	IF @intSpacePosition = 0
		BEGIN
			SET @strInprocess = @strInput
		END
	ELSE
		BEGIN
			SET @strInprocess = rtrim(LEFT(@strinput,@intSpacePosition))
		END

	IF EXISTS (SELECT BL_ID FROM dbo.tblBudgetlines WHERE BL_ID = @strInprocess) AND LEN(@strInprocess) > 16
		BEGIN
			SET @strOutput = @strInprocess
		END

	-- Return the result of the function
	RETURN @strOutput
 
Last edited:

Zedster

Registered User.
Local time
Today, 21:50
Joined
Jul 2, 2019
Messages
168
Now it's not working again and the code hasn't changed, driving me mad!
 

DrallocD

Registered User.
Local time
Today, 17:50
Joined
Jul 16, 2012
Messages
112
Why did you include "AND LEN(@strInprocess) > 16" ? I looks like this will fail if the length is less than 16 chars.
 

Zedster

Registered User.
Local time
Today, 21:50
Joined
Jul 2, 2019
Messages
168
Why did you include "AND LEN(@strInprocess) > 16" ? I looks like this will fail if the length is less than 16 chars.

It has been a while now since I wrote this and I can't remember my reason. TBH it looks redundant because BL_ID will always be over 16 characters anyway. But the function now works so I will leave well alone.
 

Users who are viewing this thread

Top Bottom