How to suppress messages returned by sqlcmd while executing stored procedure from function?

Question

For some reason, I had to execute stored procedure from a function and since it is not possible and I tried as explained at Stackoverflow :: Execute Stored Procedure from a Function

Instead osql, I tried sqlcmd. It worked but I get multiple blank rows, rows with ------ and rows with NULL value.

This function can not be changed to stored procedure.

Is there any way to suppress these messages and unwanted rows ?

Or is there any alternative for executing stored procedure inside functions ?

enter image description here

Sql:

master..xp_cmdshell 'sqlcmd -COM252\INSTANCE2008 -E -q 
       "SET NOCOUNT ON;
        exec MyDatabaseName..storedProcName ''param1'', ''param2'' "'

I even tried pushing on temporary tables but unfortunately again, temporary tables can not be accessed on function.

IMHO, you need to move that logic from a stored procedure to something else. Do whatever it does in a function, then you can call the function from a function or a stored procedure. — Aaron Bertrand, Feb 05 '14 at 21:13

score 0 · Answer 1 · answered Feb 05 '14 at 22:42

0

Haven't tried it, but how about this?

DECLARE @dummy TABLE(f1 varchar(max));

INSERT @dummy (f1)
EXEC master..xp_cmdshell 'sqlcmd -COM252\INSTANCE2008 -E -q 
       "SET NOCOUNT ON;
        exec MyDatabaseName..storedProcName ''param1'', ''param2'' "'

answered Feb 05 '14 at 22:42

Kevin Suchlicki

3,096
2
15
17

In fact I am going to execute this on SQL function. This error `Invalid use of a side-effecting operator 'INSERT EXEC' within a function.` will be fired if I attempt your solution. – hsuk Feb 05 '14 at 23:13

How to suppress messages returned by sqlcmd while executing stored procedure from function?

1 Answers1