Solved

Using EXEC (string) in a function

Posted on 2012-04-05
6
337 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 500 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

742 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