Menu
Possible Duplicate:
SQL: Select dynamic column name based on variable
I have the following simple select statement:
DECLARE @value varchar(10) SET @value = 'intStep' SELECT @value FROM dbo.tblBatchDetail I have tried
SELECT CAST(@value AS varchar(10)) FROM dbo.tblBatchDetail and all I get a list of intstep
QUESTION: can I pass variable as column name to this statement? Or what is the best way of doing this
I'm using SQL Server 2008 (9.0 RTM)
This will be a stored procedure
Thanks in advance
894
Answers. You can't do that directly. You have to create a dynamic sql statement using a var and execute the statement.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
You can just add the new column to the table as nullable, either with SQL Server Management Studio by right clicking on the Table and clicking "Design" or by using an ALTER TABLE statement like this ALTER TABLE TableName ADD NewColumnName DataType NULL .
You can't use variable names to bind columns or other system objects, you need dynamic sql
DECLARE @value varchar(10) SET @value = 'intStep' DECLARE @sqlText nvarchar(1000); SET @sqlText = N'SELECT ' + @value + ' FROM dbo.tblBatchDetail' Exec (@sqlText) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
