Link to home
Start Free TrialLog in
Avatar of Denis Orozco
Denis OrozcoFlag for United States of America

asked on

Create SQL function passing a variable

Hi there,
I need to turn this statement into a functionwhere VARIABLE is a parameter
DECLARE @cmd nvarchar(500)
SET @cmd = 'dir \\BCLA\Documents\Images\' + 'VARIABLE\'
CREATE TABLE #DirOutput(
     files varchar(500))

INSERT INTO #DirOutput
EXEC master.dbo.xp_cmdshell @cmd

SELECT count(*)
FROM #DirOutput
WHERE files LIKE '[0-9][0-9]/%'
and files not like '%<DIR>%'

DROP TABLE #DirOutput

Open in new window

Thanks
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

stored procedure:
create procedure ExecDir 
@Directory varchar(250)
as

DECLARE @cmd nvarchar(500)
SET @cmd = 'dir \\BCLA\Documents\Images\' + @Directory + '\'
CREATE TABLE #DirOutput(
     files varchar(500))

INSERT INTO #DirOutput
EXEC master.dbo.xp_cmdshell @cmd

SELECT count(*)
FROM #DirOutput
WHERE files LIKE '[0-9][0-9]/%'
and files not like '%<DIR>%'

DROP TABLE #DirOutput

Open in new window


scalar function:
CREATE FUNCTION dbo.ExecDir(@Directory varchar(250))
RETURNS bigint 
as
begin

 declare @result bigint

DECLARE @cmd nvarchar(500)
SET @cmd = 'dir \\BCLA\Documents\Images\' + @Directory + '\'
CREATE TABLE #DirOutput(
     files varchar(500))

INSERT INTO #DirOutput
EXEC master.dbo.xp_cmdshell @cmd

SELECT @result= count(*)
FROM #DirOutput
WHERE files LIKE '[0-9][0-9]/%'
and files not like '%<DIR>%'

DROP TABLE #DirOutput
   
return @result
end

Open in new window

You can't use temporary tables in functions. The trick is to use table variable instead:
Declare @DirOutput Table (files varchar(500))

INSERT INTO @DirOutput
EXEC master.dbo.xp_cmdshell @cmd

SELECT @result= count(*)
FROM @DirOutput
WHERE files LIKE '[0-9][0-9]/%'
    AND files not like '%<DIR>%'

Open in new window

Avatar of Denis Orozco

ASKER

@Kyle
when i tried your suggestion i get this messages:
Msg 2772, Level 16, State 1, Procedure ExecDir, Line 10
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure ExecDir, Line 14
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure ExecDir, Line 18
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure ExecDir, Line 23
Cannot access temporary tables from within a function.

I think Vitor was right can't crerate table on functions.

so I tried it like this:
CREATE FUNCTION dbo.ExecDir(@Directory varchar(250))
RETURNS bigint 
as
begin

 declare @result bigint

DECLARE @cmd nvarchar(500)
SET @cmd = 'dir \\BCLA\Documents\Images\' + @Directory + '\'
Declare @DirOutput Table (files varchar(500))

INSERT INTO @DirOutput
EXEC master.dbo.xp_cmdshell @cmd

SELECT @result= count(*)
FROM @DirOutput
WHERE files LIKE '[0-9][0-9]/%'
    AND files not like '%<DIR>%'

   
return @result
end

Open in new window


I got the following message: Msg 443, Level 16, State 14, Procedure ExecDir, Line 12
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
Oh, you can't use either the INSERT in functions also.
You might need to redesign the process to achieve what you want.
Thank vitor to point that out. redesign any sugestions?
I think if you use CLR, it will give you the potential of using .NET in your SQL Server database. Here is an example of what you can do with CLR.
Looks like you'll need the storeprocedure, but you can change your main query to use a crossapply to pull of the same function.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
with that change i was able to create the function
So the issue is with the EXEC command.
which version of SQL?
You cant EXECUTE a stored procedure within a function.
You cant EXECUTE a stored procedure within a function.
True but problem is when you want to insert the return data from the Stored Procedure, the SQL Server engine will create a temporary table to execute the insert. And functions doesn't allow temporary tables and that's why it fails.