CODEWITHSUNDEEP
sql-servert-sql
Alican Uzun
Alican Uzun

Reputation: 349

How to select alias name from a table in sql server?

I have a language table and I want to select aliases from that table according to the specified language. 

ALTER PROCEDURE sp_executesql
(@parameter1 NVARCHAR(MAX)
,@parameter2 NVARCHAR(MAX)
,@code NVARCHAR(MAX),@language NVARCHAR(MAX))

DECLARE @sql NVARCHAR(MAX)

SET @sql = 'SELECT '+@parameter1+' AS (SELECT @language FROM Languages WHERE code=somecolumn) '+@paramter2+' AS (SELECT @language FROM Languages WHERE code='+@code+') FROM mytable'

EDIT: in Stored Procedure, I need something like that.

Thanks for answers..

Upvotes: 1

Views: 1332

Answers (2)

Jamiec
Jamiec

Reputation: 136104

You cannot use a subquery to build an alias in that way, you would need to use dynamic sql to do this.

DECLARE @language NVARCHAR(255)  -- or whatever type your field is
SELECT @language=language FROM Languages WHERE code=@code

DECLARE @sql NVARCHAR(MAX) = 'SELECT ' + @parameter1 + ' AS ' + QUOTENAME(@language) + ' FROM MyTable'
EXEC sp_executesql @sql

(Note the inclusion of QUOTENAME around the alias - this is a safety feature in case of your alias names having invalid characters.)

You can repeat the code above for the second parameter inside your stored procedure.

Upvotes: 3

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this:

CREATE PROCEDURE sp_NameOfSP
(@parameter1 NVARCHAR(MAX)
 ,@parameter2 NVARCHAR(MAX)
 ,@code NVARCHAR(MAX)
 ,@language NVARCHAR(MAX))
AS
BEGIN 
 DECLARE @sql NVARCHAR(MAX)

 SELECT TOP(1) @language=LanguageColumn FROM Languages WHERE code=somecolumn

 SET @sql = 'SELECT '+@parameter1+' AS '+@language+', '

 SELECT TOP(1) @language=LanguageColumn FROM Languages WHERE code=@code

 SET @sql=@sql+@paramter2+' AS '+@language+' FROM mytable'

 EXEC(@SQL)
END

Replace LanguageColumn with proper column name from Languages table

Upvotes: 1

Related Questions