Solved

SQL SELECT multi-columns INTO multi-variable

Posted on 2014-02-25
11
439 Views
Last Modified: 2014-03-12
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

0
Comment
Question by:marrowyung
  • 8
  • 3
11 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 39885140
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39885472
You could use a table variable (or user-defined data type) to store the combinations if I understand your need correctly.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39885596
please give an example, I don't quite understand you.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39886457
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39887893
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39888015
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39888570
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39891272
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 39911925
" 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
 
LVL 1

Author Comment

by:marrowyung
ID: 39925506
then I prefer to have one more cursor which I just built one.,
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39925507
I use other method to build my solution !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Connect to an SQL server ActiveX 10 27
SQL Query 2 34
Error when creating a table from a function 6 20
Parse this column 6 27
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question