SQL SELECT multi-columns INTO multi-variable

right now try to input all variable from:

SELECT c.session_id as "session id", c.name, c.properties as "The cursor properties", c.creation_time as "Cursor creation time", c.is_open as "is the Cursor opened" , t.text as "The SQL Query"

FROM sys.dm_exec_cursors (54) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 

Open in new window


to varible I defined.

how can I fit it into many variable in one singal statement ?

 this:

SELECT @Cursor_SessionID = c.session_id, @Cursor_Name= c.name, @Cursor_Pro = c.properties,
@Cursor_CreationTime = c.creation_time, c.is_open as "is the Cursor opened" , 
@Cursor_query= t.text

FROM sys.dm_exec_cursors (@spid) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 

Open in new window


?

while @spid get from that:
DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (minute, login_time, GETDATE()) > 
@Total_time_Executed  ;

Open in new window

LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
can I have more than one cursor inside a singal SP in MS SQL ?

why I need this? as this one:

SELECT @Cursor_SessionID = c.session_id, @Cursor_Name= c.name, @Cursor_Pro = c.properties,
@Cursor_CreationTime = c.creation_time, c.is_open as "is the Cursor opened" , 
@Cursor_query= t.text

FROM sys.dm_exec_cursors (@spid) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t  

Open in new window


even the same session_id, can have more than one statement returned.

Or how can we loop out the result of this statment one by one and fit into our variable ?
0
Kevin CrossChief Technology OfficerCommented:
You could use a table variable (or user-defined data type) to store the combinations if I understand your need correctly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
please give an example, I don't quite understand you.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kevin CrossChief Technology OfficerCommented:
Here is an example with explanations:
/* Table variable to hold cursor information. */
DECLARE @cursors TABLE (
    sessionID INT,
    name NVARCHAR(128),
    properties NVARCHAR(128),
    creationTime DATETIME,
    isOpen BIT,
    querySQL NVARCHAR(MAX)
);

/* 
Query cursors dynamic management view, filling above table variable.
http://technet.microsoft.com/en-us/library/ms190346.aspx 
http://technet.microsoft.com/en-us/library/ms181929.aspx
*/
INSERT INTO @cursors(
    sessionID,
    name,
    properties,
    creationTime,
    isOpen,
    querySQL
)
SELECT c.session_id as "session id"
     , c.name
     , c.properties as "The cursor properties"
     , c.creation_time as "Cursor creation time"
     , c.is_open as "is the Cursor opened"
     , t.text as "The SQL Query"
FROM sys.dm_exec_cursors(0) c 
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
;

/* Use table variable in later part of procedure. */
SELECT *
FROM @cursors
;

Open in new window


I hope that helps.

Regarding your question on multiple cursors, yes there can be more than one.  Here is Microsoft's example: http://msdn.microsoft.com/en-us/library/ms180169.aspx.
0
marrowyungSenior Technical architecture (Data)Author Commented:
today I found that my query:

SELECT c.session_id as "session id"
     , c.name
     , c.properties as "The cursor properties"
     , c.creation_time as "Cursor creation time"
     , c.is_open as "is the Cursor opened"
     , t.text as "The SQL Query"
FROM sys.dm_exec_cursors(0) c 
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
;

Open in new window


only return on row, which is perfect ! But not all the time like that.

so the temp table @cursors will have more than one row, then how can handle it one by one?

so I need 2 x cursor for that, but this link http://msdn.microsoft.com/en-us/library/ms180169.aspx.  means i can do it !!

did you try this beofre? MySQL can't do this !! one SP can only have 1 x cursor .
0
marrowyungSenior Technical architecture (Data)Author Commented:
so can't do:


SELECT @A = c.session_id 
     , @B= c.name
     , @C= c.properties 
     , @D= c.creation_time
     , @E= c.is_open
     , @F= t.text 
FROM sys.dm_exec_cursors(0) c 
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
;

Open in new window


in order to assign value when I do the select statment?
0
Kevin CrossChief Technology OfficerCommented:
Short answer, no.  You cannot do it that way.  You can concatenate values into variables, but I suspect you want the related individual values to use one at a time.  Therefore, you would do it like I showed with a table, then you could pull rows from the table one at time for further use.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Short answer, no."

I didn't test this one yet but I google that

"but I suspect you want the related individual values to use one at a time. "

I just want to ASSIGN the value of result to my variable directly from the query so tha t I can do some logic once after that .

"Therefore, you would do it like I showed with a table, then you could pull rows from the table one at time for further use. "

you query is this:

SELECT *
FROM @cursors
; 

Open in new window


what if there are more than one row ? how to loop the next one like what cursor do ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
" Therefore, you would do it like I showed with a table, then you could pull rows from the table one at time for further use"

I think you are talking about:

/* Use table variable in later part of procedure. */
SELECT *
FROM @cursors
;

but by this can't insert any string in between to make the whole string meaning ful and can't make the lower level support understand what it is, as the result of it will only show rows of result and we can't add string to it and show the messag in the way we want, right ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
then I prefer to have one more cursor which I just built one.,
0
marrowyungSenior Technical architecture (Data)Author Commented:
I use other method to build my solution !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.