Problem Passing UDF Scalar Result To UDF Table Function
I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.
My functions are defined like so:
drop function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
begin
return 1
end
go
drop function dbo.table_func
go
create function dbo.table_func(@p int)
returns table
return (select @p as id )
go
Given the above, I can do the following:
Select from the scalar function works:
1> select dbo.scalar_func() as scalar_result
2> go
scalar_result
-------------
1
Selecting from the table function works, if i pass a
constant value (or a variable)
1> select id from dbo.table_func(1)
2> go
id
-------------
1
But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:
1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
What am I missing here?
Thanks kindly
View Complete Forum Thread with Replies
Related Forum Messages:
Passing MS SQL2005 Query Result Into Javascript Function
I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function. This is how I retrieve the longitude: <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>" SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"> </asp:SqlDataSource> I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this: var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); However, lat & long do not contain the retrieved result but rather a useless System.something string. How do I assign the retrieved results to these variables and port them over to Javascript as required? Many thanks!
View Replies !
Table Scalar Function Syntax. . How Wrong And How Far Am I?
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION DetailedView AS BEGIN Declare @fieldname varchar(10) Declare @stmt varchar(4000) Declare Fields Cursor For Select Amounttype From Amounttypes Set @stmt = 'Select pono, myid, billtype' Open Fields Fetch Next From Fields Into @fieldname While @@Fetch_Status = 0 Begin Set @stmt = @stmt + ', sum(amountfc * Case When amounttype = ''' + @fieldname + ''' Then 1 Else 0 End) As ' + @fieldname Fetch Next From Fields Into @fieldname End Close Fields Deallocate Fields Set @stmt = @stmt + ' From multiplebillsviewall Group By pono, myId,billtype ' return Exec(@stmt) END
View Replies !
Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all, I copied the following code from Microsoft SQL Server 2005 Online (September 2007): UDF_table.sql: USE AdventureWorks; GO IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL DROP FUNCTION dbo.ufnGetContactInformation; GO CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL ) AS -- Returns the first name, last name, job title, and contact type for the specified contact. BEGIN DECLARE @FirstName nvarchar(50), @LastName nvarchar(50), @JobTitle nvarchar(50), @ContactType nvarchar(50); -- Get common contact information SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM Person.Contact WHERE ContactID = @ContactID; SELECT @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = @ContactID) THEN (SELECT Title FROM HumanResources.Employee WHERE ContactID = @ContactID) -- Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) THEN (SELECT ct.Name FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) -- Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = @ContactID) THEN (SELECT ct.Name FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE ContactID = @ContactID) ELSE NULL END; SET @ContactType = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = @ContactID) THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) THEN 'Vendor Contact' -- Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = @ContactID) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM Sales.Individual i WHERE i.ContactID = @ContactID) THEN 'Consumer' END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END; GO ---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise. Thanks in advance, Scott Chang
View Replies !
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't... I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it. So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need. Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like: DECLARE @table table@table=EXEC sp_pkeys MyTable That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great. Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out... SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT) Syntax Error. Ugh. Eventually, I even try: SELECT *FROM Split(substring('abc,def',2,6),DEFAULT) Syntax Error. Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function? SELECT *FROM Split('bc,def',DEFAULT) works just fine. So my questions are: Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it? Is there any way to pass a scalar-valued function as a parameter into a table-valued function? Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround: DECLARE @tmp varchar(8000) SET @tmp=(SELECT dbo.fn_pkeys('MyTable')) SELECT * FROM Split(@tmp,DEFAULT)
View Replies !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function: ALTER FUNCTION MakeArDetail ( -- Add the parameters for the function here @dateStart DATETIME, @dateEnd DATETIME ) RETURNS @arDetail TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) AS BEGIN DECLARE @arTemp TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) INSERT INTO @arTemp SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims; UPDATE @arTemp SET Claim = (SELECT SUM(Pims.AmtReq) FROM Pims WHERE Pims.Insurer = @arTemp.Insurer AND Pims.NABP = @arTemp.NABP AND Pims.NumRx = @arTemp.NumRx ); INSERT INTO @arDetail SELECT * FROM @arTemp RETURN END GO I get Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43 Must declare the scalar variable "@arTemp". I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared. If I don't include the UPDATE command the thing works.
View Replies !
Table-Valued Function Result Vs. Calculation Table
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures. Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example: DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1... Or using a table-valued function to return a temp table as the result. I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
View Replies !
Passing Table Name As Parameter To Function
Hi all. I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer. Now to my problem: I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables. Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this. All ideas are welcome. Thanks.
View Replies !
Passing Variable To Table Function In Join
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work. There is a MAIN table: ItemNo int identity(1,0), ItemType tinyint There is a WETPAINT table: ItemNo int, Color varchar(20) There is a DRYPAINT table: ItemNo int, Color varchar(20) Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType So I created a table function called getTable: CREATE FUNCTION [dbo].[gettable] ( @ItemType int = 1 ) RETURNS @thistable TABLE ( Color varchar(20) ) AS BEGIN if @ItemType = 1 insert into @thistable (color) select color from WETPAINT if @ItemType = 2 insert into @thistable (color) select color from DRYPAINT RETURN END This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like: SELECT MAIN.ItemNo, a.Color FROM MAIN INNER JOIN gettable(Main.ItemNo) as a ON a.ItemNo = MAIN.ItemNo Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one. Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again. PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
View Replies !
Problem Passing A Variable Into A Table-valued Function
Hi, i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID. The syntax is as follows: select car.id, car.licenceNumber, car.brand, car.model, (select driverName from getCurrentDriver(car.id)) as driverName from car When I try to compile I get following error on the line of the function: Incorrect syntax near '.' The database version is SQL Server 2000 SP3. What am I doing wrong? Is there a workaround for this error?
View Replies !
UD Scalar Function
I want to add four fields of a table and place the toatal in a new field.Also I wanna have the average of the fields. For e.g I have created a marksheet having four subjects.Now I wanna add the subjects and find the average of the subjects and place them in two different fields in the same table along with the respective names in the table.Pls help. Thanks in advance.
View Replies !
Scalar Function Columns
Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler
View Replies !
How To Handle Error In Scalar Function?
Hi, I have the follow function: CREATE FUNCTION [dbo].[ToTime] ( @intHora int, --A valid hour @intMin int -- A valid minute ) RETURNS smalldatetime AS BEGIN declare @strTime smalldatetime declare @errorvar int select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar) return @strTime; END the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23 and the parameter for the minute is an negative number (-1) or a number > 59, the function produce an error. I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example if @intHora < 0 or @intHora >23 begin set @intHora = 0 end if @intMin <0 or @intMin>59 begin set @intMin = 0 end please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.
View Replies !
CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function. WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END ) I kept on getting errors, like Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View Replies !
Return NULL From A CLR Scalar-Valued Function
Hi, I have an assembly that contains the following function: Public Class Lookup <SqlFunction()> _ Public Shared Function MyTest() As Integer Return System.Data.SqlTypes.SqlInt64.Null End Function End Class Then in SSMS: CREATE ASSEMBLY IRS_MyTest FROM '\machine empmyAssembly.dll' GO CREATE FUNCTION dbo.MyTest() RETURNS INT AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest GO when I run: SELECT dbo.MyTest() the following is returned: Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest': System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. System.Data.SqlTypes.SqlNullValueException: at System.Data.SqlTypes.SqlInt64.get_Value() at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x) at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest() Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal. Thanks, Jan.
View Replies !
SOLVED: How To Call Scalar Function From JDBC
As an example, I have a scalar function called TRIM that takes a VARCHAR parameter, does a LTRIM(TRIM(VARCHAR)), and returns the result. How can I call this function from java using JDBC? I have only had luck calling basic stored procedures, but I need to call functions as well. Thanks, Ken
View Replies !
Display Single Number Using ObjectDataSource Scalar Function
I wrote a Scalar UDF in SQL2005 that returns an integer. I want to be able to display this integer in a ASP.Net 2.0 web page. I typically use a DAL for all data so I added an ObjectDataSource as a Qeury that contains only the UDF. How do I easily display the value in a Label Control or? I have tried to use a Repeater with a label, a Formview with a Label, all to no avail. Any suggestions?
View Replies !
Alternate Of Scalar Function For Comma Sepated Selection
I have 2 Tables as below, 1) Purchase_Invoice a. PurchaseInvoiceID b. SupplierName c. BillNo d. BillDate 2) Purchase_Invoice_Items a. PurchaseInvoiceItemID b. PurhcaseInvoiceID (FK to Purchase_Invoice Table) c. ItemName d. Quantity e. Rate Now I want to select all the records of Purhcase_Invoice table exactly once with one column at last containing comma separated Item name of particular PurhcaseInvoiceID as below PurchaseInvoiceID | SupplierName | BillNo | BillDate | Items (Comma Separated Items) Currently I am using Scalar Function which takes PurchaseInvoiceID as Argument and Returns Comma Separated Itemnames€¦ It works well but when number of records are large than performance is very poor. Is there another way of doing same thing? By Join or By some System Function? Nilesh
View Replies !
Calling Scalar Valued Function From SSIS OleDB Command Transformation
Hi There, I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say.. 'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column. I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this. Thanks a lot!!
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
How To Grant Rights For The Anonymous IIS Web User IUSR_.. To Execute Scalar Function In Assembly
Dear all, Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET. I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string: select dbo.CLRHTMLString('abc') The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.). To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied: EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'. Note: The 'webuser' database role has Execute permission on the Assembly. I have also tested this from my actual web page, with the following results: (1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly. (2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly. Further test results: (3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user. (4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list. Thankful for any advice on this matter.
View Replies !
Passing A Result Set To A Stored Procedure
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View Replies !
Fetch A Scalar Value From A Table---help
Dear Xperts im relatively new to t-sql. i have a table column which show the destination call no including country and area code with telephone... DestinationNo=63927012345678 (first 2 to 8 digits may be the contry and their area or service provider code) i have call rate table which has code and there rates. code destination rate ------------------------------------------- 63PHILIPPINES PROPER0.6916 632PHILIPPINES MANILA0.6939 632240PHILIPPINES MOBILE OTHER0.7007 632246PHILIPPINES MOBILE OTHER0.7007 632248PHILIPPINES MOBILE OTHER0.7007 632249PHILIPPINES MOBILE OTHER0.7007 632250PHILIPPINES MOBILE OTHER0.7007 63927 PHILIPPINES MOBILE OTHER 0.876 for each destination no, have to take max 8 digits and loop thru (8 then 7,6, 5, 4... 2 reverse order loop) to get the rate against the rate table. i have millions of recs to get the rates value ... pls suggest a optimum solution with some stored procedure or function with source code. Note::The input file is a legacy system, therefore NO changes expected... Thanks a lot in advance Regards
View Replies !
User Defined Functions, Passing Parameters From Another Udf's Results (end Result=Crosstab)
Hi All:I've read a whole slew of posts about creating temp tables using storedproceedures to get the crosstab ability, but I'm wondering if, for thisspecific case, there might be a more efficient way.What makes this question different from the others that I've read isthat I'm using user defined functions, not tables. I actually thinkthat I've got the crosstab thing down, it's just passing the parameterto the 2nd udf that's messing me up.I've got a people table and an address table. Each person can havemultiple addresses. I need to create a dataset that has in each rowthe name of the person, the first address, any second address, and anythird address. I only need to show the first 3, so if there's 100, Ican just ignore the rest.I created a user defined function to return the 1st, 2nd, or 3rdaddress for a given person.udf_ReturnAddress(PersonID,MatchNumber)Another user defined function returns the people that I'm looking for(potential duplicates for a person in this case).udf_ReturnPossibleDupsForAPerson(PersonID)SELECTMain.FoundPersonID, Main.LastName, A1.Street, A2.Street,A3.StreetFROMudf(ReturnPossibleDupsForAPerson(@PersonID) MainTableCROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,3) Add3) A3If, for the first parameter for the return address function, I replaceMain.FoundPersonID with the ID of a person, it works just fine. Iobviously don't want a static id as a parameter - I want to use the IDof the person that the first udf found. Leaving the variableMainTable.PersonID there causes an error in the query designer though.I get "Error in list of function arguments: '.' not recognized.So maybe my problem is that I just don't know how to pass the id of theperson that's found by the first UDF as the parameter of the functionto find the found person's 3 addresses.Any guidance would be greatly appreciated!ThanksKen
View Replies !
Getting SUM Function Result
Hello,In the project I'm working on, I need to add up all rows data for onecolumn. So, I have this code:$getprodcount = mysql_query("SELECT SUM(qty) FROM purchase");$numproducts=$getprodcount;Later on, I have this code: <?php print $numproducts; ?>What is being printed is Resource id #5...not the numeric value of what issupposed to be a sum. What is wrong? I am assuming taht resource id #5 is apointer of some sorts to the number I am looking for, but how do you getthe actual sum number?Thanks in advance!--Message posted via http://www.sqlmonster.com
View Replies !
Function And One Result Set
I have an function executed like that: select top 1 * from f_Function1('XXX',2099,99) ORDER BY ef DESC I have about 400 XXX values. I execute function individually for them but normally i get results in individual result sets. select top 1 * from f_Function1('XXX1',2099,99) ORDER BY ef DESC select top 1 * from f_Function1('XXX2',2099,99) ORDER BY ef DESC select top 1 * from f_Function1('XXX3',2099,99) ORDER BY ef DESC I want all 400 results in one result set. What should i do? Thanks in advance.
View Replies !
Passing Columns To CLR Function
Hello, I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like: Code Snippet public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z) { //Do Something... x = .... y=... z=... } I want to call this code from SQL code: Code Snippet create table #Temp (float a,float b) declare @x float declare @y float declare @z float exec dbo.DoSomething(a,b,@x,@y,@z) ---???? Do someone have an idea?
View Replies !
Passing Parameter In Function
I have to pass 3parameters in function, @begindate,@enddate and @group_type.. but in @group_type should be - state,zipcode and country from salestable inview :vwstzipcont create view vwstzipcont as select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3 left outer join (select distinct stype,docdate from salesdisttable) s2 on s2.stype = s3.stype where s2.soptype = 2 go create function mystzipcont ( @begindate datetime, @enddate datetime, @group_type char(70)) RETURNS TABLE AS RETURN (Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from ( select distinct vs.docdate,vs.itemnmbr, p.index From Pubs P inner join vwstzipcont vs on vs.index = p.index Where (vs.docdate between @begindate and @enddate) and @group_type ) as t order by t.itemnmbr,t.docdate end how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country can anyone tell me? what condition should be in where clause for this variable? thanks
View Replies !
Index On Result Of Function
I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15) The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?
View Replies !
How To Get Result In Decimal Using AVG Function
Hello Sir I am working on a Teacher Evaluation Project. In my database I store results from 1-5 as evaluation indicators. I apply AVG() function on the result column. The result of the query is in integer values (i.e) 4, 3 2 or 5. I want the resutl up to two decimal places. How can i write the query to get the result in decimal form? Shahbaz Hassan Wasti
View Replies !
Passing A Suquery To A Function As A Parameter
How would one pass a subquery to a function as a parameter? I have a function, f_Split that returns a table and has two parameters @List varchar(max) and @Delim char(1). I'd like to use it to normalize rows in a table and return the results for use in a report. I tried the following: SELECT * FROM dbo.f_Split((SELECT Code FROM Codes WHERE ID = 10), '|') I'm getting a syntax error for the subquery. Could anyone show me the proper way to pass a subquery to a function, even if the function is different from what I have defined here. Thanks, DD
View Replies !
In A Query, Passing Data To A .net Function
Hello, I have just migrated some databases from sql2000 to sql2005 and I just read about the possibility to use CLR-based functions within a query. This may be really nice for me, since I often need complex functions and I often cannot write them in TSQL. But my question is: what kind of data can I pass in a sql query to a .net function? Obviously, simple data, like numbers and strings from within a record can be passed. But would it be possible to go further, like passing a complete record, or even passing a set of records? How far could I go in sql when using .net function? Thanks to inform me about the available power of this new feature.
View Replies !
SQL Query - Using Result Of Create Function
I created a function that will return from OpenDataSource('.....') tablename where ... is fully populated. However, I can't figure out how to use it? For example select functiona (parameter) as data_src this returns the "from" statement above I then try to run select * data_src So how do I reference the contents of data_src in the select? Thanks for any help
View Replies !
How To Return The Result Of An EXEC From A Function
Hi, I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute. This is my function: ALTER FUNCTION [dbo].[ReturnPickItemValue] ( -- Add the parameters for the function here @TypeID int, @CaseID int ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @RTN varchar(max) IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1 BEGIN SET @RTN = 'SELECT PickItem I + CASE D.IsStartDateEstimated WHEN 0 THEN CAST(StartDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar) END + CASE D.IsEndDateEstimated WHEN 0 THEN CAST(EndDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar) END FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND P.CaseID = ' + CAST(@CaseID as varchar) END ELSE BEGIN SET @RTN= 'SELECT I.PickItem FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND CaseID = ' + CAST(@CaseID as varchar) END RETURN @RTN END Each time I try " RETURN EXEC(@RTN) " or something similar I get an error. I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error. Anyone with any ideas?
View Replies !
Passing Variable To String Compare In Function
I have created a function with: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int) RETURNS VARCHAR(100) AS BEGIN DECLARE @Output varchar(100) SELECT @Output = COALESCE(@Output + '/', '') + CAST(quantity AS varchar(5)) FROM flexing_stock_transactions WHERE item = @item AND week = @week GROUP BY quantity ORDER BY quantity RETURN @Output END how can I pass the variable @item correctly for the string comparison WHERE item = @item AND week = @week to work correctly please? WHERE item = '@item' AND week = @week won't work and WHERE item = @item AND week = @week won't work.
View Replies !
Aggregate Function For Select Statement Result?
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls. currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do. Problem: i need to call an aggregate count on the results of another aggregate function (sum) with a group by. *ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid. thanks, Tom Anderson Software Engineer Custom Business Solutions
View Replies !
How To Use A Function To Format And Display Result From Data Reader
Hi guys n gals ! I am having a few problems manipulating the results of my data reader,To gather the data I need my code is: // database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]); // sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open(); // execute SqlDataReader dr = rs.ExecuteReader(); // send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind(); Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is: // prevent html public string StripHtml(string data) { // grab the data string theData = data; // replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; } But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
View Replies !
Converting Result Of Aggregate Function Calculation To A Double (C#)
I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good). If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable? Possibly, is the expression after an AS something that I can manipulate into a double variable? My end goal is to insert the result of the calculation into a database. What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps: <asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource"> <Columns> <asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" /> <asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" /> <asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" /> <asp:BoundField DataField="density" HeaderText="density" SortExpression="density" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="lbsgalDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT a.Formulation, SUM (a.lbs) AS lbs, SUM ((a.lbs)/(b.density)) AS gal, ( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density FROM Formulations a INNER JOIN Materials b ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation"> <selectparameters> <asp:controlparameter controlid="DropDownList1" name="Formulation" propertyname="SelectedValue" type="String" /> </selectparameters> </asp:SqlDataSource>
View Replies !
Passing Parameter Query From SQL Function To Access Project Report
I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report? THANKS! p.s. I tried searching for other postings on this without any luck.
View Replies !
Passing Execute DTS Package Result (success/failure) To Calling SSIS Package
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong. As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended. Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way? If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
View Replies !
|
TRACKER
