0

the following code works:

DECLARE @Data  TABLE ( EMPID INT)
DECLARE @D     DATE  = DATEADD(d, -1, GETDATE())
DECLARE @SQL   NVARCHAR(500)
DECLARE @Param NVARCHAR(500)

SET @SQL =     'SELECT TOP 10 EMPID FROM udf_Test( @1, @2 ) la'
SET @Param =   '@1 VARCHAR(10), @2 VARCHAR(10)'

INSERT @Data ( RetailerID )
EXECUTE sp_executesql @SQL, @Param,  @1=@D, @2=@D
SELECT * FROM @Data

But, I want to use variables for the insert string. How do I do that?

This is one of the iterations I tried with no luck...

DECLARE @Data   TABLE ( EMPID INT)
DECLARE @D      DATE = DATEADD(d, -1, GETDATE())

DECLARE @Insert NVARCHAR(500) 
DECLARE @SQL    NVARCHAR(500)
DECLARE @Param  NVARCHAR(500)

SET @Insert = 'INSERT @Data( EMPID ) '
SET @SQL    = 'SELECT TOP 10 EMPID FROM udf_Test( @1, @2 ) la '
SET @Param  = '@1 VARCHAR(10), @2 VARCHAR(10)'

EXECUTE sp_executesql @Insert + @SQL, @Param,  @1=@D, @2=@D
SELECT * FROM @Data

Thank you for helping!

*This is not a duplicate. The link provided does not answer how to use a variable for the insert clause, only the select.

Uziel
  • 349
  • 4
  • 9
  • `@Data` would need to be a parameter. But that's doomed to failure because table-valued parameters are `READONLY` in T-SQL. You can't use dynamic SQL to fill a table variable. Use a temp table and/or `INSERT .. EXEC`. – Jeroen Mostert Aug 24 '17 at 18:35
  • Try `SET @SQL = @Insert + 'SELECT TOP 10 EMPID FROM (values())(emp) la '` and later `EXECUTE sp_executesql @SQL, @Param, @1=@D, @2=@D` – Zohar Peled Aug 24 '17 at 18:35
  • Thanks Zohar. I tried it but it says 'Must declare the table variable "@Data"', which is the response I get with most of the ways I tried. – Uziel Aug 24 '17 at 22:35

0 Answers0