Using EXEC (string) in a function

Hi

I have a SP to retrieve the name associated with a uniqueidentifier, the SP will search all the tables based on the information provided on the lines  
SET @r = @r +1 INSERT @TABLES VALUES(@r ,'TABLE'      ,'COLUMN_ID'            , 'COLUMN_NAME'      )
turns out that now I have the need to use this SP inside SELECTS so I need to change it from an SP to a FUNCTION.

Problem: Due to the limitation on the function I can't EXECUTE the part of the code
 INSERT INTO @OutTable
                  EXEC (@SQL)
        SELECT @Name = name FROM @OutTable

can anyone suggest a solution?

keep in mind that the line
SET @r = @r +1 INSERT @TABLES VALUES(@r ,'TABLE'      ,'COLUMN_ID'            , 'COLUMN_NAME'      )
will be repeated by has many tables I have.

Below the code I created so far.
Thanks
CREATE FUNCTION [dbo].[RCT_F_IDName] (@Code uniqueidentifier, @IDTable bit = 0)
RETURNS Varchar(MAX)
AS
BEGIN

	DECLARE @Name AS VARCHAR(MAX) -- Var to get the result of the search and use to return value
	DECLARE @TABLES AS TABLE (r INT,t VARCHAR(50), c VARCHAR(50), n VARCHAR(50)) -- Table with places to search
	DECLARE @r AS INT = 0		-- Var to get row
	DECLARE @t AS VARCHAR(50)   -- Var to table name  (per row)
	DECLARE @c AS VARCHAR(50)   -- Var with column to searc code  (per row)
	DECLARE @n AS VARCHAR(50)   -- Var with column to return name  (per row)
	DECLARE @SQL AS NVARCHAR (200) -- VAR to get SQL strig (per row)
	DECLARE @OutTable AS TABLE (Name VARCHAR(100)) -- Var to get result from search sql string  (per row)

	--Insert places to search
	SET @r = @r +1 INSERT @TABLES VALUES(@r ,'RCT_COMPANY'			,'COMPANY_ID'		, 'COMPANY_Name'	)
	SET @r = @r +1 INSERT @TABLES VALUES(@r ,'RCT_COMPANY_BASE'		,'BASE_ID'			, 'BASE_Name'		)
	SET @r = @r +1 INSERT @TABLES VALUES(@r ,'RCT_COMPANY_CONTACTS'	,'CONTACT_ID'		, 'CONTACT_Name'	)
	SET @r = @r +1 INSERT @TABLES VALUES(@r ,'TABLE'	,'COLUMN_ID'		, 'COLUMN_NAME'	)
	
	-- loop until find the name or no more tables to search
	SET @r = 1
	WHILE (1 = 1) 
	BEGIN  
	
	  -- Get variables from row  	
	  SELECT TOP 1 @t = t, @c = c, @n = n
	  FROM @TABLES
	  WHERE r = @r
	
	  -- Exit loop if no more tables
	  IF @@ROWCOUNT = 0 BREAK
		  
	  -- Change to next row
	  SET @r = @r +1
	  
	  -- Create dinamic SQL string
	  SET @SQL = 'SELECT ' + @n + ' FROM ' + @t + ' WHERE ' + @c + ' = ''' + CAST(@Code AS VARCHAR(36)) + ''''
	
	  -- Insert value to table from above string and associate it to NAME
	  INSERT INTO @OutTable
			EXEC (@SQL)
	  SELECT @Name = name FROM @OutTable
	  
	  --Exit loop if find the name
	  IF NOT @Name IS NULL BREAK
	  
	END
	
	IF @IDTable = 1 SET @Name = @Name + ' (' + @t + ')'
	
RETURN @Name

END

Open in new window

Paulo LeitaoGlobal Hardware ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I think you must be able to do it by using #OutTable insteaqd of @OutTable - so #temp tables vs @table variable.
lcohanDatabase AnalystCommented:
never mind - my bad/sorry - it is actualy viceversa and you should be able to use a @table as the "RETURNS" in a table function definition.
dqmqCommented:
Sorry, you cannot use dynamic SQL within a UDF.    If you want dynamic SQL, then you need to use  CLR function.  Alternatively, there is no need for dynamic SQL in your procedure.  convert it to static SQL and it should work OK.

Something like:

create FUNCTION [dbo].[tst] (@Code uniqueidentifier, @IDTable bit = 0)
RETURNS Varchar(MAX)
AS
BEGIN
	DECLARE @Name AS VARCHAR(MAX) -- Var to get the result of the search and use to return value

    SELECT Top 1 @Name= Name FROM 
    (Select 0 as seq, 'Name' as Name, 'ID' as ID, FROM RCT_COMPANY where 1=2 
     Union all Select 1, COMPANY_Name, Company_ID FROM RCT_COMPANY 
     Union all Select 2, BASE_Name, Base_ID FROM RCT_COMPANY_BASE
     Union all Select 3, Contact_Name, CONTACT_ID FROM RCT_COMPANY_CONTACTS
    )
    where ID = CAST(@Code AS VARCHAR(36))
    order by seq
	
RETURN @Name
END

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Paulo LeitaoGlobal Hardware ManagerAuthor Commented:
Thank you all for your quick answers, the solution from dqmq worked as turn around, I was so focused on work with the "insert to" that I didn't think on a simpler solution.

However the code suggested has 2 errors.
Line 8, it has a coma before the "FROM" which generates an error (coma must be removed)
Line 12, the select must take a name, it should be something like ") AS results"

For future reference the correct code should be something like:

create FUNCTION [dbo].[tst] (@Code uniqueidentifier, @IDTable bit = 0)
RETURNS Varchar(MAX)
AS
BEGIN
	DECLARE @Name AS VARCHAR(MAX) -- Var to get the result of the search and use to return value

    SELECT Top 1 @Name= Name FROM 
    (Select 0 as seq, 'Name' as Name, 'ID' as ID FROM RCT_COMPANY where 1=2 
     Union all Select 1, COMPANY_Name, Company_ID FROM RCT_COMPANY 
     Union all Select 2, BASE_Name, Base_ID FROM RCT_COMPANY_BASE
     Union all Select 3, Contact_Name, CONTACT_ID FROM RCT_COMPANY_CONTACTS
    ) AS Results
    where ID = CAST(@Code AS VARCHAR(36))
    order by seq
	
RETURN @Name
END

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
Very good and your corrections of my typos are spot on.
Paulo LeitaoGlobal Hardware ManagerAuthor Commented:
Suggested solution worked but had errors on the code, my comment contain the fixed code.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.