Unusual Sp!! - 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

SQL Server Development (2000)

Unusual Sp!!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-09-25 : 22:17:18

Nikhil Mishra writes "Hi All,

Config: -
OS :WIN2k Professional
SQL Server 2K

I was creating an SP which will: -
GET THE VALUES OF ALL THE COLUMNS FOR ANY GIVEN TABLE

I wrote the following statement: (of course it doesn't include the usual CREATE SPROC statement)

/************************[ORIGINAL CODE]***********************
Purpose : Extract the values from all the columns for a given
table name.The SP needs to determine the name of the
column.

Parameter: Table Name
*************************************************************/
DECLARE @COL_NAME AS VARCHAR(255)

DECLARE COL_CRSR CURSOR FOR

SELECT C.NAME
FROM SYSCOLUMNS C
INNER JOIN SYSOBJECTS S
ON C.ID = S.ID
WHERE S.XTYPE= 'U'
AND S.NAME = @table_name

OPEN COL_CRSR
FETCH NEXT FROM COL_CRSR INTO @COL_NAME

WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM COL_CRSR INTO @COL_NAME
PRINT @COL_NAME
SELECT @COL_NAME FROM @table_name

END

CLOSE COL_CRSR
DEALLOCATE COL_CRSR
/*******************[END]********************************/

Although the value of @COL_NAME is correct (i.e. I am able to fetch the correct column names for my table),it never gives me the values for this column from the table.

However if I change my statement to:

/************[CHANGED CODE]***********************************
Purpose : Extract the values from all the columns for a given
table name.The SP needs to determine the name of the
column.

Parameter: Table Name
*************************************************************/

DECLARE @COL_NAME AS VARCHAR(255)
DECLARE @STAT AS VARCHAR(255)
DECLARE @TABLE_NAME AS VARCHAR(255)
SELECT @TABLE_NAME = 'ACCOUNTING_TYPE'

DECLARE COL_CRSR CURSOR FOR
SELECT C.NAME
FROM SYSCOLUMNS C
INNER JOIN SYSOBJECTS S
ON C.ID = S.ID
WHERE S.XTYPE= 'U'
AND S.NAME = @TABLE_NAME

OPEN COL_CRSR
FETCH NEXT FROM COL_CRSR INTO @COL_NAME

WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM COL_CRSR INTO @COL_NAME
PRINT @COL_NAME
SELECT @STAT = 'SELECT ' + @COL_NAME +' FROM ' + @TABLE_NAME
EXEC(@STAT)

END

CLOSE COL_CRSR
DEALLOCATE COL_CRSR

/*********************[END]*********************************/

For this piece of code my purpose is solved. I am able to get the values for each column for any given table.

But I really couldn't understand the logic going behind the screnes.

Please acknowledge me by making the picture more clearer

Thanks in advance

Regards
Nikhil Mishra
mishra_nikhil@hotmail.com"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources