How to pass in variable/object from Select into Begin statement

Question

I have the below code which im trying to convert to its SQL equivalent:

var products = dc.Products.FirstOrDefault(p=> p.id == id && p.Active);

if(products !=null)
{
return dc.Products(p=> p.id == id && p.Active && p.ProductCompanyId == products.CompanyId);
}

I managed to get the below in SQL

Declare @id

If exists(select * from products where id = @id and active=1)
Begin
Select * from products where id=@id and active = 1 and secondaryId = XXXXXXXXXX
End

Im struggling to make the last part of the statement meet the above criteria which is getting the object from the products and then passing in the secondaryId under the BEGIN statement.

Could anyone point me towards the right direction?

Answer 1

in the C# code there is no secondaryId column it uses CompanyId and it matches to ProductCompanyId.

direct translation:

declare @id int;

declare @companyId int = (select top 1 CompanyId from Products where id = @id and Active = 1);
if @@rowcount > 0 
begin
  select *
  from Products 
  where id = @id and Active = 1 and ProductCompanyId = @companyId
end

and with ties:

declare @id int;

select top(1) with ties 
from Products 
where id = @id and Active = 1 and ProductCompanyId = companyId
order by companyId

Answer 2

I think that you can use a single statement without if:

select top(1) with ties *
from products
where active = 1
and id = @id
order by secondaryId

If you are interested in a more direct equivalent:

declare @secondaryId int = (select top(1) secondaryId from products where id = @id and active = 1)

if @secondaryId is not null
begin
    select * 
    from products
    where id = @id and active = 1 and secondaryId = @secondaryId
end

The if line can be omitted.

Answer 3

when passing a variable or object from a SELECT statement into a BEGIN block in SQL, the approach might vary based on the specific database system you're using. However, in general, you can use variables to store the result from a SELECT query and then utilize those variables within a BEGIN block.

For instance, in PostgreSQL, you might use the SELECT INTO statement to assign the result of a query to a variable. Here's an example:

sql
Copy code
DO $$ 
DECLARE 
    my_variable INTEGER;
BEGIN
    SELECT column_name INTO my_variable FROM your_table WHERE condition;
    -- Use my_variable or perform operations based on its value within this block
    -- For example: 
    RAISE NOTICE 'The value of my_variable is %', my_variable;
END $$;
Replace column_name, your_table, and condition with the appropriate column, table, and condition from your database.

In other database systems like MySQL or SQL Server, you might use different syntax or constructs to achieve a similar result, but the principle remains the same: storing the result of a SELECT query into a variable and then utilizing that variable within a BEGIN block or a stored procedure.

If you provide more details about the specific database system you're using or the context of your query, I can offer more precise guidance like <a href="