Not In Query - 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 2008 Forums

Other SQL Server 2008 Topics

Not In Query

Author

Topic

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-27 : 09:45:31

Hi Everyone,
please refer the following query. it's bring the result after 15 seconds. please simplify this query.

select distinct top 15 '' as sel,RecordId,menuDesc,MenuID
from dbo.table_LIST('admin','xxx','zzz')
where len(RecordId) >3
and RecordId not in
(select distinct top 0 RecordId
from dbo.table_LIST('admin','xxx','zzz')
where len(RecordId) >3 order by RecordId) order by RecordId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 09:50:31

without seeing what table_LIST does its difficult to suggest. can you see execution plan and see what it suggests

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Dhanushkodi
Starting Member

21 Posts

Posted - 2013-06-27 : 10:04:24

table list is the function. Function is following.

ALTER FUNCTION [dbo].[table_LIST](@User varchar(100),@SuperUSer varchar(100),@InType varchar(100))
RETURNS @Result TABLE (RecordId varchar(200),menuDesc varchar(800),MenuId int)
AS
BEGIN
Declare @userId int
Declare @ModuleIdlike varchar(200)
set @userId=0

select @userId=inUserId from table1 where vcDelflag='False' and vcUSerName=@User
if @User=@SuperUSer
begin
if @InType ='material'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and len(vcRecordId) > 3 and btMaterial=1
end
else if @InType='services'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and len(vcRecordId) > 3 and btServices=1
end
else
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and len(vcRecordId) > 3 and btServices=1 and btMaterial=1
end
End
else
begin
declare ModuleId_List cursor for select distinct a.vcRecordId as modID from table2 a inner join table3 b on a.inModuleScreenId=b.vcModuleId and
b.vcdelFlag='False' and b.vcItemNatureType=@InType and (b.inUsrorGrpId=@userId and b.vcAuthorizationLevel='user' ) or
(b.inUsrorGrpId in(select distinct inSgroupId from table4 where inUserId=@userId
and vcItemNatureType=@InType and vcDelFlag='False') and b.vcAuthorizationLevel='group')

open ModuleId_List
fetch next from ModuleId_List into @ModuleIdlike
WHILE @@FETCH_STATUS=0
BEGIN
if @InType ='material'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and vcRecordId like @ModuleIdlike+'%' and len(vcRecordId) > 3 and btMaterial=1
end
else if @InType ='services'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and vcRecordId like @ModuleIdlike+'%' and len(vcRecordId) > 3 and btServices=1
end
else
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and vcRecordId like @ModuleIdlike+'%' and len(vcRecordId) > 3 and btMaterial=1 and btServices=1
end

FETCH NEXT FROM ModuleId_List into @ModuleIdlike
END
CLOSE ModuleId_List
DEALLOCATE ModuleId_List

end
RETURN

END

please give me a solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 11:58:10

can you explain what you're trying to do inside udf? its quite difficult from your current code.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources