locked
Odd input param question RRS feed

  • Question

  • Not sure if this is possible, but I need to run an INSERT Statement based upon a SELECT of another table.  Now, based upon the value of an input param, I need to include a specific field in that SELECT portion of the INSERT statement.  That is, I need to specify a Field based upon the input param value.

    Any input is appreciated!

    Something like:

    ALTER PROCEDURE blah
    @inValue
    
    CASE WHEN @inValue='cs' THEN
               INSERT tab1(fld1)
               SELECT tab2.CS
               FROM tab2
             WHEN @inValue='us' THEN
               INSERT tab1(fld1)
               SELECT tab2.US
               FROM tab2
    END
    
    
    

    jfc
    Friday, December 18, 2009 2:42 PM

Answers

  • Yes, you can do that. Have in mind that CASE expression returns a scalar value of a single data type and column data types must be compatible.


    ALTER PROCEDURE blah @inValue CHAR(2)
    AS INSERT INTO tab1(fld1) SELECT CASE WHEN @inValue = 'cs' THEN tab2.CS WHEN @inValue = 'us' THEN tab2.US END FROM tab2 WHERE @inValue IN ('cs', 'us');




    Plamen Ratchev
    • Proposed as answer by Naomi N Friday, December 18, 2009 3:03 PM
    • Marked as answer by Zongqing Li Thursday, December 24, 2009 8:28 AM
    Friday, December 18, 2009 2:49 PM

All replies

  • eh.  I can just use an IF...ELSE to get this done.

    But open to other ideas.
    jfc
    Friday, December 18, 2009 2:49 PM
  • Yes, you can do that. Have in mind that CASE expression returns a scalar value of a single data type and column data types must be compatible.


    ALTER PROCEDURE blah @inValue CHAR(2)
    AS INSERT INTO tab1(fld1) SELECT CASE WHEN @inValue = 'cs' THEN tab2.CS WHEN @inValue = 'us' THEN tab2.US END FROM tab2 WHERE @inValue IN ('cs', 'us');




    Plamen Ratchev
    • Proposed as answer by Naomi N Friday, December 18, 2009 3:03 PM
    • Marked as answer by Zongqing Li Thursday, December 24, 2009 8:28 AM
    Friday, December 18, 2009 2:49 PM
  • This will work fine.....................


    ALTER PROCEDURE blah @inValue CHAR(2)
    AS
      INSERT INTO tab1(fld1)
      SELECT CASE @inValue 
                      WHEN 'cs' THEN tab2.CS
                      WHEN 'us' THEN tab2.US
             END
      FROM tab2
      WHERE @inValue IN ('cs', 'us');
    Friday, December 18, 2009 2:58 PM
  • thanks for the replies.  it all helps!
    jfc
    Friday, December 18, 2009 3:09 PM