Post reply

Passing a Table Column name to a Multistatement Ta

  • December 29, 2003 at 5:29 am

    #81765

    Hi all,

    Can an entire column name be paased as an input parameter to a multistatement table-valued UDF in order to populate another table with the result?

    Example scenario:

    I have a UDF (Fxn_StringComponents) that accepts 2 parameters: @delimitedString and @Delimiter and returns a @tableVariable (RowID, DataComponents).

    I also have 2 tables: #Test1(DelimitedString VARCHAR(255) not null) and #Test2(Val1 VARCHAR(20) null,Val2 VARCHAR(5) null, Val3 VARCHAR(10) null).

    ----------------------

    Sample Data:

    INSERT INTO #Test1(DelimitedString )

    SELECT '633345788213445255,01,0704'

    UNION ALL

    SELECT '633345788213611,9,1205'

    UNION ALL

    SELECT '49254578821363334,,0704'

    -----------------------

    I want to be able to insert the result from passing DelimitedString column to my UDF into #Test2 as follows:

    Val1 Val2 Val3

    ------------------------------- --------- --------

    633345788213445255 01 0704

    633345788213611 9 1205

    49254578821363334 <null> 0704

    Is this feasible and how do I achieve this?

    PS: I have successfully done this by "cursoring" through #Test1 data. Just wondered whether I could just do a Table/Column -wise pass.

    Thanks in advance.

  • December 29, 2003 at 5:32 am

    #487922

    Is this http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1092 going your direction?

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2003 at 6:29 am

    #487929

    Thanks Frank.

    Not quite what I want. The link provides a solution similar to what I have just now. My main question is whether it is possible to pass an entire column as the parameter to a the UDF. So instead of doing

    SELECT * FROM dbo.Fnx_UDFName(@List,@Delim)

    I want to replace the @list with a table column name.

    Thanks in advance.

  • December 29, 2003 at 6:49 am

    #487935

    Sorry!

    I have little experience with UDF's, but can't you pass the column name to your function, build there dynamically your statement and execute it via sp_executeSQL?

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply