Execute a function from a query

Question

Execute a function from a query

krypto69

SSChampion

Points: 13609
More actions
August 6, 2013 at 1:09 pm

#280885

Hi,
I have this function that is used to fill a SQL table - which is then used for a data export.
ALTER FUNCTION [dbo].[itfAPVEND]
(
-- Add the parameters for the function here
)
RETURNS
@APVEND TABLE
([CO] [char](5) NOT NULL,
[A] [char](12) NOT NULL,
[char](60) NULL,
[C] [char](30) NULL,
[D] [char](30) NULL,
[E] [char](30) NULL,
[F] [char](30) NULL,
[G] [char](20) NULL,
[H] [char](20) NULL,
[char](20) NULL,
[J] [char](20) NULL,
[K] [text] NULL,
[L] [char](15) NULL
)
AS
BEGIN
insert into @APVEND
select 'WSIAP', * from
openquery(WSIAP, 'select VendorKey, Vendorname, ContactName,
Vendoraddress1, Vendoraddress2, Vendoraddress3,
VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND')
insert into @APVEND
select 'ASSFD', * from
openquery(ASSFD, 'select VendorKey, Vendorname, ContactName,
Vendoraddress1, Vendoraddress2, Vendoraddress3,
VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND')
RETURN;
END
Problem is - I now need to execute this function every night. What is the best way to accomplish this? I was thinking of using a SQL job and call it from a query. BUt I can't seem to get the function to execute from a query. How would I execute this function from a query?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

select * from dbo.itfAPVEND()

Be careful here though. You have a table valued function but it is a multi-statement function. The performance can end up even worse than scalar functions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

krypto69 SSChampion Points: 13609 More actions · Answer 2

Sean,

Running that returns records..but doesn't exec and fill the vendor table.

krypto69 SSChampion Points: 13609 More actions · Answer 3

If I do this it works..

insert into Vendors

SELECT * FROM dbo.itfAPVEND()

Thanks for your help Sean!

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

Sean Lange (8/6/2013)
select * from dbo.itfAPVEND()
Be careful here though. You have a table valued function but it is a multi-statement function. The performance can end up even worse than scalar functions.

Your function only has 2 statement and they both insert into the local table variable. There is no insert into a vendors table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

krypto69 (8/6/2013)
If I do this it works..
insert into Vendors
SELECT * FROM dbo.itfAPVEND()
Thanks for your help Sean!

Glad you got it working. I must have been typing my last response when you posted this. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/