2

I want to select the store procedure parameter as aliases in select statement in sql

example i have two parameter in store procedure

@programcode int,
@class int

Query where I want use those parameters as alias. 

select programcode as @programcode from tbl_name
Rahul Neekhra
  • 780
  • 1
  • 9
  • 39
Lovedeep Singh
  • 23
  • 7

2 Answers2

1

Why would you want a column alias to be a number?

In any case, you can write this as:

DECLARE @sql nvarchar(MAX);

SET @sql = N'
SELECT programcode  AS [' + CAST(@programcode AS VARCHAR(MAX)) + ']
FROM tbl_name
';

EXEC sp_executesql @sql;

Numbers are not really recommended for column aliases, so they need to use escapes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Try this query. 

DECLARE @SqlText nvarchar(MAX);
SET @sqlText = N'SELECT programcode  AS ' + CAST(@programcode AS VARCHAR(MAX)) + ' FROM tbl_name'
Exec (@sqlText)
Dumi
  • 1,414
  • 4
  • 21
  • 41
  • This will cause an error: "Conversion failed when converting the nvarchar value 'SELECT programcode AS ' to data type int". You must first cast the int to a nvarchar. For ints, use `nvarchar(11)` to make sure you have enough chars to convert even if the value is the minimum value of int (-2,147,483,648). – Zohar Peled Nov 12 '18 at 10:27
  • Also, If `@programcode` wasn't an int, this kind of query would be a security risk since it would be an open door for sql injection attacks. – Zohar Peled Nov 12 '18 at 10:29
  • Updated the answer. Thanks @ZoharPeled – Dumi Nov 12 '18 at 10:33
  • 1
    Good, but why `varchar(max)`? the maximum number of chars you might need is 11, as I've already written in the first comment. Using `max` for everything is a bad habit that can drastically effect performance. – Zohar Peled Nov 12 '18 at 10:52
  • What Zohar said. Using nvarchar(max) is just lazy. Using varchar(max) is also lazy and inconsistent. – SMor Nov 12 '18 at 13:16
  • after apply the above query i am getting below error "Must declare the scalar variable "@programcode"" @programcode is store procedure parameter and its delared already – Lovedeep Singh Nov 13 '18 at 04:07
  • `EXEC` should be avoided and `sp_executesql` should be used instead: https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/ and https://stackoverflow.com/questions/14722201/stored-procedure-exec-vs-sp-executesql-difference – Dai Nov 14 '18 at 03:56