Dynamic SQL Query is not returning data into SQL sheet

Question

I have Dynamic SQL Query like below

Dynamic SQL Query:

Declare @Currentmonth4date varchar(8)= convert(varchar(8), DATEADD(month, DATEDIFF(month, 0, GETDATE()), 3),112)
Declare @SQL varchar(max)= 'dbo.Exclusion_Process_Tab1_' + '20181204'

Declare @Select varchar(400) = 'Select * from ' + @SQL

exec (@Select)

I have tried to execute this code from excel'2010 through microsoft query source.

after clicking on return data to option form query window,it is not returning the data into excel sheet.

Please help

Create an SP, put the code inside it and call the SP from Excel. And you don't use your variable @Currentmonth4date — Denis Rubashkin
– Denis Rubashkin, Commented Jan 3, 2019 at 13:57
i don't have permissions to create sp , could you please suggest if is there any other approach that you know — ashok nagulapati
– ashok nagulapati, Commented Jan 3, 2019 at 14:05
I think there is an erratum in the code provided, I guess you wanted to say in line 2 "Declare @SQL varchar(max)= 'dbo.Exclusion_Process_Tab1_' + @Currentmonth4date" — Angel M.
– Angel M., Commented Jan 3, 2019 at 14:34

Alvaro Parra · Accepted Answer · 2019-01-03 14:14:31Z

2

Did you try to test your dynamic sql

It is returning Select * from dbo.Exclusion_Process_Tab1_20181204

try this

Declare @Currentmonth4date varchar(8)= convert(varchar(8), DATEADD(month, DATEDIFF(month, 0, GETDATE()), 3),112)
Declare @SQL varchar(max)= 'dbo.Exclusion_Process_Tab1_'+ @Currentmonth4date

Declare @Select varchar(400) = 'Select * from '+@SQL

PRINT(@SELECT)

--exec (@Select)

This one is returning

Select * from dbo.Exclusion_Process_Tab1_20190104

After testing comment print and uncomment exec

Before executing your dynamic queries test them with print and see what do they return.

answered Jan 3, 2019 at 14:14

Alvaro Parra

8052 gold badges9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Turczyn · Accepted Answer · 2019-01-03 14:17:48Z

0

You don't need dynamic SQL, what's the point?

You just doing something like:

EXEC('SELECT * FROM dbo.Exclusion_Process_Tab1_20181204')

Why not just:

SELECT * FROM dbo.Exclusion_Process_Tab1_20181204

And omit all unnecessary variables.

answered Jan 3, 2019 at 14:17

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Dynamic SQL Query is not returning data into SQL sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related