Help with dynamic SQL query, EXEC command...

Help with dynamic SQL query, EXEC command...


I want to run custom sql query, and this query have 2 dynamic where conditions.

IF (@ClassId <> 0)
    SELECT @Where = 'AND {UserTask}.[ClassId] = ' + CAST(@ClassId AS VARCHAR(MAX)) + ' '

IF (@ActionId <> 0)
            SELECT @Where = @Where + 'AND {UserTask}.[ActionId] = ' + CAST(@ActionId AS VARCHAR(MAX)) + ' '

SELECT @Query = '
      SELECT COUNT(DISTINCT {UserTask}.[UserId])
            FROM {UserTask}
      WHERE {UserTask}.[EndDateTime] = ''' + REPLACE(CAST(@EndDate AS VARCHAR(MAX)), '#', '') + '''
      AND {UserTask}.[TaskId] = ' + CAST(@TaskId AS VARCHAR(MAX)) + ' ' + @Where
EXEC (@Query)

This is possible to make in outsystems???
Run SQL from string by EXEC command????

Best Regards
Duarte Brito

Hi Duarte,
I'm not sure if its possible,
but you can for sure do something like build the clauses outside and do the query with something like two input parameters with the expand inline



Carlos Rocha

But you know why this is a problem?
It's a simple SQL query...
Hi Duarte,

It is possible BUT Definitely it's not performant and you're not folowing the best practices rules of the development! 

Don't use select distinct. don't use cast conversions in the where clause and don't complicate. (sorry my ruddiness but what I'm seeing here is that you're probably missing some trainning videos available for free).

To perform that query you just have to do a simpley query! How?

1. Do a simple query with parameters
  • ClassId - With "Is search Parameter" value Yes
  • ActionId - With "Is search Parameter" value Yes
  • EndDate Time  - With "Is search Parameter" value No
  • TaskId  - With "Is search Parameter" value No
2. The Simple query with Entity/structure
  • 		UserTask
3. The where clause with all the clauses.

The Is search parameter turned to yes will do what you want. It will dynamic generate the where clause if the input paramenter is filled.

4. Finally, you should use the query.count property to get the number of tasks that the user have.

Cheers ,
Thanks for the reply,
But ... my knowledge is very old, and my experience is very small ...

I think we have not answered my question, can I execute dynamic SQL with EXEC command?

dummy question, dummy answer...

As I've told you, yes you can, but in your example you don't needed, and that is the key of my answer. Why cach bugs with a cannon ball?

Still, if you just want to ignore the How to do it right, and do a non performant and spaguetti code, go ahead, the explanation is here (you can use OPENQUERY to do that).

Hope it help anyway, 
Ricardo, I dont ignore your answer...
I explain better, actualy I have the application developed in that way... But I'm not a outsystems developer.
To do correctly, I need wait for the outsystems developers to this.

Now the solution, without change de application code is alter only the query...

O meu inglês é muito mau. Explico-me em Português. Não me compreendas mal. Estou aqui para aprender.
Tenho a aplicação desenvolvida assim, com um Advanced Query, que recebe esses parametros.
Para fazer correctamente como tu indicaste tem de se alterar o codigo, e é isso que não quero, porque não sou um developer de outsystems (Apenas tenho algumas luzes).

Por isso a alternativa rapida para solucionar a coisa era fazer um SQL dinamico com o comando EXEC e desta forma não tinha que mudar código, só uma query.

Thanks for the reply
roger that!

But take notion on that for the next time to warn them ;)