May 26, 2016 at 11:47 am
hi Guys -
I am trying to create one function with multiple sub queries based on the param value passed in. If i try to use the IF statement I get a scalar variable Declare issue, If I use the case select it seems to fail on my second WHEN statement?
Any ideas on how to effectively do this?
ALTER FUNCTION [dbo].[myFunction](
@Usage_Date SmallDAteTime,
@RateCode Varchar(4)
)
RETURNS TABLE AS
RETURN
SELECT CASE
WHEN (@RateCode = 'O07') THEN
(
select Rate=case
May 26, 2016 at 12:01 pm
fergfamster (5/26/2016)
hi Guys -I am trying to create one function with multiple sub queries based on the param value passed in. If i try to use the IF statement I get a scalar variable Declare issue, If I use the case select it seems to fail on my second WHEN statement?
Any ideas on how to effectively do this?
ALTER FUNCTION [dbo].[myFunction](
@Usage_Date SmallDAteTime,
@RateCode Varchar(4)
)
RETURNS TABLE AS
RETURN
SELECT CASE
WHEN (@RateCode = 'O07') THEN
(
select Rate=case
CASE is an expression. It is used to control the return value of a single column. It is NOT used to control flow. Perhaps if you can provide us some more details we can help you figure out how to do whatever it is you are trying to do. At the very least some kind of explanation would help. DDL and sample data would be a big plus but not totally sure it is needed here.
_______________________________________________________________
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 12:24 pm
If the table structures to be returned are the same for all conditions, you can use UNION ALL with the appropriate WHERE conditions so that only one query ever returns results:
...
RETURN (
SELECT ...
FROM ...
WHERE @RateCode = '007'
UNIO N AL L
SELECT ...
FROM ...
WHERE @RateCode = '008'
UNIO N AL L
SELECT ...
FROM ...
WHERE @RateCode = '009'
...
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 26, 2016 at 12:59 pm
ScottPletcher (5/26/2016)
If the table structures to be returned are the same for all conditions, you can use UNION ALL with the appropriate WHERE conditions so that only one query ever returns results:
...
RETURN (
SELECT ...
FROM ...
WHERE @RateCode = '007'
UNIO N AL L
SELECT ...
FROM ...
WHERE @RateCode = '008'
UNIO N AL L
SELECT ...
FROM ...
WHERE @RateCode = '009'
...
)
Does this mean all queries are from different tables?
May 26, 2016 at 1:15 pm
djj (5/26/2016)
ScottPletcher (5/26/2016)
If the table structures to be returned are the same for all conditions, you can use UNION ALL with the appropriate WHERE conditions so that only one query ever returns results:
...
RETURN (
SELECT ...
FROM ...
WHERE @RateCode = '007'
UNIO N AL L
SELECT ...
FROM ...
WHERE @RateCode = '008'
UNIO N AL L
SELECT ...
FROM ...
WHERE @RateCode = '009'
...
)
Does this mean all queries are from different tables?
Yes, they definitely can be. As long as the result sets are all compatible, the queries can be from different tables, have different joins, etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 26, 2016 at 1:45 pm
Each expression used is for a reason to call a different query under each. "If statements" require declaring a variable and that errors, so was trying to find a work around. The variable would fail on declaring a Table Scalar Value after the Returns statement
IE:
IF @RateCode = ''
Query
ELSE IF
Query
Else
May 26, 2016 at 1:50 pm
Sorry Scott, I thought the OP had left some code. :hehe:
I really need to read more carefully
May 26, 2016 at 2:00 pm
fergfamster (5/26/2016)
Each expression used is for a reason to call a different query under each. "If statements" require declaring a variable and that errors, so was trying to find a work around. The variable would fail on declaring a Table Scalar Value after the Returns statementIE:
IF @RateCode = ''
Query
ELSE IF
Query
Else
Without you showing us what you are trying to accomplish there is very little chance that any shots in the dark are going to help you.
May 26, 2016 at 2:09 pm
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 ...
)
May 26, 2016 at 2:23 pm
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 ...
)
Everything you have given so far is incomplete. No idea what you have and are trying to accomplish by giving us snippets of your code. Sorry.
May 26, 2016 at 2:27 pm
Ok, well i cant give you all my code. If you wanted to create an Inline function and have separate queries returned based on an IF statement is the bottom line or a case statement etc.. these are not union all type queries. Its ok, ill figure it out.
May 26, 2016 at 2:29 pm
fergfamster (5/26/2016)
Ok, well i cant give you all my code. If you wanted to create an Inline function and have separate queries returned based on an IF statement is the bottom line or a case statement etc.. these are not union all type queries. Its ok, ill figure it out.
Then create something unique that mirrors your current problem, just be sure it mirrors it completely and you don't leave out something important.
May 26, 2016 at 2:34 pm
fergfamster (5/26/2016)
Ok, well i cant give you all my code. If you wanted to create an Inline function and have separate queries returned based on an IF statement is the bottom line or a case statement etc.. these are not union all type queries. Its ok, ill figure it out.
As I said originally....case is an expression. It is NOT used to control flow like an IF statement. You have to keep in mind that we can't see your screen, we have no idea what your tables are like, we have no idea what your project is. The only details we have are what you have posted and at this point all you have posted is portions of a query and an incredibly vague description of what you want it to do. There are least 4 different people in here trying to help you but for that to succeed you have to help us by providing the details of your problem. Without those details we are shooting blindly. It does not have to be your actual query or even anything revealing the true nature of the problem. But it does have to have the same level of detail.
_______________________________________________________________
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:34 pm
fergfamster (5/26/2016)
Ok, well i cant give you all my code. If you wanted to create an Inline function and have separate queries returned based on an IF statement is the bottom line or a case statement etc.. these are not union all type queries. Its ok, ill figure it out.
You don't have to give all your code. Not even real column or table names. But we would expect you to at least post an accurate mock up and use FROM in your SELECT statements, as well as number of columns.
I saw this before anyone answered, but you didn't provide anything that allows us to give you an adequate solution.
May 26, 2016 at 2:38 pm
From what you describe, this sounds like a very poor usage of an inline function.
Is this what you are doing:
SELECT
field,
field,
inline_function
FROM Table
My suggestion is to re-think this.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply