Forum Discussion
SQL syntax error
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,
7 Replies
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 Table1You cannot use the key word declare in the sql query window from power bi
No the solution didn't work. Got an error for must declare scalar variable '@name'
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.
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.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.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.
