CODEWITHSUNDEEP
sqlsql-serverparametersssms
Jt2ouan
Jt2ouan

Reputation: 1964

Make value of a parameter the column name without dynamic SQL

Is there a way to name a column the value of what's in a parameter, without using dynamic SQL?

I need to somehow output the value of what is in the @input parameter as the name of the column in the SQL statement. I need to avoid using dynamic SQL.

DECLARE @input VARCHAR(10)=' Person '; 

SELECT count(*) AS  @input + ' Open Data'    
FROM #Accounts a
JOIN dbo.FileFeed t On t.ID = a.AccountID     
GROUP BY a.accountid

Upvotes: 3

Views: 3547

Answers (7)

shawnt00
shawnt00

Reputation: 17915

If you've got a later version of SQL Server then you might look at this option:

EXEC <stored_procedure> WITH RESULT SETS (...)

I imagine you're already using a stored procedure. This way you'd at least be able to keep the main logic completely static in a second procedure and just interface to a procedure with the dynamic exec call to accomplish the renaming of column(s).

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

IF it is the case that the values of what can be in the @input variable / parameter are limited (i.e. not open-ended user input or something of that nature), then you can do this without DynamicSQL. You just need to have multiple copies of the query in this Stored Procedure, each being exactly the same except for the alias of the column, and pick the one you want based on the value of the variable. For example:

DECLARE @input VARCHAR(10)=' Person '; 

IF (@input = 'Person')
BEGIN
  SELECT COUNT(*) AS [Person Open Data]
  FROM #Accounts a
  JOIN dbo.FileFeed t
    ON t.ID = a.AccountID     
  GROUP BY a.accountid;
END;

IF (@input = 'Account')
BEGIN
  SELECT COUNT(*) AS [Account Open Data]
  FROM #Accounts a
  JOIN dbo.FileFeed t
    ON t.ID = a.AccountID     
  GROUP BY a.accountid;
END;

...

Upvotes: 0

Birby
Birby

Reputation: 331

Unless you rename the columns in the application, this is the way i would do it.

DECLARE @input VARCHAR(10)=' Person '; 
Declare @sql varchar(max);
set @sql'SELECT count(*) as ['+ @input +' Open Data]
FROM #Accounts a
JOIN dbo.FileFeed t On t.ID = a.AccountID     
GROUP BY a.accountid';

EXEC(@sql);

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

No. Databases use a process similar to compiling to turn your query into an execution plan. Part of this process involves determining whether the user running the query has permissions to access the tables and columns used by the query. If those tables and columns are not determined until execution time, the compilation step can't finish. It seems strange, but the same thing applies to the result set.

Dynamic SQL (which creates a new query, with a new compilation step where the tables and column names are known up front) will be the only way to accomplish this.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175656

One ugly way, without Dynamic-SQL is using temporary table and rename column:

DECLARE @input VARCHAR(10) = ' Person '; 
DECLARE @new_name VARCHAR(100) = @input + ' Open Data';

SELECT [rename_me] = COUNT(*)
INTO #temp    
FROM #Accounts a
JOIN dbo.FileFeed t On t.ID = a.AccountID     
GROUP BY a.accountid;

EXEC tempdb..sp_rename '#temp.rename_me', @new_name, 'COLUMN';

SELECT * 
FROM #temp;

Upvotes: 3

Johnny Bones
Johnny Bones

Reputation: 8402

The answer is "No". Dynamic SQL, by definition, means that the code can alter itself. The only alternative to Dynamic SQL is Hard-Coded SQL, which is when you specifically write every piece of it yourself.

You're asking if you can change a field name based on a variable, which can only be done with Dynamic SQL.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You need to use dynamic SQL:

DECLARE @input VARCHAR(10) = ' Person '; 

DECLARE @sql NVARCHAR(MAX) = '
SELECT count(*) AS  [@Input Open Data]    
FROM #Accounts a JOIN
     dbo.FileFeed t
     On t.ID = a.AccountID     
GROUP BY a.accountid';

SET @sql = REPLACE(@sql, '@Input', @Input);

exec sp_executesql @sql;

However, I don't really think this is a good idea. If you need to rename a column, do it in the application code.

Upvotes: 1

Related Questions