locked
Using subquery to find ID in stored procedure RRS feed

  • Question

  • User-718146471 posted

    Ok, I am sure I have this goofed up somehow and it is probably something simple. My if not exists query looks like this:

    	INSERT INTO CVEDetails (CVENumber,VendorID,SoftwareID,BucketScenario,AppTypeID,CCB,CVSSBase,Severity,Resolution)
    	values (@CVENumber,(SELECT CVEID from CVEDetails where @CVENumber = CVENumber),
    	(SELECT VendorID from Vendors where VendorName = @VendorName),
    	(SELECT SoftwareID from Software where SoftwareName = @SoftwareName),
    	(SELECT bsID from BucketScenarios where BSName = @BucketScenario),
    	(SELECT AppTypeID from AppTypes where AppTypeName = @AppType),
    	@CCB, @cvss_base,
    	(SELECT SeverityID from Severity where SeverityName = @LevelVal),
    	@summary) WHERE NOT EXISTS (SELECT CVENumber from CVEDetails WHERE CVENumber = @CVENumber);
    

    It is complaining about WHERE saying incorrect syntax near 'WHERE'. Can someone help me figure this out please? Thank you!

    Monday, September 26, 2016 7:31 PM

Answers

  • User-1672470423 posted

    I think you want something like below:

    I just commented one extra column as you can see in third line and used select for selecting rows. Try to check by commenting insert and checking the resultset.

    INSERT INTO CVEDetails (CVENumber,VendorID,SoftwareID,BucketScenario,AppTypeID,CCB,CVSSBase,Severity,Resolution)
        SELECT @CVENumber,--(SELECT CVEID from CVEDetails where @CVENumber = CVENumber),
        (SELECT VendorID from Vendors where VendorName = @VendorName),
        (SELECT SoftwareID from Software where SoftwareName = @SoftwareName),
        (SELECT bsID from BucketScenarios where BSName = @BucketScenario),
        (SELECT AppTypeID from AppTypes where AppTypeName = @AppType),
        @CCB, @cvss_base,
        (SELECT SeverityID from Severity where SeverityName = @LevelVal),
        @summary WHERE NOT EXISTS (SELECT CVENumber from CVEDetails WHERE CVENumber = @CVENumber);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 26, 2016 8:25 PM