SQL query error in directquery

[Lily36876]

I need to use directquery to use parameter in slicer,

but my sql query is success in SQL SERVER but happen error when using direct query,

I  have try to use -->select * from (.......my program......) as SourceQuery

still show error

"Microsoft SQL: Incorrect syntax near keyword 'declare'.
Incorrect syntax near ')'. "

I have so idea:(

[aciravegna]

I have the solution!

Convert your stored procedure to function that returns a table. It works!

Something like this:

create or alter function dbo.my_function()
returns @my_data table
(
Field1 varchar(20),
Field2 int,
Field3 int
)
as
begin

   declare @test  int

   set  @test = 1

   insert into @my_data

   select [Field1, Field2, Field3]

   from [your table]

   where Field3 = @test

   return

end

[amitchandak]

@Lily36876 , You have to use select here. declare etc is not supported.

SP is supported, But not sure that can work direct query

[Lily36876]

thx For reply,that meas I can't use any declare in DQ? 

[amitchandak]

@Lily36876 , No,You can not declare. I think so.