Multi statement function error

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)

Multi statement function error

Author

Topic

Timax
Starting Member

37 Posts

Posted - 2015-05-02 : 17:05:42

Hi, I have this function but it returns an error:
Select statements included within a function cannot return data to a client.
What am I doing wrong?

CREATE FUNCTION StockRoomPNSort
(
-- Add the parameters for the function here
@CUSparam INT
)
RETURNS
@t TABLE
(
-- Add the column definitions for the TABLE variable here
PN# VARCHAR(30),
[Part Number] VARCHAR(30),
[Description] VARCHAR(50),
Package VARCHAR(20),
ManufName VARCHAR(30),
[Manufacturer PN] VARCHAR(30),
[Manuf Desc] VARCHAR(30)
)
AS
BEGIN
--DECLARE @MySQL VARCHAR(Max)
--SET @MySQL = "
SELECT dbo.ABC_PN.PN#, dbo.ABC_PN.[Part Number], dbo.ABC_PN.[Description], dbo.Packages.Package, dbo.Manufacturer.ManufName,
dbo.ABC_PN.[Manufacturer PN], dbo.ABC_PN.[Manuf Desc]
FROM dbo.ABC_PN LEFT OUTER JOIN
dbo.Packages ON dbo.ABC_PN.Package = dbo.Packages.PGID LEFT OUTER JOIN
dbo.Manufacturer ON dbo.ABC_PN.Manufacturer = dbo.Manufacturer.ManufID
WHERE dbo.ABC_PN.Customer = @CUSparam
ORDER BY dbo.ABC_PN.[Part Number]

RETURN
END
GO

My second question about this function is that the whole idea of this function is to create IF statement for WHERE clause and depending of the condition (@CUSparam value) WHERE ether exists or not. For example IF @CUSparam = 113 then WHERE clause exist ELSE it doesn't exist. Can I do this thru Defining local variable, assigning SQL statement to that variable, manipulate it and then return value? Or how?

Kristen
Test

22859 Posts

Posted - 2015-05-03 : 03:27:43

[code]
SELECT dbo.ABC_PN.PN#, ...
FROM dbo.ABC_PN ...

RETURN
[/code]
needs to be
[code]
RETURN
SELECT dbo.ABC_PN.PN#, ...
FROM dbo.ABC_PN ...
[/code]
or (if cannot build the functionas a single statement)
[code]
INSERT INTO @t
(
PN#, [Part Number], ...
)
SELECT dbo.ABC_PN.PN#, ...
FROM dbo.ABC_PN ...
RETURN
[/code]

The most common way that we have "optional" parameters is to pass the parameter as NULL if it is not to be used and then do:
[code]
WHERE (@CUSparam IS NULL OR dbo.ABC_PN.Customer = @CUSparam)
[/code]

Personally I wouldn't use a FUNCTION for this. Performance tends not to be very good. But there might be other reasons for using a function ...

If your WHERE clause is more complicated than just "Optional" parameters then you could produce the SQL for the SELECT "dynamically" - so the WHERE clause is built as a String containing only the tests on columns that are needed. This can make a dramatic improvement to performance (if done right!!) but there are other pitfalls which you would need to be aware of, such as security and SQL Injection. Let us know what you are trying to do, overall, and folk here can advise if there is a "better way"

Timax
Starting Member

37 Posts

Posted - 2015-05-04 : 07:22:49

Kristen,
Works like a charm! Thank you so much. I used it in many functions tonight and all perfect. Now, one more question about conditional WHERE... I am trying to do function with multi statement conditional WHERE:

ALTER FUNCTION [dbo].[StockRoomSearch]
(
-- Add the parameters for the function here
@CUSparam AS INT,
@TXTparam AS VARCHAR(max),
@FLDparam AS INT
)
RETURNS
@t TABLE
(
-- Add the column definitions for the TABLE variable here
PN# VARCHAR(50),
[Part Number] VARCHAR(max),
[Description] VARCHAR(max),
Package VARCHAR(max),
ManufName VARCHAR(max),
[Manufacturer PN] VARCHAR(max),
[Manuf Desc] VARCHAR(max)
)
AS
BEGIN

INSERT @t
SELECT dbo.ABC_PN.PN#, dbo.ABC_PN.[Part Number], dbo.ABC_PN.[Description], dbo.Packages.Package, dbo.Manufacturer.ManufName,
dbo.ABC_PN.[Manufacturer PN], dbo.ABC_PN.[Manuf Desc]
FROM dbo.ABC_PN LEFT OUTER JOIN
dbo.Packages ON dbo.ABC_PN.Package = dbo.Packages.PGID LEFT OUTER JOIN
dbo.Manufacturer ON dbo.ABC_PN.Manufacturer = dbo.Manufacturer.ManufID
WHERE (@CUSparam IS NULL OR dbo.ABC_PN.Customer = @CUSparam) AND
CASE WHEN @FLDparam = 1 THEN dbo.ABC_PN.[Description] Like '%' + @TXTparam + '%' END,
CASE WHEN @FLDparam = 2 THEN (dbo.ABC_PN.[Manufacturer PN] LIKE '%' + @TXTparam + '%') END,
CASE WHEN @FLDparam = 3 THEN (dbo.ABC_PN.[Manuf Desc] LIKE '%' + @TXTparam + '%') END

ORDER BY dbo.ABC_PN.[Part Number]

--WHERE (@CUSparam IS NULL OR dbo.ABC_PN.Customer = @CUSparam)

RETURN

END

... and it doesn't like it... How can I rewrite this WHERE clause so it will work? Please help :)

Kristen
Test

22859 Posts

Posted - 2015-05-04 : 14:23:13

[code]
WHERE (@CUSparam IS NULL OR dbo.ABC_PN.Customer = @CUSparam)
AND (
(@FLDparam = 1 AND dbo.ABC_PN.[Description] Like '%' + @TXTparam + '%')
OR (@FLDparam = 2 AND dbo.ABC_PN.[Manufacturer PN] LIKE '%' + @TXTparam + '%')
OR ...
)
[/code]
but performance will be terrible. Won't matter if you only have a few rows in the table, but for large tables it will be slow.

Timax
Starting Member

37 Posts

Posted - 2015-05-06 : 06:54:51

Thank you very much! All works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources