Query related - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query related

Author

Topic

arvind
Starting Member

12 Posts

Posted - 2006-12-22 : 08:00:17

hallo to all
i write a query that give results which i want.
but i wnat ot short this so any body give help for
write feasible query for this.
bye
arvind

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 08:01:17

Use ORDER BY to sort.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 08:38:35

What do you mean by short?

If it is Sort, then Learn sql

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

arvind
Starting Member

12 Posts

Posted - 2006-12-29 : 06:00:27

quote:
Originally posted by arvind

hallo to all
i write a query that give results which i want.
but i wnat ot short this so any body give help for
write feasible query for this.
bye
arvind

first of all sorry for post incomplete topic,
Actually my sp is like that
CREATE PROCEDURE Dyn_GetUsersByStatusNType
@Status int,
@GuestUserTypeId int ,
@GuestRoleId int ,
@RoleId int ,
@GroupId int
AS
if @RoleId <> 0
BEGIN

Select userid,username as LoginName ,isnull(firstname ,'')+ ' ' + isnull (Middlename,'') + ' '+ isnull( LastName,'') as Name ,
Role_Master.Role_name,tblusertype.usertype,email from users join Role_Master
on(Role_master.Role_id=Users.Role_id)inner join tblusertype on (users.usertypeid=tblusertype.usertypeid)
where users.status=@Status and userid!=-1 and userid!=0 and tblusertype.usertypeid<>@GuestUserTypeId
and Role_Master.Role_Id<>@GuestRoleId AND Role_Master.Role_Id =@RoleId
and users.userid not in (Select userid from tblgroupuser where groupid=@GroupId)
UNION
Select userid,username as LoginName ,isnull(firstname ,'')+ ' ' + isnull (Middlename,'') + ' '+ isnull( LastName,'') as Name ,
Role_Master.Role_name,tblusertype.usertype,email from users join Role_Master
on(Role_master.Role_id=Users.Role_id)inner join tblusertype on (users.usertypeid=tblusertype.usertypeid)
where users.status=@Status and userid!=-1 and userid!=0 and tblusertype.usertypeid<>@GuestUserTypeId
and Role_Master.Role_Id<>@GuestRoleId AND Role_Master.Role_Id =@RoleId
and users.userid in (Select userid from tblgroupuser where groupid=@GroupId)
END
ELSE
BEGIN
Select distinct users.userid,username as LoginName ,isnull(firstname ,'')+ ' ' + isnull (Middlename,'') + ' '+ isnull( LastName,'') as Name ,
Role_Master.Role_name,tblusertype.usertype,email from users join Role_Master
on(Role_master.Role_id=Users.Role_id) join tblusertype on (users.usertypeid=tblusertype.usertypeid )
where users.status=@status and users.userid!=-1 and users.userid!=0
and tblusertype.usertypeid<>@GuestUserTypeId and Role_Master.Role_Id<>@GuestRoleId
and users.userid not in (Select userid from tblgroupuser where groupid=@GroupId)
UNION
Select distinct users.userid,username as LoginName ,isnull(firstname ,'')+ ' ' + isnull (Middlename,'') + ' '+ isnull( LastName,'') as Name ,
Role_Master.Role_name,tblusertype.usertype,email from users join Role_Master
on(Role_master.Role_id=Users.Role_id) join tblusertype on (users.usertypeid=tblusertype.usertypeid )
where users.status=@status and users.userid!=-1 and users.userid!=0
and tblusertype.usertypeid<>@GuestUserTypeId and Role_Master.Role_Id<>@GuestRoleId
and users.userid in (Select userid from tblgroupuser where groupid=@GroupId)
END

Now i required which user are selected in a prticular group then these selected user show first and unselected users after that when i pass groupid and other value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources