Solved

Using EXEC (string) in a function

Posted on 2012-04-05
6
335 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 39

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 39

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach 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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
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 …

786 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