0

In here I've declared two variables, one is for writing a query, the other is to store the output.

declare @EM_NUMBER nvarchar(100)
declare @SQL nvarchar(max)

set @SQL='select vendor.VD_TABLE_KEY_VALUE 
          from [HS_EM_VENDOR_DATA_MAP] as vendor 
          inner join [HS_HR_EMPLOYEE] as emp on vendor.VD_TABLE_KEY_VALUE = emp.EMP_NUMBER
          where vendor.VD_TABLE_NAME = ''HS_HR_EMPLOYEE''
            and vendor.VD_TENANT_UNIQUE_ID = ' + @VD_TENANT_UNIQUE_ID + '
            and emp.HIE_CODE_' + @DEF_LEVEL + ' = ' + @HIE_CODE + '
            and vendor.VD_TABLE_REF_VALUE = ' + @XeroEmpNumber

Once I execute this @SQL output something like this

EXEC sp_executesql @SQL

Output:

enter image description here

I want to store this output into @EM_NUMBER variable

Asanka Yaparathna
  • 25
  • 6
  • 1
    Does this answer your question? [How to get sp\_executesql result into a variable?](https://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) – Hozikimaru Sep 29 '21 at 05:39
  • Careful, the above is a massive security vulnerability. You need to fix that huge injection issue you have by parametrising your parameters (seems silly to say, but you're not) and safely injecting dynamic object names with `QUOTENAME`. – Thom A Sep 29 '21 at 07:06

0 Answers0