CODEWITHSUNDEEP
sqlsql-servert-sqlvariables
dragonfury2
dragonfury2

Reputation: 428

Executing select statement as variable from TVF

I have to get a list of results from a Table value function from a variable. I have done something like this:

DECLARE @Date char(8) = '20200508'
DECLARE @Type varchar(100) = 'Inbound'
DECLARE @Offset INT = 3600
DECLARE @EmployeeID INT = null

DECLARE @TypeFunc as varchar(max) 
SET @TypeFunc= N'select EmpID, Callcount from dbo.fn_' + @Type + '('''+ @Date +''','+ CAST(@Offset as Varchar(100))+','+ CAST(@EmployeeID as varchar(100))+')';
EXEC (@TypeFunc)

I expect to see a list of results as if I'm doing a normal select query, however, it is just coming back with 'Commands completed successfully.' in the results grid, which doesn't seem like its doing it correctly.

The query it should run should look like

Select EmpID, Callcount From dbo.fn_Inbound('20200508', 3600, null)

Anything I'm missing here?

Upvotes: 0

Views: 123

Answers (1)

Thiyagu
Thiyagu

Reputation: 1340

I found 2 mistakes in your Query:

1.) Use CONCAT instead of + because if any of your concatenating string is null it makes the whole Concatenation as NULL (For your case EmpID is null it will makes the Whole Query as null by using +)

2.)ISNULL(CAST(@EmployeeID as varchar(100)),'NULL') Use ISNULL fn to pass as null for that Parameter in your function

SET @TypeFunc= CONCAT(N'select EmpID, Callcount from dbo.fn_' , @Type , '(''', @Date 
,''',', CAST(@Offset as Varchar(100)),',',ISNULL(CAST(@EmployeeID as 
varchar(100)),'NULL'),')');

enter image description here

Upvotes: 1

Related Questions