cursor

What' s wrong with this code?
Declare @.UDName varchar(150)
Declare @.UName varchar(50)
Declare @.Domain varchar(50)
Declare @.EMail varchar(20)
Declare @.GName varchar(50)
Declare @.Description varchar(150)
Declare @.FName varchar(50)
Declare @.ShortName varchar(150)
Declare UserCursor Cursor
For
SELECT ( UsrUsers.[Domain] + '' + UsrUsers.UserName) AS
UDName, UsrUsers.UserName as UName, UsrUsers.[Domain],UsrUsers.EMail,
UsrGroups.Name AS GName, UsrGroups.Description,
UsrFunctions.Name AS FName, UsrFunctions.ShortName AS
ShortName
FROM UsrUsers INNER JOIN
UsrUsersGroups ON UsrUsers.UserID =
UsrUsersGroups.UserID INNER JOIN
UsrFunctions INNER JOIN
UsrFunctionsGroups ON UsrFunctions.FunctionID =
UsrFunctionsGroups.FunctionID INNER JOIN
UsrGroups ON UsrFunctionsGroups.GroupID =
UsrGroups.GroupID ON UsrUsersGroups.GroupID = UsrGroups.GroupID
ORDER BY UName,GName,FName
open UserCursor
fetch next from UserCursor
into
@.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.ShortName
while @.@.fetch_status = 0
begin
select
@.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.ShortName
end
close UserCursor
deallocate UserCursor
I run it in sql query analizer and it goes on and on until 'Not Responding'
HrckoTechnically, you need a FETCH inside the loop, else you are working on the s
ame row endlessly. But
the cursor seems totally meaningless to me. Why not just have the SELETE sta
tement as is? What is
the purpose of using a cursor here? Why do you want to return one table for
each row, each table
containing one row?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:djl4p1$omg$1@.ss405.t-com.hr..
.
> What' s wrong with this code?
> Declare @.UDName varchar(150)
> Declare @.UName varchar(50)
> Declare @.Domain varchar(50)
> Declare @.EMail varchar(20)
> Declare @.GName varchar(50)
> Declare @.Description varchar(150)
> Declare @.FName varchar(50)
> Declare @.ShortName varchar(150)
> Declare UserCursor Cursor
> For
> SELECT ( UsrUsers.[Domain] + '' + UsrUsers.UserName) AS UDName, UsrU
sers.UserName as
> UName, UsrUsers.[Domain],UsrUsers.EMail, UsrGroups.Name AS GName, UsrGroups.Descri
ption,
> UsrFunctions.Name AS FName, UsrFunctions.ShortName AS
ShortName
> FROM UsrUsers INNER JOIN
> UsrUsersGroups ON UsrUsers.UserID = UsrUsersGroups.Us
erID INNER JOIN
> UsrFunctions INNER JOIN
> UsrFunctionsGroups ON UsrFunctions.FunctionID = UsrFu
nctionsGroups.FunctionID
> INNER JOIN
> UsrGroups ON UsrFunctionsGroups.GroupID = UsrGroups.G
roupID ON
> UsrUsersGroups.GroupID = UsrGroups.GroupID
> ORDER BY UName,GName,FName
> open UserCursor
> fetch next from UserCursor
> into
> @.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.ShortName
> while @.@.fetch_status = 0
> begin
> select @.UDName,@.UName,@.Domain,@.EMail,@.GName,@.De
scription,@.FName,@.Shor
tName
> end
> close UserCursor
> deallocate UserCursor
> I run it in sql query analizer and it goes on and on until 'Not Responding
'
> Hrcko
>|||I have to read all the users from the table first and then functions.
I need it for report.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uLX0HiV2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Technically, you need a FETCH inside the loop, else you are working on the
> same row endlessly. But the cursor seems totally meaningless to me. Why
> not just have the SELETE statement as is? What is the purpose of using a
> cursor here? Why do you want to return one table for each row, each table
> containing one row?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:djl4p1$omg$1@.ss405.t-com.hr...
>|||>I have to read all the users from the table first and then functions.
I'm afraid that I don't understand what you mean by "and then function"...
Just be aware that using a cursor is often *much* slower, and often you have
to write more code
which is harder to maintain, compared to a single SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message news:djl5p1$qnf$1@.ss405.t-com.hr..
.
>I have to read all the users from the table first and then functions.
> I need it for report.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uLX0HiV2FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||I have to tables: Users and Functions.
In my report(crystal report 11) i have to read all data from table Users and
then all data from table Functions, because in Detail section it reads
record by record - so I get a line with user name and then a line with
function name. I don't want that.
I want all users and then all functions.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcMatwV2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> I'm afraid that I don't understand what you mean by "and then
> function"...
> Just be aware that using a cursor is often *much* slower, and often you
> have to write more code which is harder to maintain, compared to a single
> SELECT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hrvoje Voda" <hrvoje.voda@.luatech.com> wrote in message
> news:djl5p1$qnf$1@.ss405.t-com.hr...
>|||As Tibor indicated, you'll probably be better off not using a cursor.
To help you with that, please post DDL, sample data and show your
required results. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Those are two tables, which translates to two resultsets, which in turn give
s
you a typical master/detail dataset to work with. What's wrong with that?
ML