?
Solved

Using EXEC (string) in a function

Posted on 2012-04-05
6
Medium Priority
?
339 Views
Last Modified: 2012-04-10
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

0
Comment
Question by:Leitao
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 37812401
I think you must be able to do it by using #OutTable insteaqd of @OutTable - so #temp tables vs @table variable.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37812450
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.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 2000 total points
ID: 37812659
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

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Accepted Solution

by:
Leitao earned 0 total points
ID: 37814570
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

0
 
LVL 42

Expert Comment

by:dqmq
ID: 37814647
Very good and your corrections of my typos are spot on.
0
 

Author Closing Comment

by:Leitao
ID: 37826823
Suggested solution worked but had errors on the code, my comment contain the fixed code.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question