Is this an efficient SQL server?

Question

If I write sproc in this format, will the sql server do this efficiently or do I need to perform sql weaving on the server side (.net)?

Note: this is just a rough idea of ​​what my request looks like, are there any additional if clauses that I execute with the "exec" function

declare @sql nvarchar(4000)
declare @order nvarchar(4000)


set @sql = 'select id, name'
set @sql = @sql + ' ....' 

if(@sortOrder)
    set @order = 'order by name desc'

exec @sql + @order

Answer 1

Instead of creating an SQL string, why not just use the case statement in order?

eg. (assuming one sorts by first and / or last name in a table with FirstName and LastName fields)

order by
  case 
  when @sortExpression = 'lastname asc' then CAST(LastName as sql_variant)
  when @sortExpression = 'firstname asc' then CAST(FirstName as sql_variant)
  end asc,
  case
  when @sortExpression = 'lastname desc' then CAST(LastName as sql_variant)
  when @sortExpression = 'firstname desc' then CAST(FirstName as sql_variant)
  end desc

, order by SQL, .

Answer 2

, , - SQL .

Answer 3

. SQL- SQL , , , :

select
  t.* 
from 
  table as t 
order by
  case @val
    when 1 then column1
    when 2 then column2
  end

, , , , , sql:

select
  t.*
from
  xfn_Function(@arg1, @arg2) as t
order by
  t.col1, t.col2

, , t.col1, t.col2 .. .., .

Answer 4

SQL?

, SQL-, ; , , ( ), SQL proc, .

SQL 2k5 2k8, nvarchar (max) .

Answer 5

, SQL, .Net-, SQL-, proc.

Answer 6

, . . , ?

Answer 7

. , .

, "ifs"

if(@sortorder = 'name') select id, name from tbl order by name asc
if(@sortorder = 'id') select id, name from tbl order by id asc

Answer 8

.

"exec". .NET.

At some point and time, it compiles SQL into some internal code. This can happen when .NET pushes it or subsequently.

Answer 9

I would build the rest of the request up to order, i.e.

@sql = 'SELECT id, name FROM myTable ORDER BY ' + @order

... and pass the name and direction of the column to proc. This method is safer, because not much can be transferred to the reservation clause to cause any harm.

To answer your question, yes, it is effective.