How to use the EXEC command

Question

I am trying to get a result from a database that will be selected after knowing the country of a user given his email.

I tried using EXEC() but now I get too many results from different users when I clearly indicate that I only want the result from certain user in the 'where' clause.

I first tried making the query with pure inner joins but it failed, it indicated that there was a syntax error, but if i ran it separately without the exec it worked.

After that I decided to use sub-queries, but as I mentioned above, it is returning all of the values, as if it wasn't considering the 'where'

What am I doing wrong?

Here is a sample of the query:

DECLARE @email nvarchar(150) = 'name.lastname@mx.company.com'

        --Getting the country code of user
        DECLARE @country_code nvarchar(3) =  (SELECT country_code FROM general.countries WHERE id_country = (SELECT fk_country FROM databases_access.staff WHERE email = @email))

        --Setting user database to search for job title & department
        DECLARE @dbname NVARCHAR(25)
        SET @dbname = 'dsv_global_' + @country_code

        Declare @query nvarchar(500)

        -- Query to be run to to get the user department and job title
        SET @query =

        '
            USE '+@dbname+'

            SELECT
                id_staff,
                email,
                (SELECT complete_name_dept FROM dsv_global.departments WHERE id_department = fk_department),
                (SELECT CONCAT(title,'' '',description) FROM dsv_global.job_titles WHERE id_job_title = (SELECT fk_title FROM dsv_global.staff_information WHERE fk_staff = id_staff)) ,
    (SELECT COUNT(fk_staff) FROM dsv_global.staff_managers WHERE fk_manager = fk_staff)
            FROM dsv_global.staff
            WHERE email = '''+@email+''' AND status = ''ACTIVE''
    '

        ----Storing department & title from user in temp table
        --DECLARE @user_info TABLE (id_staff int, email nvarchar(200),complete_name_dept nvarchar(100), title nvarchar(200),num_of_errors int)
        --INSERT INTO @user_info
        EXEC(@query)

Edit:

I expect to receive:

But I receive:

Answer 1

The problem is that i was not assigning enough space to store the query inside a string :

I had:

Declare @query nvarchar(500)

i changed it to :

Declare @query nvarchar(500)

Answer 2

It's worth to use Common Table Expressions when you deal with complex queries. You can put WITH to define a temporary named result set that available temporarily in the execution scope of a statement. And, by another hand, put GROUP BY for your COUNT function. Also, you need to put id_staff inside the WITH block, it looks like:

     WITH cte_titles ( job_title)
     AS (
         SELECT CONCAT(title,'' '',description) 
         FROM dsv_global.job_titles 
         WHERE id_job_title IN 
                    (SELECT fk_title 
                     FROM dsv_global.staff_information
                     WHERE fk_staff = id_staff)
        ),
        cte_staff (count_staff) AS 
        (
         SELECT COUNT(fk_staff) 
         FROM dsv_global.staff_managers 
         WHERE fk_manager = fk_staff
         GROUP BY fk_staff
        )

       SELECT
            dsv.id_staff,
            dsv.email,
            job_title,
            count_staff,
       FROM dsv_global.staff dsv
            cte_staff,
            cte_titles
       WHERE email = '''+@email+''' AND status = ''ACTIVE''