Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with query

Author

Topic

chrispow88
Starting Member

1 Post

Posted - 2008-03-13 : 19:37:20

Database procedure to retrieve a columnname and its value.

I need something like the following but not sure how to code it, I am finding it very hard to understand the more complex dynamic SQL.

----------------
Declare a temp table temp_table(ColumnName, ColumnValue)

//List all the column names in @mytable place in @column_name
SELECT distinct column_name FROM information_schema.columns WHERE table_name = @mytable1
begin

//get the associated value for column_name place in @column_value
Select @column_name from @mytable1 where @Code = "A1"

//insert the pair into a temp_table
insert into @temp_table(ColumnName, ColumnValue) values (@column_name,
@column_value);
end

select SQL = Select * from temp_table;
Exec (SQL);
----------------------
I have tried using cursors but can't get that to work.

Any help would be useful, example code would be great.

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-13 : 21:01:02

You should first be aware that dynamic SQL can be "bad" for the most part, but at times--it can be necessary.

You can pass a column name, and build the dynamic SQL to select the value from it..



Declare @SQL varchar(8000)
Declare @ColumnName varchar(128)
Declare @Table varchar(128)
SET @ColumnName = 'MyColumnName'
SET @Table = 'MyTablename'

SET @SQL = 'Select ['+ LTRIM(RTRIM(@ColumnName)) + '] FROM dbo.[' + LTRIM(RTRIM(@Table)) + ']'
Print @SQL --  print to check!
/*
Select [MyColumnName] FROM dbo.[MyTablename]
*/
--Exec (@SQL)

For example...

Just experimenting?, or do you have a specific need to address? there may better ways without using dynamic SQL...

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources