.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

return table from function (function has WITH common_table_expression )

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

I am trying to create following function which should return table but when I try to create it I get error message as "Select statements included within a function cannot return data to a client.". I am looking for workaround for this issue.



function dbo.GetCategoryIdTillBase(@Category_Id as char

View Complete Post

More Related Resource Links

Function to return number


I am trying to create a function that  return the number part within / / from a url if exist:

For exmple:

http://www.mydomain.com/Mobile_Exec/1234/Orange_revamps_business_website.aspx SHOULD RETURN "\1234\"

http://www.mydomain.com/News/Orange_revamps_business_website.aspx    SHOULD RETURN "\\"

http://www.mydomain.com/News/Features/627362/Orange_revamps_business_website.aspx SHOULD RETURN "\627362\"

I prefer if possible to use regular expression. Any help would be really appreciated.

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,

Scalar Function Variable length return value

Would like to write a Scalar Function that returns a variable length string of spaces. Length being determined by an input parameter (int) Maybe I am missing the obvious but it seems impossible because you have to declare the size  of the varchar return variable I know there is a performance hit using scalar functions but we are generating a view that emulates an old fixed file format with lots of unused fields. So it will make the resultant view readable. So I am looking to do select goodfield1,fnSpace(5) as Unusedfield1,goodfield2,fnSpace(10)... from MyView thanks Bob

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.

Java function needs to return only two places

I have this java function that works great, but I need it to only return two decimal places like 333.33. Currently it returns 333.3333333333. What can I do to modify this? plannedpmt = (pledgedamt / numberpayments) 

Return linq query in function

I try to return a var query from a function, but i'll get the error: Error:Error 41 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Xml.XmlElement>'. An explicit conversion exists (are you missing a cast?)  Code: protected static IEnumerable<XmlElement> GetModels(byte MerkID) { string rawXML = AutoDisk.GetModels(12345, true, MerkID, "2010", ""); XmlDocument xml = new XmlDocument(); xml.LoadXml(rawXML); var query = from feed in xml.DocumentElement.ChildNodes.Cast<XmlNode>() select new { nModelID = feed.SelectSingleNode("nModelID").InnerText, Modelnaam = feed.SelectSingleNode("Modelnaam").InnerText }; return query; } Whats wrong and how can i make this working ?  

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?

Error: "Select statements included within a function cannot return data to a client"

hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

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

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,

function or query to return continuous span of months $ years as rows in query



I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?




and so on to





Thanks in advance,




Simple return value from WebMethod from Ajax function using JQuery


I have an Ajax function called from JQuery that goes to a webservice to return a value.  I need a SIMPLE example on how I can do this.  I've been going nuts with serializing and every other aspect of this topic. 

I need to return either an ArrayList with ONE string field or a DataTable of some kind.  Either way, I'm populating it into a DropDownList.

I'm willing to consider alternatives to this idea. (Background info - I get a value from a textbox and I need to run it through a DB to get an associated value or set of values).  I'm being really general so that someone can show a simple example.  Thanks.

Capture large text return from Javascript function


I have a Javascript function that writes a large scale of text into a div for now, but then I need to capture the text and use it in code behind. I tried to use hidden field but it has size limit so it didn't work.  The text is shown in the page, but when I view source, there is no text in the div so I couldn't use code behind to read the text from the div even though runat is server because the text is always empty. Any suggestions? Thanks!!!

using table name variable in create function


anybody help me, i wanna create function using table name parameter.

this is my function code

ALTER function [dbo].[CreateRecID]( @TableName varchar(100))
returns bigInt
declare @RecID bigint
declare @Sql varchar(2000)
set @Sql = (select case when max(RecID) is null then 0 else max(RecID) + 1 end as RecID from @TableName)

return @Sql

when i run this code

i got error "Must declare the table variable "@TableName".

I have already declare a @TableName Parameter.

can you show me the way to solve my problem?

Thanks so much

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