Solved

Using EXEC (string) in a function

Posted on 2012-04-05
6
333 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now