locked
Having An Issue With Variable in SELECT Statement... RRS feed

  • Question

  • I'm using a cursor to insert records into a table.  For some reason, when I use one of the variables in the SELECT statement, it gets inserted into the target table literally.  Code below:

    DECLARE @WellID nvarchar(50)
    DECLARE cur_WellID CURSOR FOR
    	SELECT WellID FROM tblHistWellID
    
    OPEN cur_WellID
    FETCH NEXT FROM cur_WellID
    INTO @WellID
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	INSERT INTO tblAnalyteData_historical(WellID, AnalyteID, MeasuredValue, Qualifier, Units, AnalysisDate, DBComments)
    	SELECT @WellID, '14795-55-8', @WellID, null, null, SampleDate, 'Unvalidated data from OCI CD'
    	FROM tmp_Nitrate
    	
    	FETCH NEXT FROM cur_WellID
    	INTO @WellID
    END
    
    CLOSE cur_WellID
    DEALLOCATE cur_WellID
    GO
    

    In the SELECT statement I have:

    @WellID, '14795-55-8', @WellID, null, null, SampleDate, 'Unvalidated data from OCI CD'
    

    My problem is that the first instance of @WellID I want to literally be the value that is assigned to it (example "A123").  The second instance, I don't want the literal value, I want to use it as part of the actual SELECT statement.  But it doesn't work.  To help explain, I will give a second example.  Let's assume the current value of @WellID is "A123":

     

    SELECT @WellID, '14795-55-8', @WellID, null, null, SampleDate, 'Unvalidated data from OCI CD'
    FROM tmp_Nitrate
    Results: A123, 14795-55-8, A123, null, null, 1/1/2001, Unvalidated data
    
    SELECT @WellID, '14795-55-8', A123, null, null, SampleDate, 'Unvalidated data from OCI CD'
    FROM tmp_Nitrate
    Results: A123, 14795-55-8, 16.5, null, null, 1/1/2001, Unvalidated data
    

    Note in the second set of results, I have the value from the column A123 in the tmp_Nitrate table instead of @WellID.

    Am I making any sense?  I want the first instance of @WellID to be the literal value, the second to be the equivalent of if I actually hard coded the column name I was selecting in.

    I just confused myself.  I hope someone out there can understand me LOL

    Tuesday, April 5, 2011 7:22 PM

Answers

  • You will need dynamic sql, because the name of the column is not known in advance.

    declare @sql nvarchar(max);
    ...
    set @sql = N'SELECT @WellID, ''14795-55-8'', '  + quotename(@WellID) + N', null, null, SampleDate, ''Unvalidated data from OCI D'' FROM tmp_Nitrate';

    INSERT INTO tblAnalyteData_historical(WellID, AnalyteID, MeasuredValue, Qualifier, Units, AnalysisDate, DBComments)
    exec sp_executesql @sql, N'@WellID nvarchar(50)', @WellID;
    ...

     


    AMB

    Some guidelines for posting questions...



    • Edited by HunchbackMVP Tuesday, April 5, 2011 8:01 PM
    • Marked as answer by Jay Mazz Tuesday, April 5, 2011 10:29 PM
    Tuesday, April 5, 2011 7:31 PM

All replies

  • You will need dynamic sql, because the name of the column is not known in advance.

    declare @sql nvarchar(max);
    ...
    set @sql = N'SELECT @WellID, ''14795-55-8'', '  + quotename(@WellID) + N', null, null, SampleDate, ''Unvalidated data from OCI D'' FROM tmp_Nitrate';

    INSERT INTO tblAnalyteData_historical(WellID, AnalyteID, MeasuredValue, Qualifier, Units, AnalysisDate, DBComments)
    exec sp_executesql @sql, N'@WellID nvarchar(50)', @WellID;
    ...

     


    AMB

    Some guidelines for posting questions...



    • Edited by HunchbackMVP Tuesday, April 5, 2011 8:01 PM
    • Marked as answer by Jay Mazz Tuesday, April 5, 2011 10:29 PM
    Tuesday, April 5, 2011 7:31 PM
  • Hi Hunchback,

    When I tried I got the following error:

    Msg 8178, Level 16, State 1, Line 1

    The parameterized query '(@WellID nvarchar(50))SELECT @WellID, '14795-55-8', [EW03], null' expects the parameter '@WellID', which was not supplied.

    Tuesday, April 5, 2011 7:37 PM
  • Sorry, I forgot to pass value to the parameter. I corrected my post already.

    exec sp_executesql @sql, N'@WellID nvarchar(50)', @WellID;

     


    AMB

    Some guidelines for posting questions...

    Tuesday, April 5, 2011 8:02 PM
  • That did the trick!  Thank you so much!
    Tuesday, April 5, 2011 10:29 PM