Styjun

How should Thaumaturgy's "three times as loud as normal" be interpreted?

Proof for integral representation of Lambert W function

Is it right to use the ideas of non-winning designers in a design contest?

Complex conjugate and transpose "with respect to a basis"

Word for something that used to be popular but not anymore

Features seen on the Space Shuttle's solid booster; what does "LOADED" mean exactly?

Template default argument loses its reference type

What happens when a file that is 100% paged in to the page cache gets modified by another process

indexes are not created on localdb

Are fast interviews red flags?

How to finish my PhD?

Do aarakocra have arms as well as wings?

The Green Glass Door, Revisited

How to improvise or make pot grip / pot handle

Yet another calculator problem

What makes things real?

How is the phase of 120V AC established in a North American home?

Friend is very nitpicky about side comments I don't intend to be taken too seriously

Is Sanskrit really the mother of all languages?

is it possible to change a material depending on whether it is intersecting with another object?

Would scoring well on a non-required GRE Mathematics Subject Test make me more competitive?

Contour plot of a sequence of spheres with increasing radius

Owner keeps cutting corners and poaching workers for his other company

How do German speakers decide what should be on the left side of the verb?

How to pass table variable from FunctionA to FunctionB, and return table variable from FunctionB to FunctionA?

How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How do I get list of all tables in a database using TSQL?How to concatenate text from multiple rows into a single text string in SQL server?Select columns from result set of stored procedureSQL update from one Table to another based on a ID matchHow do I UPDATE from a SELECT in SQL Server?Scope of Derived Tables in SQL ServerMust declare the table variableThe multi-part identifier “dbo.showSelectedTable” could not be bound

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

As title,I have written a simple code as following.

functionA : pass the result (table variable) to functionB

functionB : reassign the field name and return the result (table variable) to functionA

create function functionA(
 @Type nvarchar(max) 
 , @isArray bit = 0
)
returns @tempTable table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 declare @tempTableA table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit) 

 insert @tempTableA 
 select 
 *
 from 
 TableA
 where 
 @isArray = 1 and @Type='TypeA'

 insert @tempTable 
 select * from functionB(@tempTableA)

 return 

end
go

create function functionB(
 --How to get the table variable from functionA?
 @tempTableA table
)
returns @tempTableB table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 insert @tempTableB
 select 
 FieldId [Id]
 , FieldName [Name]
 , FieldType [Type]
 , IsArray Array
 from 
 @tempTableA

 return 

end
go

When I expect the functionB will get error message :

The syntax near the keyword 'table' is incorrect.

The data table variable "@tempTableB" must be declared.

The data table variable "@tempTableA" must be declared.

sql-server sql-function

share|improve this question

edited Mar 28 at 7:23

Annie

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

add a comment | 

1

As title,I have written a simple code as following.

functionA : pass the result (table variable) to functionB

functionB : reassign the field name and return the result (table variable) to functionA

create function functionA(
 @Type nvarchar(max) 
 , @isArray bit = 0
)
returns @tempTable table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 declare @tempTableA table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit) 

 insert @tempTableA 
 select 
 *
 from 
 TableA
 where 
 @isArray = 1 and @Type='TypeA'

 insert @tempTable 
 select * from functionB(@tempTableA)

 return 

end
go

create function functionB(
 --How to get the table variable from functionA?
 @tempTableA table
)
returns @tempTableB table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 insert @tempTableB
 select 
 FieldId [Id]
 , FieldName [Name]
 , FieldType [Type]
 , IsArray Array
 from 
 @tempTableA

 return 

end
go

When I expect the functionB will get error message :

The syntax near the keyword 'table' is incorrect.

The data table variable "@tempTableB" must be declared.

The data table variable "@tempTableA" must be declared.

sql-server sql-function

share|improve this question

edited Mar 28 at 7:23

Annie

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

add a comment | 

As title,I have written a simple code as following.

functionA : pass the result (table variable) to functionB

functionB : reassign the field name and return the result (table variable) to functionA

create function functionA(
 @Type nvarchar(max) 
 , @isArray bit = 0
)
returns @tempTable table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 declare @tempTableA table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit) 

 insert @tempTableA 
 select 
 *
 from 
 TableA
 where 
 @isArray = 1 and @Type='TypeA'

 insert @tempTable 
 select * from functionB(@tempTableA)

 return 

end
go

create function functionB(
 --How to get the table variable from functionA?
 @tempTableA table
)
returns @tempTableB table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 insert @tempTableB
 select 
 FieldId [Id]
 , FieldName [Name]
 , FieldType [Type]
 , IsArray Array
 from 
 @tempTableA

 return 

end
go

When I expect the functionB will get error message :

The syntax near the keyword 'table' is incorrect.

The data table variable "@tempTableB" must be declared.

The data table variable "@tempTableA" must be declared.

sql-server sql-function

share|improve this question

edited Mar 28 at 7:23

Annie

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

create function functionA(
 @Type nvarchar(max) 
 , @isArray bit = 0
)
returns @tempTable table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 declare @tempTableA table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit) 

 insert @tempTableA 
 select 
 *
 from 
 TableA
 where 
 @isArray = 1 and @Type='TypeA'

 insert @tempTable 
 select * from functionB(@tempTableA)

 return 

end
go

create function functionB(
 --How to get the table variable from functionA?
 @tempTableA table
)
returns @tempTableB table (
 FieldId nvarchar(4000)
 , FieldName nvarchar(4000)
 , FieldType nvarchar(4000)
 , IsArray bit)
as
begin
 insert @tempTableB
 select 
 FieldId [Id]
 , FieldName [Name]
 , FieldType [Type]
 , IsArray Array
 from 
 @tempTableA

 return 

end
go

sql-server sql-function

sql-server sql-function

share|improve this question

edited Mar 28 at 7:23

Annie

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

share|improve this question

edited Mar 28 at 7:23

Annie

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

asked Mar 28 at 6:52

AnnieAnnie

6799 bronze badges

1 Answer
1

active

oldest

votes

1

Table variable should be READONLY. Let me show an example:

At first you should create User Defined Table type:

CREATE TYPE [dbo].[tp_List] AS TABLE(
 [Val] [int] NULL
)
GO

Then use this table type into your function:

GO 
CREATE FUNCTION FunctionB( 
@TableName tp_List READONLY
)
RETURNS @mt table (a int)
AS
BEGIN
 INSERT INTO @mt
 SELECT * FROM @TableName
 RETURN
END
GO

And usage:

DECLARE @tbl tp_List
INSERT INTO @tbl
(
 Val
)
VALUES
(50)
SELECT * FROM FunctionB(@tbl)

OUTPUT:

a
50

share|improve this answer

edited Mar 28 at 7:07

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Should @tempTableA must be User-Defined Table Types? create type TableType as table (...)
  
  – Annie
  Mar 28 at 7:02
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Annie please, see my updated answer
  
  – StepUp
  Mar 28 at 7:08
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55391702%2fhow-to-pass-table-variable-from-functiona-to-functionb-and-return-table-variabl%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Table variable should be READONLY. Let me show an example:

At first you should create User Defined Table type:

CREATE TYPE [dbo].[tp_List] AS TABLE(
 [Val] [int] NULL
)
GO

Then use this table type into your function:

GO 
CREATE FUNCTION FunctionB( 
@TableName tp_List READONLY
)
RETURNS @mt table (a int)
AS
BEGIN
 INSERT INTO @mt
 SELECT * FROM @TableName
 RETURN
END
GO

And usage:

DECLARE @tbl tp_List
INSERT INTO @tbl
(
 Val
)
VALUES
(50)
SELECT * FROM FunctionB(@tbl)

OUTPUT:

a
50

share|improve this answer

edited Mar 28 at 7:07

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Should @tempTableA must be User-Defined Table Types? create type TableType as table (...)
  
  – Annie
  Mar 28 at 7:02
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Annie please, see my updated answer
  
  – StepUp
  Mar 28 at 7:08
  

  
  
  
  

add a comment | 

1

Table variable should be READONLY. Let me show an example:

At first you should create User Defined Table type:

CREATE TYPE [dbo].[tp_List] AS TABLE(
 [Val] [int] NULL
)
GO

Then use this table type into your function:

GO 
CREATE FUNCTION FunctionB( 
@TableName tp_List READONLY
)
RETURNS @mt table (a int)
AS
BEGIN
 INSERT INTO @mt
 SELECT * FROM @TableName
 RETURN
END
GO

And usage:

DECLARE @tbl tp_List
INSERT INTO @tbl
(
 Val
)
VALUES
(50)
SELECT * FROM FunctionB(@tbl)

OUTPUT:

a
50

share|improve this answer

edited Mar 28 at 7:07

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Should @tempTableA must be User-Defined Table Types? create type TableType as table (...)
  
  – Annie
  Mar 28 at 7:02
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Annie please, see my updated answer
  
  – StepUp
  Mar 28 at 7:08
  

  
  
  
  

add a comment | 

Table variable should be READONLY. Let me show an example:

At first you should create User Defined Table type:

CREATE TYPE [dbo].[tp_List] AS TABLE(
 [Val] [int] NULL
)
GO

Then use this table type into your function:

GO 
CREATE FUNCTION FunctionB( 
@TableName tp_List READONLY
)
RETURNS @mt table (a int)
AS
BEGIN
 INSERT INTO @mt
 SELECT * FROM @TableName
 RETURN
END
GO

And usage:

DECLARE @tbl tp_List
INSERT INTO @tbl
(
 Val
)
VALUES
(50)
SELECT * FROM FunctionB(@tbl)

OUTPUT:

a
50

share|improve this answer

edited Mar 28 at 7:07

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges

CREATE TYPE [dbo].[tp_List] AS TABLE(
 [Val] [int] NULL
)
GO

GO 
CREATE FUNCTION FunctionB( 
@TableName tp_List READONLY
)
RETURNS @mt table (a int)
AS
BEGIN
 INSERT INTO @mt
 SELECT * FROM @TableName
 RETURN
END
GO

DECLARE @tbl tp_List
INSERT INTO @tbl
(
 Val
)
VALUES
(50)
SELECT * FROM FunctionB(@tbl)

a
50

share|improve this answer

edited Mar 28 at 7:07

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges

answered Mar 28 at 6:55

StepUpStepUp

10.4k1010 gold badges4646 silver badges8585 bronze badges