Create function to execute code

Question

I would not normally use a function to execute code, but my boss is insisting on me using a function instead of an SP to execute SSIS packages. Don't really know why!

I need to create a function that will execute the following code and the user only need to provide the SSIS package name when calling the function.

declare @cmdtest varchar(200)
set @cmdtest = 'dtexec /f "C:\Users\Desktop\testpackage.dtsx"'
exec master..xp_cmdshell @cmdtest

This code above will execute my testpackage.dtsx, now my function must just ask for the SSIS package name and must be a varchar.

Since I am not clued up on functions can someone please show me what the best way of doing this is. I assume a Scalar Function needs to be used?

I don't think your boss understands what a function does in SQL Server. Have them read this, for example: http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server ... or find out why they insist on a function and share that information with us. — Aaron Bertrand, Jul 25 '12 at 11:45

score 3 · Accepted Answer · answered Jul 25 '12 at 11:45

3

Maybe something like this:

CREATE FUNCTION dbo.ExecutePackage
(
    @PackageName VARCHAR(30)
)
RETURNS BIT
AS 
BEGIN
    DECLARE @cmdtest varchar(200);
    SET @cmdtest = 'dtexec /f "C:\Users\Desktop\'+ @PackageName + '.dtsx"';
    EXEC master..xp_cmdshell @cmdtest;

    RETURN 1; --you could do some error checking and return either true or false - just a thought
END

Then you execute it as

SELECT dbo.ExecutePackage('testpackage');

answered Jul 25 '12 at 11:45

tobias86

4,979
1
21
30

4

I disagree with this approach. You're shoe-horning a function into a purpose not meant for a function, and you need to enable xp_cmdshell on the server to do so, and you will probably need to elevate rights for the caller as well. – Aaron Bertrand Jul 25 '12 at 11:46
2

@AaronBertrand: I fully agree with you. Just answering the OP's question. There are always more ways to skin a cat. – tobias86 Jul 25 '12 at 11:48
xp_cmdshell is enabled on the server – Etienne Jul 25 '12 at 11:49
8

@Etienne that doesn't make a function the right way to do this. I might have an ejector seat in my car but it's not the right way to drop my mother-in-law off at the mall. – Aaron Bertrand Jul 25 '12 at 11:50
@AaronBertrand I agree, but one have to do what the boss say you must do hey. – Etienne Jul 25 '12 at 12:11
1

@Etienne like jump off a bridge? Sometimes you can question the boss and ask ***WHY***. – Aaron Bertrand Jul 25 '12 at 12:13
I think it is easier to explain to the boss why using a function is a bad idea in this case when you show the pitfalls of the implementation. Just saying functions aren't meant to do this is an overly abstract explanation to someone who doesn't fully understand the problem. – JeffO Jul 25 '12 at 12:51
@Etienne: I think you should try to get your boss to explain to you exactly why he needs it in a function. Maybe he's just confusing functions and stored procedures? – tobias86 Jul 25 '12 at 12:57
@tobias86 I agree, going to tell him that I created what he asked but I will ask him why. I he very senior so I will have to ask him in a nice way and tell him that we should rather use a SP. – Etienne Jul 26 '12 at 05:27
What would be the best practice here? – TheL0grus Jan 19 '17 at 17:21

Create function to execute code

1 Answers1