Monday, January 30, 2017

Cursor with Function

DECLARE @result INT
     EXEC master.dbo.xp_fileexist '\\sharebear\client_project_folders\453064052\Allocations\Rony.txt', @result OUTPUT
Select @result

USE [TPAManager_be]
GO
/****** Object:  UserDefinedFunction [dbo].[fc_FileExists]    Script Date: 1/30/2017 05:40:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fc_FileExists](@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;


-----------------------CURSOR---ForEach Loop------------
DECLARE cur_savings CURSOR FOR
Select FilePathID,ProjectID,FilePath  from ProjectFile where AccountID=453064052 and ProjectID=50752 and WebAccess=1

DECLARE @ProjectID int,@AccountID int,@strSql VARCHAR(4000),@Path varchar(200),@cmd varchar(1000)
OPEN cur_savings

FETCH NEXT FROM cur_savings INTO @AccountID,@ProjectID,@Path

WHILE @@FETCH_STATUS = 0
BEGIN

--create table #File(AccountID Int,ProjectID Int,FileExist Bit)

insert #File
Select @AccountID,@ProjectID,dbo.[fc_FileExists] (@Path)

   FETCH NEXT FROM cur_savings  INTO @AccountID,@ProjectID,@Path
END

CLOSE cur_savings
DEALLOCATE cur_savings
GO


Select * from #File where FileExist=0

No comments:

Post a Comment