How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans