Search code examples
sqlsql-servert-sqlplsqlsqlcmd

Using value of one cell in a select statement (running with sqlcmd)

EDIT: My mistake was that I had a "go" after the first select statement and that would cause @variableName to go out of scope.

I have a bunch of queries that depend on each other and I want to use the value that's returned by the first one in another one. For example I want to use the product ID of the first query, : 

SELECT ProductID,
FROM mproducts 
WHERE (ProductCode='$(product)' 
    or ProductCode='$(product)' 
    or BuildSysProductCode='$(product)');

in the second one

SELECT ProductVersionID, 
FROM cb_mproductversions
WHERE ProductID=***variableName***
go

I looked into variables, and I've seen a few ways of storing values into variables like doing 

DECLARE @variableName
SELECT @variableName AS productID 
FROM mproducts 
...

But it seems that the scope of @variableName is only the next select statement, and when I get to the ones after the one right after DECLARE it says that it's not declared.

Solution

  • Use it like this

    DECLARE @variableName int
SELECT @variableName = productID FROM mproducts WHERE .. 

SELECT ProductVersionID
FROM cb_mproductversions
WHERE ProductID = @variableName