May 26, 2016 at 2:42 pm
You can use derived tables within the query to provide computed values, you don't need variables.
Also, here's a quick demo to show that you can query from different tables, as long as the results sets are compatible:
CREATE FUNCTION dbo.test_diff_queries_in_a_function
(
@Usage_Date SmallDAteTime,
@RateCode Varchar(4)
)
RETURNS TABLE AS
RETURN
SELECT t.object_id
FROM sys.tables t
WHERE @RateCode = 'T'
UNIO N AL L --Edit: I can't code this correctly or my work filter rejects it as "sql injection"
SELECT TOP (100) column_id
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.name LIKE '%id%'
AND @RateCode = 'C'
GO
SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'T')
SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'C')
GO
DROP FUNCTION dbo.test_diff_queries_in_a_function
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 26, 2016 at 2:44 pm
Or
create function dbo.SomeFunction(
@parm1 int,
@parm2 varchar(4)
)
returns table as
return
select col1, col2, col3 from dbo.tab1 where @parm2 = '007' and ....
union all
select col1, col2, col3 from dbo.tab2 where @parm2 = '006' and ....
...
As long as col1, col2, and col3 are of the same data types.
May 26, 2016 at 2:46 pm
Guys, after reviewing MSDN, SQL does not allow this type of usage inside a function. I appreciate your help.
May 26, 2016 at 2:46 pm
ScottPletcher (5/26/2016)
You can use derived tables within the query to provide computed values, you don't need variables.Also, here's a quick demo to show that you can query from different tables, as long as the results sets are compatible:
CREATE FUNCTION dbo.test_diff_queries_in_a_function
(
@Usage_Date SmallDAteTime,
@RateCode Varchar(4)
)
RETURNS TABLE AS
RETURN
SELECT t.object_id
FROM sys.tables t
WHERE @RateCode = 'T'
UNIO N AL L --Edit: I can't code this correctly or my work filter rejects it as "sql injection"
SELECT TOP (100) column_id
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.name LIKE '%id%'
AND @RateCode = 'C'
GO
SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'T')
SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'C')
GO
DROP FUNCTION dbo.test_diff_queries_in_a_function
I have that problem with the word D E C L A R E.
May 26, 2016 at 2:47 pm
Thanks Lynn, each query has different columns and widths. otherwise this would have worked,
May 26, 2016 at 2:52 pm
fergfamster (5/26/2016)
Thanks Lynn, each query has different columns and widths. otherwise this would have worked,
You can always use a hard coded NULL as column placeholder when doing UNION ALL.
select name
, column_id
from sys.columns
union all
select name
, NULL --Just need a placeholder here
from sys.tables
You can adjust this type of thing to suit your requirements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 26, 2016 at 2:59 pm
thanks Sean, I should have thought of that.
this works now.
May 27, 2016 at 9:40 am
fergfamster (5/26/2016)
Well I gave the code at the beginning.here is my original with 1 inline query:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MyFunction](
@Usage_Date SmallDAteTime,
@RateCode Varchar(4)
)
RETURNS TABLE AS
RETURN
select, where
group By ....
My New Query Im simply trying to use an IF statement to have 4 different types of queries based on the variable value for rateCode. When trying to use an If statement I get an error like the one below stating Declare Table Scalar Variable. Its declared directly after the function name for the variables passed, however seems to not like being used with if statements or case statements other than just calling the variable name itself in my query directly.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MyFunction](
@Usage_Date SmallDAteTime,
@RateCode Varchar(4)
)
RETURNS TABLE AS
RETURN
IF @RateCode = 'O07'
(
select Rate=case
Group By ...
)
ELSE IF @RateCode = 'O01'
(
select Rate=case
Group By ...
)
You can't do what you're trying to do. The "RETURNS TABLE" version of UDFs is for inline-table functions. They have a very specific syntax and work a very specific way.
You might be able to do it as a multi-statement UDF. In that case, you would Return a table variable. Looks like "RETURNS @TableName TABLE (...table variable definition...)". Create Function documentation on MSDN has the details (https://msdn.microsoft.com/en-us/library/ms186755.aspx). This will only work if the returned table has the same structure (columns, data-types, etc.) and what you want is different queries.
Performance on multi-statement UDFs is almost always much, much worse than inline-table functions. The advantage is they allow the kind of thing you're trying to do.
There are other options, but the particular solution you are trying to do is not something SQL Server (or Oracle/DB2/etc.) supports.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply