How to call one UDF from Query Analizer? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to call one UDF from Query Analizer?

Author

Topic

koci
Starting Member

6 Posts

Posted - 2004-10-02 : 18:16:21

Hi all. I make one function en SQL Server 2000.

---------------------------------------------------------------------------------------
CREATE FUNCTION CRelacion (@DesdeFecha Datetime = '01/01/04',
@HastaFecha Datetime = '31/12/04')

RETURNS TABLE

AS

RETURN

(SELECT ALBARANES.IDAlbaran,
ALBARANES_DETALLE.IvaVenta

FROM ALBARANES

INNER JOIN ALBARANES_DETALLE ON ALBARANES.IDAlbaran = ALBARANES_DETALLE.IDAlbaran

WHERE ALBARANES.FechaAlbaran Between @DesdeFecha And @HastaFecha

GROUP BY ALBARANES.IDAlbaran, ALBARANES_DETALLE.IvaVenta)

----------------------------------------------------------------------------------------

So my function have to input parameters, @DesdeFecha y @HastaFecha.

The question is: for see if work corect, HOW to run this function FROM Query Analizer?

When I write in Query Analizer:

Exec dbo.CRelacion

I obtain the following message (in spanish):

"Error en la petición procedimiento 'CRelacion'. 'CRelacion' es un objeto función."

I know that it's one function object, but I don't know how to call a such function.

When I write:

Exec dbo.CRelacion('01/01/04','31/12/04')

I got the following message (in spanish):

Línea 1: sintaxis incorrecta cerca de '01/01/04'.

Have somebody any idea?

Thank's anticipated.

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-02 : 18:24:03

try
select * from dbo.CRelacion('01/01/04','31/12/04')

koci
Starting Member

6 Posts

Posted - 2004-10-02 : 18:53:26

Many thank's VIG! It's work!

I make this function - like one stored procedure - to call in other procedure.
But with this statement (select * from dbo.CRelacion('01/01/04','31/12/04')) how to build the main procedure (the principal)?

Main procedure:

SELECT CRelacion.IDAlbaran, ALBARAN.FechaAlbaran
FROM ALBARANES
JOIN ????? ON ALBARAN.IDALBARAN = ????.IDALBARAN

PS. The main procedure it's a litle more complicated, here I write only one fragment.

Thank's.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-02 : 19:00:32

will this do?

SELECT t2.IDAlbaran, t1.FechaAlbaran
FROM ALBARANES t1
JOIN dbo.CRelacion('01/01/04','31/12/04') t2 ON t1.IDALBARAN = t2.IDALBARAN

Go with the flow & have fun! Else fight the flow

koci
Starting Member

6 Posts

Posted - 2004-10-04 : 10:09:21

Thank's spirit1, It's work!

How to use one stored procedure (with two input paramaters) instead of this function (dbo.CRelacion), to obtain the same result?

The question is: how to use one stored procedure - not user defined function-, (with input parameters) in other procedure? In fact, how to use the columns of one stored procedure in other stored procedure? Something like in Access, when you have one query with parameters in Where statement, can use this query in other query and access the parameterized query's columns like any other column of any other query or table.

Thank's anticipated.

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-04 : 10:25:55

create proc spCRelacion
@DesdeFecha Datetime = '01/01/04',
@HastaFecha Datetime = '31/12/04'
as
set nocount on
SELECT t2.IDAlbaran, t1.FechaAlbaran
FROM ALBARANES t1
JOIN
(SELECT ALBARANES.IDAlbaran,
ALBARANES_DETALLE.IvaVenta

FROM ALBARANES

INNER JOIN ALBARANES_DETALLE ON ALBARANES.IDAlbaran = ALBARANES_DETALLE.IDAlbaran

WHERE ALBARANES.FechaAlbaran Between @DesdeFecha And @HastaFecha

GROUP BY ALBARANES.IDAlbaran, ALBARANES_DETALLE.IvaVenta) as t2
ON t1.IDALBARAN = t2.IDALBARAN

in QA
exec spCRelacion @date1,@date2
or exec spCRelacion -will be used default parameters

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-04 : 10:28:11

well sproc's are not designed to work in that way.
one way is put the data returned from the sproc in a temp table and then join to the temp table.

create table #temp (column definitions)
insert into #temp
exec YourSProc

if you want to execute a 2nd sproc for each row of data thats in the 1st sproc, than you can't. UDF's are designed to do that.
but usually that isn't necesarry.

maybe if you explained what do you want to do in more detail we can help you better.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources