marrowyung
asked on
SQL SELECT multi-columns INTO multi-variable
right now try to input all variable from:
to varible I defined.
how can I fit it into many variable in one singal statement ?
this:
?
while @spid get from that:
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
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
?
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 ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
please give an example, I don't quite understand you.
Here is an example with explanations:
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.
/* 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
;
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.
ASKER
today I found that my query:
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 .
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
;
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 .
ASKER
so can't do:
in order to assign value when I do the select statment?
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
;
in order to assign value when I do the select statment?
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.
ASKER
"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:
what if there are more than one row ? how to loop the next one like what cursor do ?
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
;
what if there are more than one row ? how to loop the next one like what cursor do ?
ASKER
" 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 ?
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 ?
ASKER
then I prefer to have one more cursor which I just built one.,
ASKER
I use other method to build my solution !
ASKER
why I need this? as this one:
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 ?