Select Column (as Parameter) from Table
-
Sorry, I have this sql statement where I supply the column as parameter and then use it to retrieve the values of the specified column. Instead result is column name =( declare @sql varchar(200) SELECT @sql = 'SELECT COLUMN_NAME AS Columns FROM information_schema.columns WHERE (TABLE_NAME = ''MyTable'') and (Column_name = ''Column1'')' exec ('select (' + @sql + ') from MyTable') RESULT: Columns ------- Column1 Column1 Column1 Column1 Column1 Help Please! Thanks! Gerri
-
Sorry, I have this sql statement where I supply the column as parameter and then use it to retrieve the values of the specified column. Instead result is column name =( declare @sql varchar(200) SELECT @sql = 'SELECT COLUMN_NAME AS Columns FROM information_schema.columns WHERE (TABLE_NAME = ''MyTable'') and (Column_name = ''Column1'')' exec ('select (' + @sql + ') from MyTable') RESULT: Columns ------- Column1 Column1 Column1 Column1 Column1 Help Please! Thanks! Gerri
-
Sorry, I have this sql statement where I supply the column as parameter and then use it to retrieve the values of the specified column. Instead result is column name =( declare @sql varchar(200) SELECT @sql = 'SELECT COLUMN_NAME AS Columns FROM information_schema.columns WHERE (TABLE_NAME = ''MyTable'') and (Column_name = ''Column1'')' exec ('select (' + @sql + ') from MyTable') RESULT: Columns ------- Column1 Column1 Column1 Column1 Column1 Help Please! Thanks! Gerri
-- stored procedure
CREATE PROCEDURE returncolname @tablename varchar(255), @colname varchar(255) AS declare @tblname as varchar(255) set @tblname = @tablename declare @clnName as varchar(255) set @clnName = @colname SELECT @clnName AS [Columns] FROM information_schema.columns WHERE (TABLE_NAME = @tablename) and (Column_name =@colname ) GO-- execution of returncolname procedureexec returncolname 'tablename','columname' -
-- stored procedure
CREATE PROCEDURE returncolname @tablename varchar(255), @colname varchar(255) AS declare @tblname as varchar(255) set @tblname = @tablename declare @clnName as varchar(255) set @clnName = @colname SELECT @clnName AS [Columns] FROM information_schema.columns WHERE (TABLE_NAME = @tablename) and (Column_name =@colname ) GO-- execution of returncolname procedureexec returncolname 'tablename','columname' -
Sorry, I have this sql statement where I supply the column as parameter and then use it to retrieve the values of the specified column. Instead result is column name =( declare @sql varchar(200) SELECT @sql = 'SELECT COLUMN_NAME AS Columns FROM information_schema.columns WHERE (TABLE_NAME = ''MyTable'') and (Column_name = ''Column1'')' exec ('select (' + @sql + ') from MyTable') RESULT: Columns ------- Column1 Column1 Column1 Column1 Column1 Help Please! Thanks! Gerri
I usually write out the SQL Statement using place holders. In this case ?COLUMN_NAME? fills in for the variable that will be passed in. I then replace the place holders with using REPLACE. ** Be sure you read up on SQL Injection Attacks if you allow free form entry from the users. This may not be as optimal as building the string on the fly but, it is much less error prone and much more maintainable.
CREATE PROCEDURE DynamicSqlTest ( @columnName NVARCHAR(100) ) AS DECLARE @sql NVARCHAR(1000) SET @sql = N'SELECT COLUMN_NAME AS Columns FROM information_schema.columns WHERE (TABLE_NAME = ''MyTable'') and (COLUMN_NAME = ''?COLUMN_NAME?'')' SET @sql = REPLACE(@sql,'?COLUMN_NAME?',@columnName) EXEC(@sql) -
I usually write out the SQL Statement using place holders. In this case ?COLUMN_NAME? fills in for the variable that will be passed in. I then replace the place holders with using REPLACE. ** Be sure you read up on SQL Injection Attacks if you allow free form entry from the users. This may not be as optimal as building the string on the fly but, it is much less error prone and much more maintainable.
CREATE PROCEDURE DynamicSqlTest ( @columnName NVARCHAR(100) ) AS DECLARE @sql NVARCHAR(1000) SET @sql = N'SELECT COLUMN_NAME AS Columns FROM information_schema.columns WHERE (TABLE_NAME = ''MyTable'') and (COLUMN_NAME = ''?COLUMN_NAME?'')' SET @sql = REPLACE(@sql,'?COLUMN_NAME?',@columnName) EXEC(@sql)