Function To Call Function By Name Given As Parameter
Jul 20, 2005
I want to write function to call another function which name is
parameter to first function. Other parameters should be passed to
called function.
If I call it function('f1',10) it should call f1(10). If I call it
function('f2',5) it should call f2(5).
So far i tried something like
CREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)
RETURNS varchar(50) AS
BEGIN
return(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')
END
When I call it select dbo.formuła('f_test',1000) it returns
'select f_test(1000)', but not value of f_test(1000).
Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.
In t-sql 2012, the followinng sql works fine when I declare @reportID.
IF @reportID <> 0 BEGIN SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue UNION SELECT 'Students report 2', 2 UNION
[code]...
However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:
FUNCTION [dbo].[fn_splitString] ( @listString VARCHAR(MAX) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l CROSS JOIN dbo.sequenceNumbers sn WHERE sn.Num < LEN(l.listString) AND SUBSTRING(l.listString, sn.Num, 1) = ',' )
GO
how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString 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)
Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.
1 ALTER PROCEDURE [dbo].[GetPortalSettings]2 (3 @PartyId uniqueidentifier,45 AS6 SET NOCOUNT ON7 CREATE TABLE #Groups8 (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
select centerid from GetChildCenter(@Centerid) union select centerid from getParentCenter(@Centerid) } as Center c
on c.Centerid = client.Centerid
Query 2
declare @Center table ( centerid int) insert into @Center
select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)
Select Clinetid from clinet inner join @Center c on c.Centerid = client.Centerid
I just want to know which one is better performance wise.. because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()
Hi All, I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions: 1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject. 2) Can someone provide any links to online tutorials on t-sql? Thanks in advance. Curtis
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
Hello All, How do i call a user defined function from within a stored procedure, I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string. That part works.
We found the problem that when the SP call function,there must have 'dbo.' before the function.Does it necessarily?Can delete 'dbo.' schema when call function in SP?
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.
I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?
Thanks for any help.
ALTER PROCEDURE [dbo].[pTAPServiceWeb]
@sUrl varchar(200),
@response varchar(8000) out
AS
DECLARE @obj int
DECLARE @hr int
DECLARE @status int
DECLARE @msg varchar(255)
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
how can you call a sql function in data flow? I have a function that calculate age base on the data in two columns . I would like to call this function in data flow to calculate the age..
I have a question that seems easy but I can not figure out...
Premise:
Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.
I have a Dataset that has a calculation for a column within a select statement
Query Pseudocode:
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from
(subquery"blah" )
Union
(Subquery"blah")
Custom Code:
Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double if denominator = 0 then return 0 else return numerator/denominator end if End Function
How To use:
If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.
Problem:
How do I add this code reference in the following dataset select statement
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
Help!
P.S.
this is a Matrix report and this select statement is within one of the datasets that fill a matrix.
I have a function that I need to call from an execute sql task. I want to bind the return value from the function to an ssis variable.
Can someone please show me an example of what the function syntax needs to look like in order for this to work? I know that with sp's, you need to explicitly state the column names.
What is the syntax on calling a function from a column formula in an MS SQL table.
I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?
SELECT dbo.FunctionName([Parameter1, Parameter2])
But i can't save the table, "Error validating the formula".
Hello Folks,I encountered a problem with SQL server 2000 and UDFs.I have a scalar UDF and a table UDF where I would like the scalar UDFto provide the argument for the table UDF like in:SELECT*FROMtransaction_tWHEREtrxn_gu_id in (select get_trxns_for_quarter(get_current_quarter( GetDate() ) ))'get_current_quarter' returns an integer which is a GUID in a tablecontaining business quarter definitions, like start date, end date.'get_current_quarter' is a scalar UDF.'get_trxns_for_quarter' will then get all transctions that fall intothat quarter and return their GUID's in a table.'get_trxns_for_quarter' is a table UDF.This doesn't seem to work at all. Regardless whether I provide thenamespace (schema) calling the scalar UDF or not. Error message isjust different.Both functions operate correctly invoked un-nested.The whole expression does work fine if I turn 'get_trxns_for_quarter'into a scalar UDF as well, e.g. by returning just one trxn_gu_id withe.g. MAX() in a scalar datatype. But of course that's no good to me.It also works fine if I select the result of 'get_current_quarter'into a variable and pass that variable into 'get_trxns_for_quarter'.But that's no good to me either since then I cannot use the wholething embedded into other SELECT clauses.Both UDF's are non-deterministic but I couldnt see how that would havean impact anyway.Never mind the syntax on that example or anyhting, I tried all theobvious and not so obvious stuff and it really seems to come down tothe fact that one UDF is scalar and the other one is not. However, Idid not come across any type of information saying that this cannot bedone.Have you any ideas?Any help would be greatly appreciated.Carsten
I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow. I tried OLD DB Command but I cannot configure to return a value back to the same data flow. If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process? Need more detailed instruction.
I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below:
CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end;
In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked.
The error message is :provider can not derive parameter information and setparameterinfo has not been called.
I am creating a vb.net app for a windows ce handheld device. I am using replication to create the database on the handheld. I am getting this error when this error:
Hi, I just found out that when I create a user defined scalar function, I must call it using dbo.[myFunctionName]. Why won't it work w/out dbo? Why are stored procedures able to use omit dbo?
Also, what is dbo specifying? I'm very unfamiliar with sql server security. Is this the user, schema, role? What's a schema? lol. Thanks.
To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.
I've tried
SELECT
{fn PRO_ELEMENT(fldarr1,1,1)} as fld1
from ls1..pub.tab
This just returns an unknown function message which I believe is on the SQL Server end of the call.
This statement works fine through Business Objects.
In a nut shell I want to be able to instruction some Data Analysts on how to modify SSIS packages using the simpliest solutions possible. This is because there are many different data sources and some of these data sources have a huge number of fields, and yes you guessed it these data sources are subject to change on a regular basis.
A very common task they will need to do is to modify an SSIS package to do a to transform of a source date string format of "YYYYMMDD" into a date data type field within a table.
Similar threads have advised the use of the Data Flow Transformations->Derived Column for this sort of thing.
So within the Expression Text box I have inserted the following SSIS compatible SQL to convert the above string into a british format date data type; -
