Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
create procedure sp_First
select @columnname from Table_1
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
As mentioned by MatBailie
This is much more safe since it is not a dynamic query and ther are lesser chances of sql injection . I Added one situation where you even want the where clause to be dynamic . XX YY are Columns names
CREATE PROCEDURE [dbo].[DASH_getTP_under_TP]
@fromColumnName varchar(10) ,
@toColumnName varchar(10) ,
-- this is the column required for where clause
declare @colname varchar(50)
set @colname=case @fromUserType
when 'XX' then 'XX'
when 'YY' then 'YY'
select SelectedColumnId from (
when 'XX' then tablename.XX
when 'YY' then tablename.YY
end as SelectedColumnId,
when 'XX' then XX
when 'YY' then YY
end)= ISNULL(@ID , @colname)
) as tbl1 group by SelectedColumnId
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'
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...
WHEN 'Col1' THEN Col1
WHEN 'Col2' THEN Col2
END as selectedColumn
This is a bit more long winded, but a whole lot more secure.
+1, This is a bit more long winded, but a whole lot more secure.
If you're getting the columns from another table "UpdateableColumns" you can also do some kind of verification with it. Example: "Where Column exist in (select ColumnName from UpdateableColumns)"
Create PROCEDURE USP_S_NameAvilability
DECLARE @cmd AS NVARCHAR(max)
SET @Value = ''''[email protected]+ ''''
SET @cmd = N'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @Value
As i have tried one the answer, it is getting executed successfully but while running its not giving correct output, the above works well
Please Try with this.
I hope it will work for you.
Create Procedure Test
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * FROM ' + @Table + ' WHERE ' + @Column + ' = ' + @Value
/** Exec Test Products,IsDeposit,1 **/
Select * is not a good practice and this is not the user's doubts
Try using dynamic SQL:
create procedure sp_First @columnname varchar
declare @sql nvarchar(4000);
set @sql='select ['[email protected]+'] from Table_1';
exec sp_executesql @sql
exec sp_First 'sname'
You can pass the column name but you cannot use it in a sql statemnt like
Select @Columnname From Table
One could build a dynamic sql string and execute it like EXEC (@SQL)
For more information see this answer on dynamic sql.
Dynamic SQL Pros and Cons
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''
it must be slower, right? but how much? can it be ignored?
No, not much slower. The only amount it's slower by is the string concatenation overhead. sp_executesql will execute the text in a way where it will be translated into an execution plan just like any other command.
This is not possible. Either use dynamic SQL (dangerous) or a gigantic case expression (slow).
If the CASE is only in the SELECT statement (and not in a JOIN, WHERE clause, ORDER BY, etc) then this option is not actually that slow.
It will pull out all columns every time, no matter what concrete column is requested.