[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

array in Stored procedure??

greetings,
below is my code,

while @i<5
begin

select @filename = '\\agmpimo173\Brio_Schedule\'+ @DailyFile.(i) +''      
EXEC master..xp_fileexist @filename, @return OUTPUT
print @return
set @i = @i + 1

end

will i cannot really execute the code above to run and print the result as well... please help.. many thanx

me, Yee
0
mingfattt
Asked:
mingfattt
  • 7
  • 3
  • 2
  • +2
1 Solution
 
Anthony PerkinsCommented:
You can INSERT the results in a temporary table (or variable of type table) and at the end select from that table.
0
 
appariCommented:

can you explain what exactly are you trying to do?

are you trying to find if file exists or not(filename is somefilename.X where X is 1 to 5)?
if that is the case try something like this


while @i<5
begin

select @filename = '\\agmpimo173\Brio_Schedule\'+ @DailyFile + "." + i +''    
EXEC master..xp_fileexist @filename, @return OUTPUT
print @return
set @i = @i + 1

end
0
 
mingfatttAuthor Commented:
i am a newbie here, can you show me the way? i mean how am i going to stored it inside the table... do i need to make it one by one? can i use the looping in this case?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
mingfatttAuthor Commented:
ya i want the system return me the value either 1 for exist and 0 for not... but appari the system keep asking me to declare the DailyFile meanwhile i need it to execute the term like

DailyFile1,DailyFile2

which i had been declared earlier on.....which each of this refer to particular file... please advise

Me, Yee
0
 
ram2098Commented:
appari's code works for you.

Try it as below...



while @i<5
begin

select @filename = '\\agmpimo173\Brio_Schedule\'+ @DailyFile + i    
EXEC master..xp_fileexist @filename, @return OUTPUT
print @return
set @i = @i + 1

end
0
 
mingfatttAuthor Commented:
sorry to say that this is not really what i want you see here is my means

@Dailyfile1 = openpo.txt
@Dailyfile2 = req.txt

i want the code to detect one by one whether each of this file is exist... anyway thanx very much



0
 
ram2098Commented:
not sure whether I got your requirement...

Do you want to check only there two files (or) more? And what is the source for these filenames..if it is one time hard-code, put them above the sql statement.

Declare @dailyFile1 varchar(50),
..........

SELECT @Dailyfile1 = openpo.txt
SELECT @Dailyfile2 = req.txt

while @i<5
begin

select @filename = '\\agmpimo173\Brio_Schedule\'+ @DailyFile + i    
EXEC master..xp_fileexist @filename, @return OUTPUT
print @return
set @i = @i + 1

end
0
 
mingfatttAuthor Commented:
well but i cannot see the result in the sql analyzer... well my expected result will be

example
1
0
1
1
1

which this will give me the image where Dailyfile1 is exist due to
when i = 1

the statement
select @filename = '\\agmpimo173\Brio_Schedule\'+ @DailyFile + i    

will actually alias to the \\agmpimo173\Brio_Schedule\'+ @DailyFile1' 'so on and so fore for @DailyFile2... when the increment of i is being done

Thanx for the reply.

Me, Yee
0
 
HilaireCommented:
Here's an idea how you could do it

-- 1) create a user-defined function
create function dbo.ufn_fileexists(@filename varchar(200))
returns int
as
begin
      declare @return int
      EXEC master..xp_fileexist @filename, @return OUTPUT
      return @return
end

-- 2) in you current code store the file names in a table variable to emulate the array
declare table @temp (my_filename varchar(500))
-- 3) populate the array
insert @temp values ('openpo.txt')
insert @temp values ('req.txt')
--and so on, with as many files as you want
-- it could also be a static table if file names never change
-- 4) check file existence for each and every file

select my_filename, dbo.ufn_fileexists('\\agmpimo173\Brio_Schedule\' + my_filename) as flag_exists
from @temp T

0
 
mingfatttAuthor Commented:
can you make it clearer please thanx
0
 
HilaireCommented:
Arrays are not supported in SQL Server,
instead, you can use comma-seaprated lists of values, XML, or which is the primary feature of a relational DBMS, a TABLE.

Making the ufn_fileexists a function allows you to call it in a select statement : As a relational DBMS, SQL server is better a select statements than branching code and cursors/loops, and the code is also less verbose.

Once the function is created (once for all), you can use it whenever you want.

Now to use it :
The list of file names has to be stored somewhere, right ?

If it's not the case, you can store them in a temporary table variable and then make a select statement on the table to check file existence for each and every file in the table.

If you already have the file names in a regular/static table (I mean a normal table in your schema, as opposed to the temporary table) somewhere in your DB, then you just have to issue a select statement on this table

select your_filename, dbo.ufn_fileexists('\\agmpimo173\Brio_Schedule\' + your_filename) as flag_exists
from your_table
0
 
mingfatttAuthor Commented:
i will try it out tomorrow and let you know then... thanx and likes to meet you

cheers
Yee
0
 
mingfatttAuthor Commented:
where should i create the function? is it inside the same code?
0
 
HilaireCommented:
Just run the "create function" code in Query Analyser
Make sure you select the correct database,
then paste the code in the Query Analyser window, select the code, and press F5

You have to do it just once.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now