0
declare @sum as nvarchar(20) = 'sum(Salary)'

select @sum from Employee

I want to do something like this. I need to store my sql query part in variable and use that variable in sql query. Is it possible in any way?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Yallappa
  • 5
  • 2
  • 1
    You might want to take a look at [Dynamic SQL](https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/) – kman Apr 11 '17 at 02:20
  • 1
    You can use `EXEC` or `sp_execute_sql`. It's called dynamic SQL. It's usually a bad idea. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql http://stackoverflow.com/questions/548090/dynamic-sql-execsql-versus-exec-sp-executesqlsql – Nick.Mc Apr 11 '17 at 02:21
  • @Nick.McDermaid I wouldn't say its a bad idea... It's just another tool in your belt, and has saved the day on many occasions. – John Cappelletti Apr 11 '17 at 02:24
  • I agree - there are definitely requirements that can _only_ be satisfied with dynamic SQL but it should never be the first port of call, and sometimes it's a symptom of a bad design decision, especially for inexperienced developers. – Nick.Mc Apr 11 '17 at 02:45

3 Answers3

1

SQL Server does not support macro-substition. It does however allow for DYNAMIC SQL. Below is a simple example

declare @sum as nvarchar(20) = 'sum(Salary)'

Declare @SQL varchar(max) = 'select '+@Sum+' from Employee'
Exec(@SQL)

Now, to be clear, there are risks of SQL Injection. Take a peek at http://bobby-tables.com/

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0
YOU CAN USE DYNAMIC QUERY

    BEGIN TRAN

    DECLARE @sum NVARCHAR(20) = 'sum(Salary)',@SQL VARCHAR(4000)
    SET @SQL=''
    SET @SQL = @SQL + 'SELECT '+@Sum+' FROM Employee'
    PRINT @SQL
    EXEC(@SQL)

    ROLLBACK TRAN
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17
-1

id 'sum(salary)' is like that then it should return a result of

declare @sum as nvarchar(20) = 'sum(Salary)'

result a literal string

sum(Salary)

but if like:

declare @sum as nvarchar(20)
 select @sum = sum(Salary),column1,..and so on from your table

then it should return a result from your function

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30