July 26, 2013 at 10:57 am
i need to create a function passing those two varaibales. How do i do that? Table valued function
select DISTINCT bb.level , 'Myvalue5' as'Table'
From BaseTable bb
Left Join dbo.a on (bb.col1 = a.col1)
UNION ALL
select DISTINCT bb.level , 'Myvalue4' as'Table'
From BaseTable bb
Left Join dbo.a on (bb.col1 = a.col1)
July 26, 2013 at 11:03 am
You don't have any variables in your code.
You can check the CREATE FUNCTION syntax in here http://msdn.microsoft.com/es-es/library/ms186755%28v=sql.90%29.aspx
CREATE FUNCTION dbo.Example_function_name
(
@Parameter1 int
)
RETURNS TABLE
AS
RETURN
SELECT bb.level , 'Myvalue5' as'Table'
From BaseTable bb
Left Join dbo.a on (bb.col1 = a.col1)
UNION
SELECT bb.level , 'Myvalue4' as'Table'
From BaseTable bb
Left Join dbo.a on (bb.col1 = a.col1) ;
July 26, 2013 at 12:18 pm
I wanted to make level andy Myvalue as a paramter. Can that be done?
July 26, 2013 at 12:30 pm
SQL_Surfer (7/26/2013)
I wanted to make level andy Myvalue as a paramter. Can that be done?
Not exactly sure what "level andy Myvalue" is but you can add parameters to your function, just put in a comma and another parameter.
_______________________________________________________________
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/
July 26, 2013 at 12:32 pm
level is column but myvalue is just a label (but changes) on each union.
July 26, 2013 at 12:34 pm
Total shot in the dark because there is nothing to work and the description is very vague but maybe you want something like this?
CREATE FUNCTION dbo.Example_function_name
(
@Level int,
@MyValue varchar(10)
)
RETURNS TABLE
AS
RETURN
select DISTINCT bb.level , SomeOtherColumn as 'Table'
From BaseTable bb
Left Join dbo.a on (bb.col1 = a.col1)
where bb.level = @Level
and SomeOtherColumn = @MyValue
UNION ALL
select DISTINCT bb.level , SomeOtherColumn as'Table'
From BaseTable bb
Left Join dbo.a on (bb.col1 = a.col1)
where bb.level = @Level
and SomeOtherColumn = @MyValue
_______________________________________________________________
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/
July 26, 2013 at 3:36 pm
How can i write a recursive function for this?
select col1, col2, col3
From BaseTable
Left Join dbo.Table6 Table6 on (BaseTable.z = Table6.ID6)
Left Join dbo.Table5 Table5 on (BaseTable.z = Table5.ID5)
Left Join dbo.Table4 Table4 on (Table5.ID4 = Table4.ID4)
Left Join dbo.Table3 Table3 on (Table4.ID3 = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where ID6 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'')
+ '/' + ISNULL(Table4.MyCol,'')+ '/' + ISNULL(Table5.MyCol,'') IS NOT NULL + '/' + ISNULL(Table6.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table5 Table5 on (BaseTable.z = Table5.ID5)
Left Join dbo.Table4 Table4 on (Table5.ID4 = Table4.ID4)
Left Join dbo.Table3 Table3 on (Table4.ID3 = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where ID5 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'')
+ '/' + ISNULL(Table4.MyCol,'')+ '/' + ISNULL(Table5.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table4 Table4 on (BaseTable.z = Table4.ID4)
Left Join dbo.Table3 Table3 on (Table4.ID3 = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where Table4.ID4 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table3 Table3 on (BaseTable.z = Table3.ID3)
Left Join dbo.Table2 Table2 on (Table3.ID2 = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where Table3.ID3 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') + '/' + ISNULL(Table3.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.Table2 Table2 on (BaseTable.z = Table2.ID2)
Left Join dbo.TableZ TableZ on (Table2.ID1 = TableZ.ID1)
where Table2.ID2 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' + ISNULL(Table2.MyCol,'') IS NOT NULL
UNION ALL
select col1, col2, col3
From BaseTable
Left Join dbo.TableZ TableZ on (BaseTable.z = TableZ.ID1)
where TableZ.ID1 IS NOT NULL AND ISNULL(TableZ.MyCol,'') + '/' IS NOT NULL
July 27, 2013 at 7:11 am
Is recursive CTE possible to rewrite this? If yes, how?
July 28, 2013 at 5:27 am
It's not really possible to tell if a rCTE would return your resultset more efficiently. Can you post the actual query rather than pseudocode? There is a far more efficient way of writing this query with your results appearing columnwise instead of rowwise, but it would depend upon the SELECT list.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 28, 2013 at 1:13 pm
Can you give me some pointers of rewriting this query? Can data be grabbed at 1 shot instead of doing UNION ALL.
July 28, 2013 at 1:46 pm
SQL_Surfer (7/28/2013)
Can you give me some pointers of rewriting this query? Can data be grabbed at 1 shot instead of doing UNION ALL.
I think so. Post the whole query and we'll see.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 28, 2013 at 4:02 pm
Sorry, I cannot post the actual code for security reasons. I have figured out a way to write a function that returns the value that I am looking for which works perfect. Scalar function was taking really long about 20s; then I converted to MultiLine table valued function. But this is also taking about 8s to return result which is about 1400 rows. Is there anyway to convert this to Inline TVF? I am hopeing Inline TVF would give some perfromance gain. Or any other suggestion to gain performance?
CREATE FUNCTION [dbo].[getInfo]
(
@a varchar(100)
)
RETURNS
@info TABLE
(
ReturnValue varchar(max)
)
AS
BEGIN
DECLARE @Var1 varchar(100), @Var2 varchar(100), @Var3 varchar(100), @Var4 varchar(100), @Var5 varchar(100)
DECLARE @ReturnVal1 varchar(100), @ReturnVal2 varchar(100), @ReturnVal3 varchar(100), @ReturnVal3 varchar(100), @ReturnVal5 varchar(100)
DECLARE @Result varchar(max)
SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a
SELECT@ReturnVal5 = RetVal, @Var4 = Val4 FROM Table5 WHERE Val5 = @Var5
SELECT @ReturnVal3 = RetVal, @Var3 = Val3 FROM Table4 WHERE Val4 = ISNULL(@Var4,@a)
SELECT @ReturnVal3 = RetVal, @Var2 = Val2 FROM Table3 WHERE Val3 = ISNULL(@Var3,@a)
SELECT @ReturnVal2 = RetVal, @Var1 = Val1 FROM Table2 WHERE Val2 = ISNULL(@Var2,@a)
SELECT @ReturnVal1 = RetVal FROM Table1 WHERE Val1 = ISNULL(@Var1,@a)
SET @Result = @ReturnVal1 + @ReturnVal2 + @ReturnVal3 + @ReturnVal3 + @ReturnVal5
INSERT INTO @info VALUES (@Result)
RETURN
END
July 29, 2013 at 2:09 am
SQL_Surfer (7/28/2013)
Sorry, I cannot post the actual code for security reasons....
Then obfuscate column and table names! Your pseudocode is so full of errors that making sense of it is more effort than writing the query.
Here's a best guess based upon the random ramblings you've posted so far:
SELECT Result = ReturnVal1 + ReturnVal2 + ReturnVal3 + ReturnVal4 + ReturnVal5
FROM Mytable m
OUTER APPLY (
SELECT ReturnVal5 = RetVal, Val4 FROM Table5 WHERE Val5 = m.mycol
) t5
OUTER APPLY (
SELECT ReturnVal4 = RetVal, Val3 FROM Table4 WHERE Val4 = ISNULL(t5.Val4,m.mycol)
) t4
OUTER APPLY (
SELECT ReturnVal3 = RetVal, Val2 FROM Table3 WHERE Val3 = ISNULL(t4.Val3,m.mycol)
) t3
OUTER APPLY (
SELECT ReturnVal2 = RetVal, Val1 FROM Table2 WHERE Val2 = ISNULL(t3.Val2,m.mycol)
) t2
OUTER APPLY (
SELECT ReturnVal1 = RetVal FROM Table1 WHERE Val1 = ISNULL(t2.Val1,m.mycol)
) t1
WHERE m.mycol = @a
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 1:04 am
SQL_Surfer (7/29/2013)
Where are you doing SELECT @Var5 = max(mycol) FROM Mytable WHERE mycol = @a ? I need to get max on that column from Mytable.
You can't get MAX(mycol) FROM Mytable when you have the filter mycol = @a.
MAX(mycol) can only be equal to @a, so @Var5 = @a and Val5 = m.mycol.
Unless of course your intention is to aggregate Mytable to one row. If it is, then adjust my query for this.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply