SQL syntax error

[shavish]

Hello all,

Am trying to add data from SQL server through direct query option. When i add data and i am getting following error:

Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'. Incorrect syntax near ')'

Query is:

--Declare variables

DECLARE @sortin INT=1;

DECLARE @sortlabel INT=1;

DECLARE @name NVARCHAR(50)= 'Example_Name';

DECLARE @ID NVARCHAR(50)= 'Example_ID';

--Main Query

SELECT

Table1.names as Name,

Table1.id as ID,

FROM

Table1.Names as Name

Table1.id as ID,

[v-tsaipranay]

Hi @shavish ,

Thank you for using Microsoft Community Forum.

The issue comes from the DECLARE statements, Power BI’s Direct Query doesn’t support procedural SQL commands like DECLARE.

Here are a few ways to fix this:

If the values are fixed, you can hardcode them directly in the WHERE clause:

SELECT

    Table1.names AS Name,

    Table1.id AS ID

FROM

    Table1

WHERE

    Table1.names = 'Example_Name'

    AND Table1.id = 'Example_ID';

If you need flexibility, consider creating a SQL view on the database side with the logic you need. Then connect Power BI to that view for a cleaner setup.

If the T-SQL logic must stay dynamic switching to Import mode instead of Direct Query will support variables and more advanced SQL syntax, though it won’t reflect live data changes.

I hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @shavish ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @shavish ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @shavish ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[shavish]

No the solution didn't work. Got an error for must declare scalar variable '@name'

[hnguy71 · Accepted solution]

Hi @shavish 

Power BI wraps your query statement in another SELECT statement, therefore, you won't be able to use the DECLARE function.

You can try this instead:

SELECT
CONVERT(varchar(50), Table1.names) As Name,
CONVERT(varchar(50), Table1.id) As ID,

FROM
Table1

[ajohnso2]

hnguy is correct but just included an extra comma.

SELECT
CONVERT(varchar(50), Table1.names) As Name,
CONVERT(varchar(50), Table1.id) As ID

FROM
Table1

 You cannot use the key word declare in the sql query window from power bi