.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Table valued function content

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

Hi there,

I have been scouring the web for hours with no success.

I am looking for a way to fetch a function's script/code content. I have a way of doing this with stored procedures (see below), I also need to do this with functions.



SELECT [sp].[name], [sc].[text], [sc].[ctext]
INNER JOIN SYS.OBJECTS AS [sp] ON [sc].[id] = [sp].[object_id]
WHERE [sp].[type] = 'p'


Any suggestions would be greatly appreciated.

Thanks in advance for all the trouble.





View Complete Post

More Related Resource Links

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

How to insert values into my table the data is coming from inline table valued function

Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

table valued function problem

This is my table-valued function Problem is it is returning only 1 record but it is supposed to return more than one i stuck with this problem plz replyALTER function [dbo].[funcqidata](@dst int,@mth int,@yer int) returns @cqi_rpt table ( hosp_name varchar(100) NULL, bedded_strength int NULL, out_patients int NULL, in_patients int NULL, minor_surgeries int NULL, major_surgeries int NULL, deliveries int NULL, radio int NULL, lab_test int NULL, tot_part1 int NULL, waste_man int NULL, swab_test int NULL, attendence int NULL, drug int NULL, charges_thismonth int NULL, charges_lastmonth int NULL, usr_charges_marks int NULL, tot_part2 int NULL --grand_tot int NULL ) as BEGIN DECLARE @hosp_name varchar(100), @bedded_strength int, @out_patients int, @in_patients int, @minor_surgeries int, @major_surgeries int, @deliveries int, @radio int, @lab_test int, @tot_part1 int, @opd_handled int, @opd_target int, --------- supporting variables declared for calculation ----------- @ipd_handled int, @ipd_target int, @minor_handled int, @minor_target int, @major_handled int, @major_target int, @delivery_handled int, @delivery_target int, @radio_handled int, @radio_target int, @lab_handled int, @lab_target int, -------- end of declaring variables ----------------------------- @check_charges int, @waste_man int, @swab_test int,

trying to use CASE within table-valued function

Hi, I am trying to create a table-valued function that takes a string argument and, depending upon it's value, returns one of a number of identically-structured tables (and before someone asks why multiple identically structured tables, NOT my idea!).  I am a novice with T-SQL, but I've been attempting something like this for the function: Case @Input   When 's1' then return table1   When 's1' then return table2 End ... but I keep getting errors.  Can someone help get the ball rolling and show me the basics on how to set up a function to do this?  I'm hoping since I'm just redirecting to existing tables that this function will have minimal performance hits.

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Schema comparer mistake in table-valued function (Nullability in column definition)

Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

how to use table valued function with join on table in server 2000


Hi please any oone tell me how to call a table valued function


select * from get_date_time_from_gmt ('+17:45','1879-01-02 00:00:00.000','17:30')  when i call it then it will work properly. but now i have to use columns from cricket_master  

as mention below



* from dbo.get_date_time_from_gmt

table valued function from column from othe table


Hi please any oone tell me how to call a table valued function


select * from get_date_time_from_gmt ('+17:45','1879-01-02 00:00:00.000','17:30')  when i call it then it will work properly. but now i have to use columns from cricket_master  

as mention below



* from dbo.get_date_time_from_gmt (a.gmt_offset

How do display null rows returned from my table valued function?

DECLARE @techNumLoop VARCHAR(25)
 Tech Varchar(25) 
,Item_Count Integer NULL 
,Expenses MONEY NULL

SELECT DISTINCT t.Case_Number Tech
FROM tblTechnology t
OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
INSERT @myTemp
FROM ufn_Find_Some_Stuff(@techNumLoop)


Table Valued Function in the select statement


Hi, I have created a table valued function

dbo.FnTaskGrp(@p1 int,@p2 datetime)..

I got the result by executing,

SELECT * FROM dbo.FnTaskGrp(1,'1-jan-2010')

But For this ,

select * from dbo.FnTaskGrp(tab.WflTask,tab.WflModDt)

                  select WflTask,WflModDt from WorkFlow


I got the Error,

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'tab'.

Isn't Possible in SQLSERVER 2005?









SQL Server 2008 CLR Table-Valued function works exactly twice, then fails unless assembly reloaded.


Very simple TVF UDF uses WebRequest and HttpWebReponse, passing the response stream off to custom class that implements IEnumerable. First invocation has a good delay (seen many other posters on this issue), second invocation is lightening fast, then all calls after that produce this error:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "tvf_OneMinuteBars":
System.Net.WebException: The operation has timed out
   at System.Net.HttpWebRequest.GetResponse()

Now, I've tried all manner of settings on the webrequest: KeepAlive, Timeout (currently 15 secs), Proxy = null. But I just can't figure it out. What is really a bummer is that I can debug invoke this against a local SQL Server as many times as I like. But if I deploy (in VS) to two different servers, or if I manually copy the assembly to the servers and do the CREATE ASSEMBLY / CREATE FUNCTION manually, it always times out after the second invocation.

Here is the tricky part: the object calling GetResponse calls HttpWebResponse.GetResponseStream(), storing the reference in a property. Then the object reference passed to the constructor of a custom class implementing IEnumerable, which does the right things with the response stream. Super fast, n

Content page control passes as argument to javascript function


Here is the content page:


<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="JavaDynamicCopyValue.aspx.cs" Inherits="JavaDynamicCopyValue" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 <script type="text/javascript" >
 function copyValue(input, output )
   ouput.value = input.value;
   return true;
 <asp:TextBox ID="txt1" runat="server" onchange="copyValue(this,document.getElementById('<%=txt2.ClientID%>'))"></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="txt2" runat="server"></asp:TextBox>


I got a javascript run time error says output is undefined. if I view source of the page, the txt1 markup became:

<input name="ctl00$ContentPlaceHol

Repeating table not showing content following a rule refresh

Background info: I have a form where I populate employee info from the GetUserProfileByName ws. From there I populate its manager info from another web service call (seperate data connections).  Once the manager is populated, through rules, I'm populating the first approver. All this is done at form load through rules. The approvers node is defined as follows and is displayed on the form as a repeating table:  participants (group)    approver (repeating group)       ApproverName       ApproverLogin       ApproverEmail Problem: The repeating table is not showing any content even though I'm using an alert to show that I do in fact have one row in there. As a side note, I know the table is working because if the user elects to delegate approval to another user (through the people picker), the table shows these approvers correctly.  Anyone as any clue what I'm doing wrong? Thanks!    

Copy table (with content and links) from one page to another

Hi, I'm new to sharepoint and learning this software on my own.  I'm running WinXP Professional.  I've created a table on one page in a subfolder/directory (10Kgold) and would like to use the same table on a page in a different subfolder (24Kgold).  Both subfolders are in the same root folder.  Here are the steps I took: On 10kgold/bracelets.aspx page: right-click on element to copy --> select Copy On 24kgold/bracelets.aspx page: right-click --> Paste On the 24kgold/bracelets page, the "Paste" option was disabled, so then I knew I wouldn't be able to copy/paste the table I copied from 10K gold/bracelets page. I don't want to recreate this table again if I don't have to. Also, I've tried to copy and paste links within the same page, but that doesn't work either. Here are the steps: Select link (yellow-gold) on 10kgold/bracelets page: right-click --> Copy Scroll to where I want to paste the link Right-click (yellow-gold) on 24kgold/bracelets page: --> Paste Is there a way to copy and paste links and tables, etc. from one page to another, with the page/s being in either the same subfolder and/or a different subfolder as long as they are within the same root folder? Thanks in advance for your help.    

using table valued parameters for stored procedures any limitations

I am thinking of using a table valued parameter for my new stored procedure, but wonder if there are any performance implications to be aware of and is the limitation of using statistics applicable with table value parameters, i.e do they keep statistics and will this affect the judgement of query plans i.e estimated number of rows, from my experience table variables do suffer from this problem but I would like to confirm if it applies to table value parameters as well.

Stored procedure and table value function


I have a function called F_TBL_VALS_FROM_STRING this function returns a table and uses '|' as a delimiter to distinguish between values.
this function is used in a procedure (bellow), while I've tested this function and it returns the requested values, using it in this stored procedure does not work.

ALTER PROCEDURE dbo.mytestproc
    @doorList     varchar(4000),
    --From Date
    @FromDateYear   int,
    @FromDateMonth  int,
    @FromDateDay   int,
    --To Date
    @ToDateYear    int,
    @ToDateMonth   int,
    @ToDateDay    int,
    --From Time
    @FromTimeHour   int,
    @FromTimeMinute  int,
    @FromTimeSecond  int,
    --To Time
    @ToTimeHour     int,
    @ToTimeMinute   int,
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend