create procedure sp_First @columnname varchar AS begin select @columnname from Table_1 end exec sp_First 'sname'
My requirement is to pass column names as input parameters. I tried like that but it gave wrong output.
So Help me
This question is tagged with
~ Asked on 2012-04-10 16:42:43
You can do this in a couple of ways.
One, is to build up the query yourself and execute it.
SET @sql = 'SELECT ' + @columnName + ' FROM yourTable' sp_executesql @sql
If you opt for that method, be very certain to santise your input. Even if you know your application will only give 'real' column names, what if some-one finds a crack in your security and is able to execute the SP directly? Then they can execute just about anything they like. With dynamic SQL, always, always, validate the parameters.
Alternatively, you can write a CASE statement...
SELECT CASE @columnName WHEN 'Col1' THEN Col1 WHEN 'Col2' THEN Col2 ELSE NULL END as selectedColumn FROM yourTable
This is a bit more long winded, but a whole lot more secure.
~ Answered on 2012-04-10 16:50:24
No. That would just select the parameter value. You would need to use dynamic sql.
In your procedure you would have the following:
DECLARE @sql nvarchar(max) = 'SELECT ' + @columnname + ' FROM Table_1'; exec sp_executesql @sql, N''
~ Answered on 2012-04-10 16:47:48