SQL Server Performance Forum – Threads Archive

Help , How to loop in all the field of one record

Recently I have one big problem , need every one help.
I need One function can let me loop in all the field of select result in SP.
Thank You very much!!! example:
select * from movingticketdetail , for this sql script , how can not loop every field value and field name?

I’m having a little trouble understanding the question. Read this, and try again to see if we can get you help a little faster. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Declare an appropriate variable for each column that you want to include in the loop, then open a cursor for all the columns from the table, and in each iteration you have the values in the variables. In case you didn’t know, a FETCH operation can cover all columns from the cursor in one go: DECLARE @Var1 INT, @Var2 INT
DECLARE crsr CURSOR FORWARD READ_ONLY
FOR
SELECT col1, col2 FROM MyTable
OPEN crsr
FETCH NEXT FROM crsr INTO @Var1, @Var2 etc. etc.

Hi my friend , thank you for your two guys’s help.
but as you know, before I declare the cursor, I did not know the col name , I just need one power function or idea like the recordset as VB, that can loop in all the field. In sql server, we have to know the col name and col count before we declare the cursor.
Thank you , can you help me?

Unfortunately there are no abstract objects in T-SQL, meaning you have to know ahead of time what data you’re handling. By the way, when you declare the cursor you should already know the column’s name – or at the very least you should pay the courtesy to look up the column names for the table you’re "opening". And then I start wondering if you are aware that a column in a database is not just a heap of bits of data but actually a well-considered and structured object to provide the user with a concise and predictable amount of information (EOR – end of rant).

select sc.name,st.name,st.xtype from syscolumns sc
inner join systypes st on sc.xtype=st.xtype
where id=object_id(‘tablename’) this will give the columns and data type for that table.

Please post DDL, sample data and desired output. To me it’s still not clear what you’re after… —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>