Function that returns a table

I have a function that returns a single row table with two columns:

dbo.Fun1(@.param1) : colA and colB

I tried to create a stored procedure that use this function:

select col1, col2, dbo.Fun1(col1) from table1

The result is : Invalid object name 'dbo.Fun1'

There is no join between table1 and Fun1, how can I select the both columns of Fun1 ?

Thanks in advance.

Long

Can u paste the declaration of the function?|||You are trying to use a table-valued UDF like a scalar UDF which is incorrect. You can use a table-valued function only in the FROM clause or as a table source. In any case, what you are trying to do is not possible in SQL Server 2000 since you can only pass variables or constants as parameters to table-valued UDFs. In SQL Server 2005, you can use the APPLY operator to the same.|||

Thanks, Umachandar,

I have to do the selection like this:

select col1, col2, (select colA from dbo.Fun1(col1) ), ( select colB from dbo.Fun1(col1))

from table1

It works, but I'm not satisfied, as it calculates the function twice.

Any other ideas?

Thanks in advance.

Long

|||

Hi,
due to the fact that you have to execute the statement once per row, there is no way to do it ohter than your mentioned way.

Without knowing your Function I would assume that even this is very wacky, because your Return could return more than one value ?! So you have to make sure from your query / ir function that only one row will be returned.

HTH, Jens Suessmeyer.

|||

I don't see how this will work in SQL2000. If you are on SQL2005 then you can simplify the query by using APPLY operator like:

select t.col1, t.col2, f.colA, f.colB

from table1 as t

cross apply dbo.Fun1(t.col1) as f

Function call in Insert Statment

Hi

i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@.value, dbo.function(@.value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @.value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db

thx for help

Hi,

seems that you only provided 1 paramters within your query statement / parameter collection. The statement expects 2 value / value1 which both have to be supplied, if this is the same paramter you can just use the same name for them

Insert Into (value, value1)

Value(@.value, dbo.function(@.value)) --> There was also a closing parant. missing

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||supply a default value for parameter @.value in the front end. Just in case the function would not return one.

Fun of INSTEAD OF UPDATE trigger

Hi All,
Here I have a view like this:
create view vw_Lcustkeycode as
select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
union all
select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode
I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
if update(UserName) and update(DateModified) begin
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from dbo.Lcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=0
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from DataEntryArchive.dbo.aLcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=1
end
END
When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167
It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.
Can anyone here explain this strange behaviour?
Thanks,
JamesJames Ma wrote:
> Hi All,
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
> Can anyone here explain this strange behaviour?
> Thanks,
> James
Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)
But now everything is fine.
"David Gugick" wrote:

> James Ma wrote:
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hello, James
The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.
Razvan

fulltext query problem

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FU_SZ_B_B_S_M_W]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FU_SZ_B_B_S_M_W]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SELECT ContactName, CompanyName
FROM dbo.Suppliers
WHERE CONTAINS(ContactName, 'Marie')
Create function FU_SZ_B_B_S_M_W (@.idTB_JEZYK int, @.branza nvarchar(100),
@.slowo nvarchar(100), @.miasto varchar(100), @.woj int)
RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA int)
AS
BEGIN
DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
int)
INSERT @.BranzeTMP
SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
TB_FIRMA,
TB_SLOWO_BRANZA,TB_SL_KLUCZOWE
WHERE TB_BRANZA.idTB_BRANZA = TB_SLOWO_BRANZA.idTB_BRANZA
AND TB_SLOWO_BRANZA.idSL_KLUCZOWE = TB_SL_KLUCZOWE.idSL_KLUCZOWE
AND TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
AND SLOWO like N'%'+@.slowo+'%'
AND NAZWA_BRANZA = N''+LOWER(@.branza)+''
AND TB_MIEJSCOWOSC.nazwa_miejscowosc like '%'+@.miasto+'%'
AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
INSERT @.Branze
SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
FROM @.BranzeTMP
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOexactly what is the problem? Did you want the full text query to replace the
like statements in the function?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O3JJueffGHA.408@.TK2MSFTNGP04.phx.gbl...
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FU_SZ_B_B_S_M_W]') and xtype in (N'FN', N'IF', N'TF'))
> drop function [dbo].[FU_SZ_B_B_S_M_W]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> SELECT ContactName, CompanyName
> FROM dbo.Suppliers
> WHERE CONTAINS(ContactName, 'Marie')
>
>
>
> Create function FU_SZ_B_B_S_M_W (@.idTB_JEZYK int, @.branza nvarchar(100),
> @.slowo nvarchar(100), @.miasto varchar(100), @.woj int)
> RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
> int)
> AS
> BEGIN
> DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
> int)
> INSERT @.BranzeTMP
> SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
> TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
> FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
> TB_FIRMA,
> TB_SLOWO_BRANZA,TB_SL_KLUCZOWE
> WHERE TB_BRANZA.idTB_BRANZA = TB_SLOWO_BRANZA.idTB_BRANZA
> AND TB_SLOWO_BRANZA.idSL_KLUCZOWE = TB_SL_KLUCZOWE.idSL_KLUCZOWE
> AND TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
> AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
> AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
> AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
> AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
> AND SLOWO like N'%'+@.slowo+'%'
> AND NAZWA_BRANZA = N''+LOWER(@.branza)+''
> AND TB_MIEJSCOWOSC.nazwa_miejscowosc like '%'+@.miasto+'%'
> AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
> AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
> GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
> INSERT @.Branze
> SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
> FROM @.BranzeTMP
> RETURN
> END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Hi
Exactly what I want is to replace "like" statement with i.e. "contains"
statement of full text query.
I prepared everything (I established full text indexed, populated and so on)
and I changed function from:
CREATE function FU_SZ_B_M_W (@.idTB_JEZYK int, @.miasto varchar(100), @.woj
int)
RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA int)
AS BEGIN
DECLARE @.miasto2 nvarchar
SET @.miasto2 = '%'+@.miasto+'%'
DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
int)
INSERT @.BranzeTMP
SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
TB_FIRMA
WHERE TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
--AND (CONTAINS(TB_MIEJSCOWOSC.nazwa_miejscowosc,@.miasto2))
AND TB_MIEJSCOWOSC.nazwa_miejscowosc Like @.miasto2
GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
INSERT @.Branze
SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
FROM @.BranzeTMP
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
##########
to function with full text:
CREATE function FU_SZ_B_M_W (@.idTB_JEZYK int, @.miasto varchar(100), @.woj
int)
RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA int)
AS BEGIN
DECLARE @.miasto2 nvarchar
SET @.miasto2 = miasto+'*'
DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
int)
INSERT @.BranzeTMP
SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
TB_FIRMA
WHERE TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
AND (CONTAINS(TB_MIEJSCOWOSC.nazwa_miejscowosc,@.miasto2))
--AND TB_MIEJSCOWOSC.nazwa_miejscowosc Like @.miasto2
GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
INSERT @.Branze
SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
FROM @.BranzeTMP
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The clue is: AND (CONTAINS(TB_MIEJSCOWOSC.nazwa_miejscowosc,@.miasto2))
where TB_MIEJSCOWOSC.nazwa_miejscowosc is the name of a town.
I always get the statement:
Execution of a full-text operation failed. A clause of the query contained
only ignored words.
It's strange because I pass the name of a town to function. I even delete
everything from noise.eng
I'm loosing my ming....
Best Regards
Darek T.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OGLFSqkfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> exactly what is the problem? Did you want the full text query to replace
> the like statements in the function?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:O3JJueffGHA.408@.TK2MSFTNGP04.phx.gbl...
>