Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121

    Unanswered: Function Problem (Sorry thats not very descriptive)

    Hiya Peeps,
    Got a basic update table:

    CREATE PROCEDURE CraigSource2Dest AS
    INSERT INTO TCraigDest(
    MyCertN
    ,MyResult
    ,MySpec)
    SELECT
    TOP 10 dbo.TCertResults.ICertResultsN
    ,dbo.TCertResults.ICertValue
    ,dbo.fx_source2dest(dbo.TCertResults.ICertValue)
    FROM dbo.TCertResults
    GO

    As you can see theres a function in the middle of select statement, that returns a value based on what passed into it. The procedure above works fine.

    The problem I have is this, can I use the value that the function returns later in the SELECT statement. As an example lets say the table had 4 columns and in the fourth column I wanted to insert the value that the function returned + the word 'TEST'.

    I've tried 2 methods of doing this the first :
    CREATE PROCEDURE CraigSource2Dest AS
    INSERT INTO TCraigDest
    (
    MyCertN,
    MyResult,
    MySpec,
    MyDerivedColumn
    )
    SELECT TOP 10 dbo.TCertResults.ICertResultsN
    ,dbo.TCertResults.ICertValue
    ,dbo.fx_source2dest(dbo.TCertResults.ICertValue) AS MyNewValue
    ,MyNewValue + 'TEST'
    FROM dbo.TCertResults
    GO

    When I try this I get the error that MyNewValue is not a valid column name.

    And the 2nd method was to try to assign the value returned from the function to a variable. Something like this

    CREATE PROCEDURE CraigSource2Dest AS
    DECLARE @MyNewValue AS VARCHAR(65)
    INSERT INTO TCraigDest
    (
    MyCertN
    ,MyResult
    ,MySpec
    ,MyDerivedColumn)
    SELECT TOP 10 dbo.TCertResults.ICertResultsN
    ,dbo.TCertResults.ICertValue
    ,@MyNewValue = dbo.fx_source2dest(dbo.TCertResults.ICertValue)
    ,@MyNewValue + 'TEST'
    FROM dbo.TCertResults
    GO

    This way gives me ERROR 141 : A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    Bit stumped really up to the point that I don't even know what to search for to look for help.

    Any help would be grand.

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i dont believe that you can the functions' alias in the code as a valid column name ( it doesn't exist at this point)
    Try calling the function explicitly + ' Test'
    like this

    CREATE PROCEDURE CraigSource2Dest AS
    INSERT INTO TCraigDest
    (
    MyCertN,
    MyResult,
    MySpec,
    MyDerivedColumn
    )
    SELECT TOP 10 dbo.TCertResults.ICertResultsN
    ,dbo.TCertResults.ICertValue
    ,dbo.fx_source2dest(dbo.TCertResults.ICertValue) + 'TEST'
    FROM dbo.TCertResults
    GO

    hope it works i didnt test it
    Last edited by Ruprect; 03-11-04 at 13:22.

  3. #3
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    The problem is that I want to insert dbo.fx_source2dest(dbo.TCertResults.ICertValue) into 1 field and dbo.fx_source2dest(dbo.TCertResults.ICertValue) + 'TEST' into another but I don't want to have to call the function twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •