November 7, 2010 at 9:52 pm
I have two table
tblFinance
tblColumns
tbl Finance have only one column: Column_Name with values
C1
C2
C3
C4
C5
C6
tblcolumns have only one column: Column_Name with values
C1
C2
C3
C6
I want to the select columns from the tblFinance table
Like: SELECT C1, C2, C3, C6 from tblFinance but here selection columns should be driven from the column names define tblcolumns
For this I have written stored procedure this is not working fine, please try to correct the same if possible
ALTER PROCEDURE ComputeColumns
AS
SET NOCOUNT ON
BEGIN
DECLARE @name varchar(225)
DECLARE @stmt varchar(MAX)
DECLARE @Quary varchar(MAX)
SET @Quary = 'SELECT '+@stmt + ' from tblfinance'
DECLARE db_cursor CURSOR FOR
SELECT Column_Name from tblColumns order by Column_Name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt += @name + ','
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Print @stmt
Print @Quary
END
November 7, 2010 at 10:18 pm
Im missing these lines
SET @stmt = LEFT(@stmt, LEN(@stmt) -1)
SET @Quary = 'SELECT '+@stmt + ' from finance'
Its resolved, thanks alot
November 8, 2010 at 12:22 am
If I'm understanding the question properly, swap the order of these items:
SET @Quary = 'SELECT '+@stmt + ' from tblfinance'
DECLARE db_cursor CURSOR FOR
SELECT Column_Name from tblColumns order by Column_Name
and put that below this part:
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt += @name + ','
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
That will get you the statement you're looking for. I think.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply