Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

create table valued function Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 10:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:27 PM
Points: 327, Visits: 853
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)
Post #1478110
Posted Friday, July 26, 2013 11:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 3,342, Visits: 7,225
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) ;




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1478113
Posted Friday, July 26, 2013 12:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:27 PM
Points: 327, Visits: 853
I wanted to make level andy Myvalue as a paramter. Can that be done?
Post #1478143
Posted Friday, July 26, 2013 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1478147
Posted Friday, July 26, 2013 12:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:27 PM
Points: 327, Visits: 853
level is column but myvalue is just a label (but changes) on each union.
Post #1478149
Posted Friday, July 26, 2013 12:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1478150
Posted Friday, July 26, 2013 3:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:27 PM
Points: 327, Visits: 853
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

Post #1478223
Posted Saturday, July 27, 2013 7:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:27 PM
Points: 327, Visits: 853
Is recursive CTE possible to rewrite this? If yes, how?
Post #1478281
Posted Sunday, July 28, 2013 5:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 1,074, Visits: 6,360
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1478344
Posted Sunday, July 28, 2013 1:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:27 PM
Points: 327, Visits: 853
Can you give me some pointers of rewriting this query? Can data be grabbed at 1 shot instead of doing UNION ALL.
Post #1478389
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse