Call a stored procedure with parameters in a user defined function in SQL SERVER
Hi, I want to call a stored procedure in a userdefined function, the stored procedure takes a parameter to filter the values from a table. As given in the below site,
http://www.dotnetfunda.com/forums/show/8955/how-can-call-stored-procedure-inside-function-in-sql-server-2005 I tried but 'm not able to pass the parameter of the stored procedure in the OPENROWSET function. Create Function dbo.Function1() Returns @Result Table(Result Varchar(100)) As Begin Insert @Result SELECT * from OPENROWSET('SQLNCLI10', 'Server= ;UID= ;Pwd= ;','Exec dbo.Proc1') AS C Return end Go This above function worked for me, but I have to pass a parameter to the stored procedure dynamically. I tried to declare a parameter in the function like, Create Function dbo.Function1(@PARAM INT) and then called it in the OPENROWSET as shown below, SELECT * from OPENROWSET('SQLNCLI10', 'Server=;UID=;Pwd=;','Exec dbo.Proc1 @PARAM') AS C But this does not work, can anyone help me on how to give the parameter in the stored procedure when we call it in a function. Thanks
Instead, I'm going to suggest you back out of the use of the function and the procedure in this case. You're starting to nest your code and SQL Server never deals well with that. Instead, identify the code you need in the function and replicate it there, if you have to use a function.
As @Grant Fritchey already pointed out, there is no good reason to nest the code in this case. The idea may look appealing because of the possibility of the "code reuse" but in reality it leads to poor performance and unwanted complexity whenever there is a need to troubleshoot the problems. If you really need to call the remote stored procedure which needs a paramater via openrowset then I can show you one of the ways to make it happen by using a dynamic SQL. The idea is to simply generate a statement which can be then executed via exec (or sp\_executesql): -- this is a parameter which is used when generating dynamic statement declare @param1 int = 83650 -- = some numeric value; -- this is a table variable (if it is needed for whatever reason) declare @Result table (Result varchar(100)); declare @sql nvarchar(1000) = ' select * from openrowset( ''SQLNCLI10'', ''Server=SomeInstance;UID=some_user;Pwd=some_pwd;'', ''exec SomeDatabase.dbo.Proc1 @param1=' + cast(@param1 as varchar(10)) + ''' )'; insert into @Result exec sp_executesql @statement = @sql; -- now the table variable has been populated with some data as -- a result of executing the stored proc from remote instance select * from @Result The statement above can be used as a wrapper to the remote procedure call by the means of the stored procedure, but not the function. The reason that the function cannot be used is because the table valued functions cannot be created if either **EXEC** or **INSERT INTO (some table) EXEC (some proc)** are used. Attempt to use the former will result in "**Invalid use of a side-effecting operator 'EXECUTE STRING' within a function**" and the use of the latter in "**Invalid use of a side-effecting operator 'INSERT EXEC' within a function**". Wrapping this code into the stored proc is possible but still is ill advised. For example, at this time the @param1 is integer, but what about if the same technique needs to be used with varchar parameter? This will open the door to SQL injection: create proc dbo.LocalProc1 (@param1 int) as begin; declare @sql nvarchar(1000) = ' select * from openrowset( ''SQLNCLI10'', ''Server=SomeInstance;UID=some_user;Pwd=some_password;'', ''exec SomeDatabase.dbo.Proc1 @param1=' + cast(@param1 as varchar(10)) + ''' )'; exec sp_executesql @statement = @sql; end; go It is easy to see that the code above is vulnerable to injection if the procedure's parameter is changed to varchar of some reasonable size. Oleg