Query columns by name in stored procedures (SQL Server 2005)
-
Hi folks, I got a question concerning stored procedures and I didn't find anything helpful on the comment search or via google: I need to query a single column in a stored procedure but I want to identify this column with a VarChar-parameter, so that I can use one stored procedure for all columns in a table. Maybe there's also a way to design such a stored procedure even with whole tables. What I'm looking for is a stored procedure like this:
CREATE PROCEDURE [dbo].[GetValueFromMyTable] ( @Column varchar ) AS BEGIN SELECT @Column FROM MyTable ENDThe code seems to be correct, but the statement doesn't work the way I want it to work. Does anyone have an idea about my problem? Thanks in advance, Tobias -
Hi folks, I got a question concerning stored procedures and I didn't find anything helpful on the comment search or via google: I need to query a single column in a stored procedure but I want to identify this column with a VarChar-parameter, so that I can use one stored procedure for all columns in a table. Maybe there's also a way to design such a stored procedure even with whole tables. What I'm looking for is a stored procedure like this:
CREATE PROCEDURE [dbo].[GetValueFromMyTable] ( @Column varchar ) AS BEGIN SELECT @Column FROM MyTable ENDThe code seems to be correct, but the statement doesn't work the way I want it to work. Does anyone have an idea about my problem? Thanks in advance, TobiasAs @Column is an input parameter and you have taken varchar as data type you have to specifies the length of input parameter. for example:- @Column varchar(20) I hope this will help you. Puneet Srivastava
-
As @Column is an input parameter and you have taken varchar as data type you have to specifies the length of input parameter. for example:- @Column varchar(20) I hope this will help you. Puneet Srivastava
Thank you for your answer, but it still doesn't work. I only get the column name as a result-set. I think the statement awaits a column-object to identify the right column, doesn't it?! Someone got an idea?
-
Hi folks, I got a question concerning stored procedures and I didn't find anything helpful on the comment search or via google: I need to query a single column in a stored procedure but I want to identify this column with a VarChar-parameter, so that I can use one stored procedure for all columns in a table. Maybe there's also a way to design such a stored procedure even with whole tables. What I'm looking for is a stored procedure like this:
CREATE PROCEDURE [dbo].[GetValueFromMyTable] ( @Column varchar ) AS BEGIN SELECT @Column FROM MyTable ENDThe code seems to be correct, but the statement doesn't work the way I want it to work. Does anyone have an idea about my problem? Thanks in advance, TobiasCREATE PROCEDURE [dbo].[GetValueFromMyTable] ( @ColID INT) AS BEGIN SELECT ColName = CASE @ColID WHEN @ColID=1 THEN Col1 WHEN @ColID=2 THEN Col2 ELSE col3 END, FROM tableName
Regards, Arun Kumar.A
-
Thank you for your answer, but it still doesn't work. I only get the column name as a result-set. I think the statement awaits a column-object to identify the right column, doesn't it?! Someone got an idea?
-
try building dynamic query with the variable name
If U Get Errors U Will Learn If U Don't Get Errors U Have Learnt
I know the possiblity of dynamic queries, but the problem is, that I've got to use one stored procedure per table. So I simply have to get the mapping between the name and the column. Is there a fast way to achieve this?
-
I know the possiblity of dynamic queries, but the problem is, that I've got to use one stored procedure per table. So I simply have to get the mapping between the name and the column. Is there a fast way to achieve this?
Ok, finally I made it work - here's the simplified procedure's code that's working fine:
ALTER PROCEDURE [dbo].[GetData]( @Column varchar(100), @Begin datetime, @End datetime ) AS BEGIN DECLARE @query nvarchar(1000) SET @query = N'SELECT ' + quotename(@Column) + ' FROM MyTable' EXEC sp_executesql @query ENDThanks for your hint vimal_yet!