question

SigmaZX-3500 avatar image
0 Votes"
SigmaZX-3500 asked SigmaZX-3500 commented

Adding a tuple from a function to table

Hi,
I am creating a function and I want it to add a tuple from the function to a table-as the question says above :'D-
I got the function right but I am missing something...I am missing the part of
RETURNS
What should I write after RETURNES keyword ? I want to add a tuple -let's say I want to add The Employee_id , Employee_name, Spec-
How can I return these values into the table ?
Thanks in advance,

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered SigmaZX-3500 commented

I'm not sure that I understand your question. In your function definition you insert any data to a table, but you can do that when you use the function.

Here is an example of an inline table-valued function:

CREATE FUNCTION dbo.inline(@n int) RETURNS TABLE AS
RETURN (SELECT object_id, name FROM sys.objects WHERE object_id % @n = 0)

Here is the same function as a multi-statement function:

CREATE FUNCTION dbo.multistmt(@n int) 
RETURNS @t TABLE (object_id int NOT NULL,
                  name      sysname NOT NULL) AS
BEGIN
   INSERT @t(object_id, name)
     SELECT object_id, name FROM sys.objects WHERE object_id % @n = 0
   RETURN
END

Here are examples of calling them and inserting data into a table:

CREATE TABLE #test (object_id int NOT NULL,
                    name      sysname NOT NULL)
go
INSERT #test (object_id, name)
   SELECT object_id, name FROM dbo.inline(5)
INSERT #test (object_id, name)
   SELECT object_id, name FROM dbo.multistmt(7)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks sir,
I will try it ,

0 Votes 0 ·