Examples using Table Variables
-
I'm wanting to load a table variable with a query result set ... and then pass the table variable to a function. I've found a few examples for the table variable setup on the internet, but I'm still unable to get my tests to work.
If anyone has some samples of using table variables, the help would be greatly appreciated. Specifically, I'm trying to load the table variable with multiple columns from a select statement.
Thanks
Norm Johnson
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"
-
quote:
I'm wanting to load a table variable with a query result set ... and then pass the table variable to a function. I've found a few examples for the table variable setup on the internet, but I'm still unable to get my tests to work.If anyone has some samples of using table variables, the help would be greatly appreciated. Specifically, I'm trying to load the table variable with multiple columns from a select statement.
It might be helpful if you post what you have so far so we can figure out.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
I'm wanting to load a table variable with a query result set ... and then pass the table variable to a function. I've found a few examples for the table variable setup on the internet, but I'm still unable to get my tests to work.If anyone has some samples of using table variables, the help would be greatly appreciated. Specifically, I'm trying to load the table variable with multiple columns from a select statement.
Thanks
Norm Johnson
If you are talking User Defined Function here then the answer is you cannot pass a table varible in.
I you look in SQL BOL at topic "CREATE FUNCTION"
it gives the examples like so
Scalar Functions
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,] ...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Inline Table-valued Functions
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
Multi-statement Table-valued Functions
CREATE FUNCTION [ owner_name. ] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
< function_option > ::=
{ ENCRYPTION | SCHEMABINDING }
< table_type_definition > :: =
( { column_definition | table_constraint } [ ,...n ] )and list the parameter key to input variable types to be defined like so.
quote:
scalar_parameter_data_typeIs the parameter data type. All scalar data types, including bigint and sql_variant, can be used as a parameter for user-defined functions. The timestamp data type and user-defined data types are not supported. Nonscalar types such as cursor and table cannot be specified.
as you can see because TABLE variables are non-scalar you cannot use it as an input variable.
-
THANK YOU to ALL of you! What a great and rapid response!
After I sent my cry for help, I found another person's question where he was using a table variable (successfully) and was trying to resolve some other problem. Using his functional code, I worked up the model shown below.
I really appreciate your feedback. Thanks again.
Norm Johnson
/* —START— */
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '20030701'
SET @EndDate = '20030730'
DECLARE @TABLEVAR table
(
ClientID int
, DivisionID int
, Status varchar(1000)
, StartDate datetime
, EndDate datetime
)
BEGIN
INSERT INTO @TABLEVAR
/* -- Load table variable from Period (@StartDate, @EndDate).*/
SELECT
/* columns to select -- it is not the column name, but the column order
that relates the "select columns" to the columns in the
"declare table (row1, row2, etc)" above. */
ClientIdnt
, DivisionIdnt
, ClientStatus
, StartDate
, CompletionDate
FROM ClientView CV
WHERE (DivisionIdnt = 17)
AND (Active = '6')
AND (ClientStatus <> 'Pending')
AND (IsDate(StartDate) = 1)
AND (IsDate(CompletionDate) = 1)
AND (CompletionDate BETWEEN @StartDate AND @EndDate)
END
select * from @TABLEVAR
/* —END— */
Norm Johnson
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!" -
This posting was intended for the T-SQL group, and somehow I put it here.
Since table variables cannot be used as inputs to functions (Antares686's reply above), the query has been changed INTO the following user defined function and gives the desired result:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
DROP FUNCTION dbo.udf_PctAppsInBusinessDays
CREATE FUNCTION dbo.udf_PctAppsInBusinessDays
-- %%%%%%%%%%%%%%%%
-- Accept parameters StartDate, EndDate, BoardIdnt, and DaysMax
-- Return AppsTotal, AppsInBDays, and AppsPercent
-- %%%%%%%%%%%%%%%%
(
@StartDate DATETIME
, @EndDate DATETIME
, @BoardIdnt INT = 10
, @DaysMax INT = 8
)
RETURNS
@retAppCounts TABLE
( AppsInBusinessDays int
, AppsTotal int
, AppsPercent decimal(5,2)
)
AS
BEGIN
DECLARE @AppsInBusinessDays int
DECLARE @AppsTotal int
DECLARE @AppsPercent decimal(5,2)
DECLARE @TABLEVAR table
(
CredentialIdnt bigint
, DivisionIdnt int
, Status varchar(1000)
, StartDate datetime
, EndDate datetime
, BusinessDays int
)
INSERT INTO @TABLEVAR
/* -- Load table variable with data from Period (@StartDate, @EndDate).*/
SELECT
/* columns to select -- the COLUMN ORDER (not the Column Name)
relates the "select columns" to the columns in the
"declare table (column1, column2, etc)" above. */
WFV.CredentialIdnt
, WFV.DivisionIdnt
, WFV.WorkFlowStatus
, WFV.StartDate
, WFV.CompletionDate
, cast(dbo.udf_GetBusinessDays(WFV.StartDate,WFV.CompletionDate) AS INT )
FROM WorkFlowView WFV
INNER JOIN CredentialView CRV
ON WFV.CredentialIdnt = CRV.CredentialIdnt
INNER JOIN Division DIV
ON CRV.DivisionIdnt = DIV.DivisionIdnt
WHERE (WFV.DivisionIdnt = @BoardIdnt)
AND (WFV.Active = '6')
AND (WFV.WorkFlowStatus <> 'Pending')
AND (IsDate(WFV.StartDate) = 1)
AND (IsDate(WFV.CompletionDate) = 1)
AND (WFV.CompletionDate BETWEEN @StartDate AND @EndDate)
/* SET return values */
SET @AppsInBusinessDays = (select COUNT(*) from @TABLEVAR
where (BusinessDays <= @DaysMax ) /* 8 days default */)
SET @AppsTotal = ( select COUNT(*) from @TABLEVAR)
SET @AppsPercent=( (@AppsInBusinessDays*1.0) / @AppsTotal )*100
INSERT @retAppCounts
SELECT
AppsInBusinessDays = @AppsInBusinessDays
,
AppsTotal = @AppsTotal
,
AppsPercent = @AppsPercent
RETURN
END
/* <<<<SAMPLE FUNCTION CALL: >>>>
SELECT *
FROM dbo.udf_PctAppsInBusinessDays(startDate,EndDate,10,8)
which returns 3 values: AppsInBusinessDays (int)
, AppsTotal (int)
, AppsPercent (decimal(5,2))
--*/
GO
My thanks to the respondents ianscarlett, Frank Kalis, and Antares686. They steered me in the right direction.
Norm Johnson
Norm Johnson
"Keep smiling ... it gives your face something happy to do
... and it makes people wonder what you're up to!"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply