Return NULL From A CLR Scalar-Valued Function
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
ADVERTISEMENT
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
Jun 29, 2015
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
View 4 Replies
View Related
Jan 20, 2006
I'm trying to create a SQL server 2000 function that returns a scalar value, but I keep getting the error "Return statements in scalar valued functions must include an argument". Online clarification of this error message is no help at all.I've tried all sorts of combinations of the following, without much luck. Can someone point out my dim-witted mistake, please?ALTER FUNCTION dbo.intCoursesPublic (@intCatID as int) RETURNS intASBEGIN RETURN SELECT COUNT(intCourseID) AS Expr1 FROM dbo.tbl_guru_course_list WHERE (intCatID = @intCatID)END
View 4 Replies
View Related
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
Aug 16, 2006
Hi,
I am trying to write a function which takes a string as input and returns the computed value.
I need to use the output of this function as a coulmn in another select query.
Here is the code (Example: @Equation = '(100*4)+12/272')
create function dbo.calc(@Equation nvarchar(100))
returns float
as
begin
return exec('SELECT CAST('+@Equation+' AS float)')
end
I am getting this error when i compile it
"RETURN statements in scalar valued functions must include an argument"
Any suggestions would be appreciated.
Please respond
Thanks
View 6 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
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
Dec 19, 2013
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
View 9 Replies
View Related
Apr 18, 2007
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
View 2 Replies
View Related
May 10, 2005
Hi,
I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).
Is there a common way to avoid this?
Thanx
View 5 Replies
View Related
Mar 19, 2007
From the SQL Server documentation : "The input parameters and the type returned from a SVF can be any of the scalar
data types supported by SQL Server, except rowversion, text,
ntext, image, timestamp, table, or cursor"This is a problem for me. Here's what I'm trying to do :I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.
View 2 Replies
View Related
Apr 4, 2007
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
View 2 Replies
View Related
Dec 9, 2007
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 8 Replies
View Related
Sep 20, 2006
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
View 3 Replies
View Related
Dec 10, 2002
I'm getting syntax errors that just aren't helping me at all, so I thought maybe what I'm trying to do can't be done. I'm creating a UDF with 4 parameters, and I want it to return a result set (i.e. a table). But I want a different result set depending upon the value of one of the parameters. This works totally fine as a SP, but I can't tell where to put the RETURN clause(s) on the UDF.
I've got:
CREATE FUNCTION myFunction(@param1,...,@param4)
RETURNS TABLE
AS
BEGIN
IF @param1='x'
BEGIN
RETURN(SELECT columns FROM TableX)
END
ELSE
BEGIN
RETURN(SELECT columns FROM TableY)
END
END
I get an "Incorrect syntax near the keyword 'IF'" error. I also tried using just one RETURN() wrapped around the outside of the IF construction (right after the very first BEGIN and before the last END), but to no avail. I get no errors when I run this logic as an SP. Is this type of construct not allowed in a UDF? Is there an alternative? I can't just leave this as a proc because I'm going to have to call these results from several views. Help!
Thanks,
-Ed H.
View 3 Replies
View Related
Dec 11, 2007
I keep getting an error message when I try to place the result of an execute scalar command into a text box. The msg is:"conversion from 'DBNull' to type 'String' is not valid"
The code I am using is:
Dim con as OLEDBConnection
con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:caps.mdb")
Dim cmd As OLEDBCommand
cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue
texBox1.Text = cmd.ExecuteScalar
The code works fine as long as there is a value for the Product. However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this?
Chas28
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
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
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
Aug 4, 2006
So I was creating a new table-valued function today which queries some data from a preexisting table. Since this is my first table-valued function, I decided to check out some of the examples and see what I can figure out.
One particular example helped me out a bit until I ran into some data access issues...
http://msdn2.microsoft.com/en-us/library/ms165054.aspx
So I create my function:
[SqlFunction(DataAccess = DataAccessKind.Read,SystemDataAccess=SystemDataAccessKind.Read,FillRowMethodName = "FillMyRow",TableDefinition ="p1 int, p2 int"]
public static IEnumerable getMyTable()
{
using (SqlConnection conn = ....)
{
using (SqlCommand command = conn.CreateCommand())
{
///.... populate command text, open connection
using (SqlDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
customObject1 o = new customObject1();
///... populate o's parameters from reader ...
yield return o;
}
}
}
}
public static void FillMyRow(
object source,
out int p1,
out int p2)
{
customObject1 f = (customObject1)source;
p1 = f.p1;
p2 = f.p2;
}
Notice, this example yield returns the value o upon each iteration of the reader.
Despite the fact that the DataAccess is set to Read I still get the error...
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
I did however get past this error, by creating a collection of customObject1, populated it within the while(rdr.Read()) loop, then return the collection after closing the connection, command and reader.
I assume this error has something to do with the fact that you can't yield return results from within an open reader. Is this error right though in this case? Whats causing it to throw a InvOp Exception? Or is this a bug?
Thanks for the attention.
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
Feb 26, 2008
i have given two functions - table valued and scaler valued
when select * from table_func('')
i m getting results..
but in select * from scalar_func('')
it gives error - invalid object name,
while i have both scripts open in same database but still from one func it gives results and from another it gives error..
so can anyone tell me plz. what should i do to see results from scalar func
thanks
View 10 Replies
View Related
Aug 8, 2007
I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this?
Thanks
Mangala
View 3 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
Mar 10, 2008
Hi,
I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method ..
eg..
SELECT A.Col1,A.Col2,B.Col1,B.Col2
FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B
ON A.Col1 = B.Col1
any body help me ... thanx in advance..
View 3 Replies
View Related
Jul 20, 2005
Is there a way to create a trigger directly on an inline or multi-line tablevalue function?I am trying to create a quick-and-dirty application using an Access DataProject front-end with SQL 2000 SP3 EE.Thanks.
View 2 Replies
View Related
Oct 22, 2007
Hi there. I've hit some gap in my SQL fundementals. I'm playing with table-valued functions but I can't figure out how to join those results with another table. I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this. What am I misunderstanding here?
The Given Objects:
function Split(stringToSplit, delimiter) returns table (column: token)
table Words (column: Words.word) -- table of predefined words
table Sentences (column: Sentences.sentence) -- table of sentences; tokens may not be in Words table, etc
The Problems:
1) how do I query a set of Sentences and their Tokens? (using Split)
2) how do I join tables Sentences and Words using the Split function?
The Attempts:
A)
select word, sentence, token
from Words,
Sentences,
dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences?
where word = token
resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."
B)
select word, sentence
from Words, Sentences
where word in (select token from dbo.Split(sentence, ' ')) -- correlated subquery?
resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."
View 6 Replies
View Related
May 29, 2008
Is it possible to define a constraint for Primary Key on more than 1 column or an alternate index on a column in a return table from an inline table valed function?
Example Header:
alter FUNCTION [dbo].[fntMetaFrame] (@ii_CompanyID int)
RETURNS @tbl_MetaFrame TABLE ( pk_Key int Identity(1,1) primary key,
ObjectID int ,
Seq int null )
I want the primary key to be pk_Key, ObjectID
OR
I want to add another index on ObjectID.
View 6 Replies
View Related
