Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-04-2019, 10:56 PM   #1
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
scalar function not working and can't debug

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.

Zedster is offline   Reply With Quote
Old 09-05-2019, 12:11 AM   #2
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 269
Thanks: 44
Thanked 76 Times in 73 Posts
sonic8 is on a distinguished road
Re: scalar function not working and can't debug

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?
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
Zedster (09-05-2019)
Old 09-05-2019, 12:25 AM   #3
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
Re: scalar function not working and can't debug

Quote:
Originally Posted by sonic8 View Post
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.

Zedster is offline   Reply With Quote
Old 09-05-2019, 12:39 AM   #4
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 269
Thanks: 44
Thanked 76 Times in 73 Posts
sonic8 is on a distinguished road
Re: scalar function not working and can't debug

Quote:
Originally Posted by Zedster View Post
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 ''.
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
Zedster (09-05-2019)
Old 09-05-2019, 12:52 AM   #5
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
Re: scalar function not working and can't debug

Quote:
Originally Posted by sonic8 View Post
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 by Zedster; 09-05-2019 at 01:04 AM.
Zedster is offline   Reply With Quote
Old 09-05-2019, 04:15 AM   #6
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
Re: scalar function not working and can't debug

Now it's not working again and the code hasn't changed, driving me mad!
Zedster is offline   Reply With Quote
Old 09-24-2019, 04:41 PM   #7
DrallocD
Newly Registered User
 
Join Date: Jul 2012
Location: Stamford, CT
Posts: 112
Thanks: 1
Thanked 19 Times in 19 Posts
DrallocD is on a distinguished road
Re: scalar function not working and can't debug

Why did you include "AND LEN(@strInprocess) > 16" ? I looks like this will fail if the length is less than 16 chars.

DrallocD is offline   Reply With Quote
Old 09-24-2019, 10:36 PM   #8
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 47
Thanks: 22
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
Re: scalar function not working and can't debug

Quote:
Originally Posted by DrallocD View Post
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.

Zedster is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Scalar Function called in a View - Is it OK for under 20,000 records? Rx_ SQL Server 0 11-04-2014 12:03 PM
Access 2007 debug stopped working on db Freewilly3d Modules & VBA 2 06-27-2012 09:14 AM
NZ function not working AnnPhil Forms 2 06-23-2010 08:20 PM
Must declare the scalar variable ice_breaker SQL Server 0 02-19-2010 08:45 AM
Public Function Debug dba2p Modules & VBA 0 03-26-2004 12:03 PM




All times are GMT -8. The time now is 01:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World