UDF does not 'reset' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UDF does not 'reset'

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-10 : 15:55:45

My UDF

FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1))
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week AND firm_or_commission = @type
GROUP BY quantity
ORDER BY quantity

RETURN @Output

END

gives the same output every time it is called, despite different parameters passed. @Output does not change as expected.

Any ideas guys?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-10 : 15:57:46

sample data? sample results expected? actual results expected? sample paramaters?

That would help.

Poor planning on your part does not constitute an emergency on my part.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-10 : 16:24:43

Oops! Here we go:

Data:

item supplier boxes quantity retail firm_or_commission week total

AL 1224 10 24 287 C 35 240
CH OZ 12 12 296 F 35 144
BAK OZ 2 12 364 F 35 24
CH 8433 6 24 290 F 35 144
CH 5541 10 36 290 F 35 360

SQL query calling the function:

SELECT retail, supplier, count = dbo.fn_concat_boxes('CH', 35, 'F'), total
FROM flexing_stock_transactions
WHERE (item = 'CH' AND week = 35 AND firm_or_commission = 'F')
GROUP BY retail, supplier, total
ORDER BY retail DESC

result given:

296 12 432
290 12 144

result expected:

296 12 432
290 24/36 504

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 16:28:06

How can you have 4 columns in the select-list, but only 3 in the expected output?

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-10 : 16:57:01

it should read:

SELECT retail, count = dbo.fn_concat_boxes('CH', 35, 'F'), total

I pasted a previous attempt, with different output formatting in error. My apologies.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 17:09:16

This is also a known bug. Which edition and version of SQL Server do you use?
One quick workaround is to change the UDF.
If no records are found, the function should return NULL and (for some reason) SQL Server returns last used value for function.

FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1))
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(101)

set @Output = ''

SELECT @Output = '/' + @Output + 
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week AND firm_or_commission = @type
GROUP BY quantity
ORDER BY quantity

RETURN substring(@Output, 2, 100)

END

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-11 : 14:21:27

Many thanks for the reply. I was unaware of this bug.

Would I be better served by using a stored procedure? If so, could someone please give an example, as stored procedures are new to me.

I'm using SQL server 2005 version.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 14:23:39

Use this in SQL Server 2005
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-11 : 14:42:56

Thanks I'll try and figure something out.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-11 : 16:33:38

I don't get it ... what is the bug? based on the sample code given, the parameters are constants, and never change, so of course the UDF always returns the same results.

Am I missing something?

UPDATE: And also, even if column names instead of constants are passed in to the function, they are limited by the WHERE clause to always be a constant value anyway, so again, the UDF will always return the same value for all rows in the table ... not because of bugs, but because that is what should be expected.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 16:41:01

The bug was SQL Server. Before SP x (I don't remember right now) under some circumstances an UDF could return exakt same value for all rows much like GETDATE() do.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-11 : 16:43:29

I don't see any bugs here, that is for sure.

OldMySQLUser -- It would help if you would explain what you are trying to do here, and what these results should be in plain english. Otherwise, all we can do is guess.

- Jeff
http://weblogs.sqlteam.com/JeffS

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-12 : 05:09:57

Sure Jeff.

Different suppliers delivery the same goods in boxes containing different multiples. My sql query needs to ascertain the total stock levels for the same products which have been booked in at the same retail price (price can vary for the same product due to the grade delivered). The concantenation (function) portion is to inform the inquirer what box multiples are available for the overall total shown at that particular price.

Hope that makes sense.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-12 : 11:30:17

I still do not follow unfortunately .... Do you understand why your UDF is always returning the same values? If you always pass in the same values, you always get back the same values.

- Jeff
http://weblogs.sqlteam.com/JeffS

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-12 : 13:11:21

quote:
Originally posted by jsmith8858

I still do not follow unfortunately ....

That's OK.

After much head-scratching I made some modifications and finally made it!

function:

FUNCTION [dbo].[fn_concat_boxes](@item varchar(10), @week int, @type char(1), @retail int)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)
SET @Output = null

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week AND firm_or_commission = @type AND retail = @retail
GROUP BY retail, quantity
ORDER BY quantity

RETURN @Output

END

sql query:

SELECT retail, count = dbo.fn_concat_boxes('CH', 35, 'F', retail), SUM(total) AS total
FROM flexing_stock_transactions
WHERE (item = 'CH' AND week = 35 AND firm_or_commission = 'F')
GROUP BY retail
ORDER BY retail DESC

MANY thanks to everyone kind enough to reply. I really appreciated the suggestions.

X002548
Not Just a Number

15586 Posts

Posted - 2007-12-12 : 13:59:41

Why do you prefer a function over a sproc?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-12 : 14:09:03

quote:
Originally posted by X002548

Why do you prefer a function over a sproc?

What does that have to do with anything? What good would a stored procedure do here? What's wrong with using a function?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources