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!"
       

  • You can insert into a table variable using a select statement in the same way you insert into a normal table, e.g.

    declare @tab table(objid int,name sysname)

    insert @tab select id,name from sysobjects

    select * from @tab

  • 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

    http://www.insidesql.de

    --
    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_type

    Is 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 @StartDatedatetime

    DECLARE @EndDatedatetime

    SET @StartDate = '20030701'

    SET @EndDate = '20030730'

    DECLARE @TABLEVAR table

    (

    ClientIDint

    , DivisionIDint

    , Statusvarchar(1000)

    , StartDatedatetime

    , EndDatedatetime

    )

    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

    ,@BoardIdntINT = 10

    ,@DaysMaxINT = 8

    )

    RETURNS

    @retAppCounts TABLE

    (AppsInBusinessDaysint

    ,AppsTotalint

    ,AppsPercentdecimal(5,2)

    )

    AS

    BEGIN

    DECLARE @AppsInBusinessDays int

    DECLARE @AppsTotalint

    DECLARE @AppsPercentdecimal(5,2)

    DECLARE @TABLEVAR table

    (

    CredentialIdntbigint

    , DivisionIdntint

    , Statusvarchar(1000)

    , StartDatedatetime

    , EndDatedatetime

    ,BusinessDaysint

    )

    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