Assigning values returned by function in Dynamic SQL

Question

I am trying to generate a dynamic SQL to assign variables with values from a function. I am trying to generate SQL something like

Select @StartDate=@EID_Dept 
from dbo.WorkHistory(@today,@EID )

The function returns a date (@EID_dept will be the name of the column returned by the function) which I need to assign to @StartDate. And @EID_Dept is generated by concatenating @EID and @dept.

If I manually write the SQL it would appear as

Select @StartDate = amaan_IT 
from dbo.WorkHistory('2016-10-10', amaan)

My code is below:

DECLARE @EID varchar(5), @StartDate VARCHAR(MAX), 
        @today DATETIME, @dept VARCHAR(10), @EID_dept varchar(20);

Select @today = SYSDATETIME()
Select @dept = dept from dbo.Dept(@EID)
Select @EID_Dept = CONCAT(@EID, @dept)

DECLARE @SQL Varchar(max);
SET @SQL = N'Select @StartDate = @EID_Dept 
             from dbo.PeriodHistory(@today, @EID)';

EXEC Sp_executesql
  @SQL,
  N'@StartDate VARCHAR(MAX) out,@EID_dept varchar(max),@today datetime,@EID Varchar',
  @StartDate out,
  @EID_Dept=@EID_Dept,
  @today=@today

Why are you using dynamic sql at all? It is not doing anything for you except making this more difficult. — Sean Lange, Oct 25 '16 at 16:38
You cannot assign object names using variables, you would need to use `SET @SQL = CONCAT(N'SELECT @StartDate = ', @EID_Dept, ' FROM dbo.PeriodHistory(@Today, @EID);`). This seems far from ideal though, I am not convinced that you are doing this the best way. — GarethD, Oct 25 '16 at 16:41
The EID will be changing and I need to concatenate EID n Dept to get the data from the function. Just EID will be passed , using EID I need to find out Dept and then concatenate both. — Amaan Khan, Oct 25 '16 at 16:41
Possible duplicate of [How to get sp\_executesql result into a variable?](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) — Lucero, Oct 25 '16 at 16:52

score 1 · Answer 1 · answered Oct 26 '16 at 08:44

I changed your query a little bit. Hope this will work fine:

DECLARE @EID nvarchar(5), 
        @StartDate nvarchar(MAX), 
        @today datetime = SYSDATETIME(), 
        @dept nvarchar(10), 
        @EID_dept nvarchar(20),
        @SQL nvarchar(max),
        @params nvarchar(max) = N'@StartDate nvarchar(MAX) out, @today datetime, @EID nvarchar(5)'

Select @dept = dept from dbo.Dept(@EID)
Select @EID_Dept = CONCAT(@EID, @dept)

SET @SQL = N'Select @StartDate = ' + QUOTENAME(@EID_dept) + ' from dbo.PeriodHistory(@today, @EID);';

EXEC sp_executesql  @SQL,
                    @params,
                    @StartDate = @StartDate out,
                    @today = @today,
                    @EID = @EID

SELECT @StartDate

I used nvarchar(max) for @SQL (you are using varchar but adding N when assign value to variable). sp_executesql awaits nvarchar in statement part

Is a Unicode string that contains a Transact-SQL statement or batch.

All parameters I put in @params variable.

As I understood you need to get another columns based on @EID_dept, so I add QUOTENAME and used this variable in SQL statement building directly. You can not send column name as variable.

Assigning values returned by function in Dynamic SQL

1 Answers1