When you
create a dynamic query and execute it. You want to get a result or value in
variable then it is not possible when you use EXEC (@SQL).
You can achieve it by using EXEC SP_EXECSQL,
- DECLARE @SQLCOMMAND NVARCHAR(1000)
- DECLARE @NAME VARCHAR(75)
- DECLARE @COUNTS INT
- SET @NAME = 'TEST'
- SET @SQLCOMMAND = 'SELECT @CNT=COUNT(*) FROM MSTEMPLOYEE WHERE FIRST_NAME = @NAME'
- EXECUTE SP_EXECUTESQL @SQLCOMMAND, N'@NAME NVARCHAR(75),@CNT INT OUTPUT', @NAME = @NAME, @CNT=@COUNTS OUTPUT
- SELECT @COUNTS AS COUNTS