Best way to construct function with multiple field

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

SQL Server 2008 Forums

Transact-SQL (2008)

Best way to construct function with multiple field

Author

Topic

Timax
Starting Member

37 Posts

Posted - 2015-04-02 : 21:09:01

Hello! I am moving custom function from msaccess to mssql which need to return multiple fields from select statement like SELECT Field1. Field2 and so on. How do I handle that? Do I need Table valued function for that? Create @table and record things there to have multiple fields from Select statement? Thank you in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-02 : 22:29:47

that would be a table valued function

Timax
Starting Member

37 Posts

Posted - 2015-04-03 : 09:01:42

Do I need to drop table after creation? Any examples perhaps how I can do that?

Timax
Starting Member

37 Posts

Posted - 2015-04-03 : 09:16:30

This is my query that I am trying to get multiple field results from. What will be the best way to get results from it?
"SELECT CompStatusFunctionQ.[#], CompStatusFunctionQ.ToBuy, CompStatusFunctionQ.Quoted, CompStatusFunctionQ.TotalBought, CompStatusFunctionQ.TotalReceived, CompStatusFunctionQ.TotalRejected FROM CompStatusFunctionQ WHERE (((CompStatusFunctionQ.[#])= " & Trans & "));"

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2015-04-03 : 10:37:47

What database server are you using?

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-03 : 10:49:23

For SQL Server, something like this:


CREATE FUNCTION [your_function_name_goes_here] (
    @Trans int
)
RETURNS TABLE
AS
RETURN (
    SELECT csfq.[#], csfq.ToBuy, csfq.Quoted, 
        csfq.TotalBought, csfq.TotalReceived, csfq.TotalRejected 
    FROM CompStatusFunctionQ csfq 
    WHERE csfq.[#] = @Trans
)
GO

SELECT mt.*, fn.*
FROM main_table mt
CROSS APPLY dbo.[your_function_name_goes_here](mt.Trans) AS fn

Timax
Starting Member

37 Posts

Posted - 2015-04-04 : 01:56:23

Thank you! I am using SQL Server 2008. All clear with first part of the code but not sure what second part do:
SELECT mt.*, fn.*
FROM main_table mt
CROSS APPLY dbo.[your_function_name_goes_here](mt.Trans) AS fn

Also, How can I set local variables to each table field after this code for farther calculations?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-04 : 08:33:03

Read up on cross apply.

Not sure what you mean by thec second question
Post some examples

Timax
Starting Member

37 Posts

Posted - 2015-04-05 : 07:20:24

ok, here is what I constructed:

ALTER FUNCTION [dbo].[CompStatusFunction] (
@Trans int
)
RETURNS TABLE
AS
RETURN (
SELECT CompStatusFunctionQ.[#], CompStatusFunctionQ.ToBuy, CompStatusFunctionQ.Quoted,
CompStatusFunctionQ.TotalBought, CompStatusFunctionQ.TotalReceived, CompStatusFunctionQ.TotalRejected
FROM CompStatusFunctionQ
WHERE CompStatusFunctionQ.[#] = @Trans
)

DECLARE @ToBuy INT, @Quoted INT, @Bought INT, @Received INT, @Rejected INT, @Status INT
SELECT @ToBuy = ToBuy, @Quoted = Quoted, @Bought = TotalBought, @Received = TotalReceived, @Rejected = TotalRejected FROM dbo.CompStatusFunction(@Trans)

If @Rejected = 0 GoTo JumpReceived;
Else If @Rejected >= @ToBuy
BEGIN
SET @Status = 9 -- Rejected
GoTo Br;
END

JumpReceived:

If @Received = 0
GoTo JumpBought
Else If @Received >= @ToBuy
BEGIN
SET @Status = 7 --Received
GoTo Br
END
Else If @Received < @ToBuy
SET @Status = 8 --Partially Received
GoTo Br

JumpBought:

If @Bought = 0
GoTo JumpQuoted
Else If @Bought >= @ToBuy
BEGIN
SET @Status = 5 --On Order
GoTo Br
END
Else If @Bought < @ToBuy
SET @Status = 6 --Partially Ordered
GoTo Br

JumpQuoted:

If @Quoted = 0
BEGIN
SET @Status = 1 --Not Quoted
GoTo Br
END
Else If @Quoted >= @ToBuy
BEGIN
SET @Status = 2 --Quoted
GoTo Br
END
Else If @Quoted < @ToBuy
SET @Status = 3 --Partially Quoted
GoTo Br

Br:
RETURN @Status
GO

This have 2 problems that I can't figure out...:

Msg 156, Level 15, State 1, Procedure CompStatusFunction, Line 19
Incorrect syntax near the keyword 'DECLARE'.
Msg 178, Level 15, State 1, Procedure CompStatusFunction, Line 72
A RETURN statement with a return value cannot be used in this context.

How can I get single result @Status from all of this? What am I doing wrong here?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-05 : 08:09:22

Well in the function definition you say that it returns a table. Then you try to return a scalar.

Timax
Starting Member

37 Posts

Posted - 2015-04-05 : 08:59:46

This is my problem... I need to return scalar but them I need to create table I guess to get all different fields to calculate... What to do?

Timax
Starting Member

37 Posts

Posted - 2015-04-05 : 09:06:19

As you see, idea of this function is to create table with multiple fields, calculate status based on values in these fields and return one result (integer). Is this possible?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-05 : 13:40:59

Wait it looks like you have two functions here:

1. CompStatusFunction which returns a table
2. a second one where you want to return @Status

if you want it all in one, use a multi-statement function. Then you can do what you want.

However, I should note that procedural logic such as yours is quite un-SQL-ly. You should be able to do the same thing as all those ifs and gotos with a single query containing a CASE statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-05 : 14:35:47

You probably just need a CASE statement, like this. You will have to check to order of the CASE because they matter.

ALTER FUNCTION dbo.CompStatusFunction
(
	@Trans INT
)
RETURNS TABLE
AS
RETURN	(
		SELECT	[#],
			ToBuy,
			Quoted, 
			TotalBought,
			TotalReceived,
			TotalRejected,
			CASE
				WHEN TotalBought >= ToBuy THEN 5	-- On order
				WHEN TotalBought < ToBuy THEN 6		-- Partially ordered
				WHEN Quoted = 0 THEN 1			-- Not quoted
				WHEN Quoted >= ToBuy THEN 2		-- Quoted
				WHEN Quoted < ToBuy THEN 3		-- Partially quoted
				WHEN TotalReceived >= ToBuy THEN 7	-- Received
				WHEN TotalReceived < ToBuy THEN 8	-- Partially Received
				WHEN TotalReceived >= ToBuy THEN 9	-- Rejected
			END AS [Status]
		FROM	dbo.CompStatusFunctionQ 
		WHERE	[#] = @Trans
	);
GO

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Timax
Starting Member

37 Posts

Posted - 2015-04-05 : 20:26:25

Thank you. This code looks much cleaner and makes more sense :) Only question I guess this function will return all 7 fields? How can I narrow it down so function only returns field Status? Can I do SELECT [Status] FROM CompStatusFunction(@Trans) after this in the same function so it will return only Status? Looks like @Trans is not recognize after I close block with GO. Anyway to pass this @Trans from one block to another one as a Global variable perhaps?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-06 : 03:29:00

Yes, you can select only a few of the columns from the function, just like any other data set.
Don't forget that the [#] columns IS the @Trans value.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Timax
Starting Member

37 Posts

Posted - 2015-04-06 : 06:48:11

I did SELECT [Status] FROM CompStatusFunction(@Trans) after that on the same function but it don't recognize @TRans after GO statement. How can I pass this value to Select statement? I want this function to return only Status field a the end so I can use this CompStatusFunction([#]) in Views where [#] is Transaction number.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-06 : 09:45:11

You have to start thinking in terms of set-based processing. That is, instead of reading all values into variables and processing them with separate lines of code loops, instead leave the values in tables and process all rows at the same time, with SELECT statements. The only time you could drop down to using loops to process is when that was the only way possible to do what you needed to do.

Timax
Starting Member

37 Posts

Posted - 2015-04-06 : 16:59:50

Thank you Scott. That's what I am trying to do with Select statement after table creation but can't pass @Trans parameter to Select statement after Go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-06 : 17:08:33

quote:
Originally posted by Timax

Thank you Scott. That's what I am trying to do with Select statement after table creation but can't pass @Trans parameter to Select statement after Go

Show us exactly the code that is throwing the error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Timax
Starting Member

37 Posts

Posted - 2015-04-07 : 02:17:29

ALTER FUNCTION [dbo].[CompStatusFunction]
(
@Trans INT
)
RETURNS TABLE
AS
RETURN (
SELECT [#],
ToBuy,
Quoted,
TotalBought,
TotalReceived,
TotalRejected,
CASE
WHEN TotalBought >= ToBuy THEN 5 -- On order
WHEN TotalBought < ToBuy THEN 6 -- Partially ordered
WHEN Quoted = 0 THEN 1 -- Not quoted
WHEN Quoted >= ToBuy THEN 2 -- Quoted
WHEN Quoted < ToBuy THEN 3 -- Partially quoted
WHEN TotalReceived >= ToBuy THEN 7 -- Received
WHEN TotalReceived < ToBuy THEN 8 -- Partially Received
WHEN TotalReceived >= ToBuy THEN 9 -- Rejected
END AS [Status]
FROM dbo.CompStatusFunctionQ
WHERE [#] = @Trans
);
GO
SELECT [Status] FROM CompStatusFunction(@Trans)

and error is:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Trans"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources