Help with Syntax for TSQL Query Parameter

[RichardJ]

Hi,

I have a query which works ok

Declare @WorkflowName NVARCHAR(100)
Set @WorkflowName = 'Design Alpha'
--Set @WorkflowName = '" & Workflow_Names & "'

Select 
WorkflowID,
@WorkflowName

From Workflows w Where Name = 'Design Alpha' 
--From Workflows w Where Name = @WorkflowName

Hard Coded Query Result

When I try to use the parameter value in the query

Set @WorkflowName = '" & Workflow_Names & "'

instead of 'Design Alpha' then the parameter value which is passed through is as shown

I'd expected the Yellow Value to be 'Design Alpha' as this is what the current Parameter Value is

However the query doesn't work as the & Workflow Names & is passed through as the value instead of the expected 'Design Alpha'

I've used Parameter values without any issue in the past but they have always been numeric values so am wondering if i'm going wrong with my TSQL syntax.

Hopefully i've made a typo or something silly but i've gone snow blind looking at it.

Can anyone advise how I can successfully pass in the dynamic parameter ?

Thanks,

Richard

[lbendlin]

Please show more of the Power Query code.  Your example is missing the beginning and end of the query string.

[RichardJ · Accepted solution]

Thanks for the response @lbendlin - I managed to figure it out.

let
    Source = Sql.Database(Server_Name, Database_Name, [Query="
    
   SELECT
   Workflows.Name as 'Workflow Name',
   Workflows.Description as 'Workflow Description',
   Workflows.InitialTransitionID as 'Workflow TransitionID',
   Status.Name as 'Workflow Status Name'

   FROM Workflows 

   Left Outer Join Status on Status.WorkflowID = Workflows.WorkflowID   
   
   Where Workflows.Name = '" & Workflow_Choice & "' and Status.Name = 'Work In Progress'
   

"])
in
    Source

I'd mistakenly set the storage mode of the table to Import when it should have been DirectQuery for the parameter to populate ok.

Appreciate you responding to the question.

Thanks,

Richard