T-SQL (SS2K8) :: UDF Scalar Value Function
Jun 24, 2015
I build a UDF scalar function like this:
CREATE FUNCTION VerificaAcessoPerfil
(
@codigo INT
)
RETURNS INT
[Code] ....
Curiously when i call my function the same one return always the same value, ex:
Select VerificaAcessoPerfil(2)
the return value is : 698 ??
but if i run the Select statment like this:
SELECT DISTINCT codigo,
(case codigo WHEN 1 THEN 695
WHEN 11 THEN 697 WHEN 2 THEN 211
WHEN 10 THEN 698 WHEN 13 THEN 696
WHEN 4 THEN 1 END)[codigo]
FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp
WHERE codigo IN (1,11,2,10,13,4)
ORDER BY 1 ASC
The value are:
1695
2211
41
10698
11697
13696
View 9 Replies
ADVERTISEMENT
Dec 1, 2014
I have troubles with this scalar-valued UDF:
I get the error:
Select statements included within a function cannot return data to a client.
Is this a proper way to include a CTE in a function?
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)
[code].....
View 4 Replies
View Related
Mar 5, 2008
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 8 Replies
View Related
Jul 20, 2005
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 4 Replies
View Related
Jan 19, 2014
I have a scalar function, which calculates the similarity of two strings. I use the following query, to compare the entries of one table against the value 'Test' and return the entries, which have a value > 50:
;WITH cte1 AS (
SELECT b.FirstName,
(SELECT fn_similarity('Test', b.FirstName)) AS [Value],
b.LastName
FROM [AdventureWorks2012].[Person].[Person] b
)
SELECT *
FROM cte1
WHERE [Value] > 50.00
ORDER BY [Value] DESC
Now I want to use this query against the first 50 entries of the [Person] table, so that the resultset includes all the values of the first 50 persons and the entries, which are similar to them.
At the moment I use a WHILE-loop and write the five single resultsets in a temporary table. Is there another way / a better way, maybe via a join?
View 9 Replies
View Related
Nov 28, 2007
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 4 Replies
View Related
May 14, 2015
I've a scalar function which is killing my performance. I've been used the SQL profiler and also DMVs to catch execution information. I'd like to store the value received by this function and also the time that it happened, but I can't think in a way to do it.
View 5 Replies
View Related
Jan 28, 2008
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 1 Replies
View Related
Aug 3, 2006
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 3 Replies
View Related
Jul 11, 2007
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 1 Replies
View Related
Mar 10, 2008
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 12 Replies
View Related
Jun 16, 2015
I have this code:
Declare @sql as varchar(4000)
declare @tbl as varchar(100)
declare @exists as bit
select @tbl = 'ACA_RSF'
select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''
print @sql
exec (@sql)
and it returns 18 million for a record count.I have this scalar returning function, which models the above, and it returns zero:
select dbo.fnGet_Rec_Count('ACA_RSF') as cnt
here is the code:
alter FUNCTION spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN
-- Declare the return variable here
DECLARE @count bigint
-- Add the T-SQL statements to compute the return value here
select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')
-- Return the result of the function
RETURN @count
END
GO
I get zero regardless of where @count is declared as in or bigint.
View 9 Replies
View Related
Aug 9, 2007
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 1 Replies
View Related
May 18, 2007
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 10 Replies
View Related
Feb 1, 2006
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 1 Replies
View Related
May 28, 2015
I have a procedure that calls a SVF to convert an xmldocument. The ultimate purpose is to update the xml in a column in a multi-million row table. The xml is stored as varchar(MAX), it was supposed to carry any type of text, initially at least.
My question is: why is the xml-parsing performed inside the function much slower when i pass the xmldocument as type xml than when it is passed as varchar(MAX) and the CAST to xml is within the function? Does processing the xml input parameter in SlowFunction involve expensive crossing of some context border?
The two versions of the SVF (they return the rowcount in this simplified example):
CREATE FUNCTION [dbo].[FastFunction]
(
@inDetaljerText varchar(MAX)
)
RETURNS int
[Code] ....
The two versions of the SP
CREATE PROCEDURE [dbo].[FastProcedure]
AS
BEGIN
SET NOCOUNT ON;
select
dbo.FastFunction(al.Detaljer)
[Code] ....
View 2 Replies
View Related
Mar 2, 2007
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 8 Replies
View Related
Jul 26, 2006
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 4 Replies
View Related
Mar 12, 2014
How to get this out put.
Details:
declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM
declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert
--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM
What is the correct date format to achieve this.
View 2 Replies
View Related
Apr 2, 2014
I am just wondering why
SELECT POWER(2.718, -34.08)
returns me 0.
Tested it in Microsoft Excel
=2.718^-34.08
and it is returning me 1.58774E-15 which is approximate 15 decimal points.
My table column defined as FLOAT which can take up to 54.
Is it the limitation in the POWER function ?
View 3 Replies
View Related
Apr 17, 2014
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).
View 2 Replies
View Related
Aug 14, 2014
How can I get the data retrieved from the exec function below into Excel
DECLARE @columns NVARCHAR(MAX) ,
@columns_n NVARCHAR(MAX) ,
@sql NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT @columns += N', X.' + QUOTENAME(aaTrxDim)
[code]....
View 5 Replies
View Related
Sep 11, 2014
I am having values as below:
99.87
99.96
8.67
And my output should be as:
99.8
99.9
8.6
How can I do this
View 9 Replies
View Related
Oct 20, 2014
I would like to create a table valued function using the following data:
create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG
[code].....
I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,
CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float
[Code] .....
View 4 Replies
View Related
Nov 19, 2014
I am debugging a DB maintance script which creates a table of index maintainance commands which are created separately for each index according to the level of fragmentation and other factors.
For the debugging process, I'm looking for a way to parse each command in the table without actually running them to locate any syntax errors. In other words, as if you clicked the blue check on each one.
Does such a function exist in SQL 2008 (the version I'm doing this on) or other versions?
View 3 Replies
View Related
May 6, 2015
I've this result from my 'case' query;
Jan Feb Mar April
1 2 3 4
I want ;
Month Value
JAN 1
Feb 2
Mar 3
April 4
View 3 Replies
View Related
Mar 4, 2014
I am trying to find out a way to pass an array of pbaseid's to a function.I have a function defind something like this
Fport(@portfoliobaseid, @reportingcurr, @rowno,@todate)
(
returning a table
)
trying to execute it like this but its giving error related to subquery must return single value
select * from Fport((select pbaseid from dbo.pbaseid),'us',1,'12/31/2013')
I think i need to find a way to pass this pbaseid one by one but i dont know how to do this ...
View 1 Replies
View Related
Mar 14, 2014
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
View 4 Replies
View Related
Jan 9, 2015
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table
tbl_1
(txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,
finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output
john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
[Code] ....
View 6 Replies
View Related
Mar 25, 2015
I have a table with a varchar column that is delimited by char(227)+char(228)
i.e.,
OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE
Looking for split function that accepts a multiple character value as a delimiter?
View 7 Replies
View Related
May 27, 2015
UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
View 4 Replies
View Related
Aug 10, 2015
I want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.
For example -
'Bill123?', 'Jones12.z-'
'John&12/', 'QWERT123&4'
Wanted results – the single quotes are there to show the space for the replaced characters.
'Bill123 '
'Jones12.z '
'John&12 '
'QWERT123 4'
Example SQL data
CREATE TABLE [Common].[AllowedCharacters] (
[Character] [varchar](1) NOT NULL,
[Replacement] [varchar](10) NULL,
[AlwaysInclude] [bit] NOT NULL)
GO
SET ANSI_PADDING OFF
[code]....
The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.
View 9 Replies
View Related
Sep 22, 2014
Any function that determines the geometric function from a series of spatial points (geometric or geographic.)?
If not I am ready to build one ... I found a clean C++ implementation that I can translate to T-SQL.
View 9 Replies
View Related
