Problem with Dynamic Sql in a function called from a SELECT statement

Question

Problem with Dynamic Sql in a function called from a SELECT statement

Wizgod

SSC Rookie

Points: 39
More actions
November 16, 2006 at 10:31 pm

#121570

Hi,
I need to get the value from Table1.FieldName and retrieve the value from Table2.SomeColumn where the name of the column in Table2 is the value in Table1.FieldName.
I'm using a function and extended sproc sp_executesql which works fine if I call the function on it's own but i get an error if i call it within a SELECT statement:
SELECT Table1.FieldType,
     CASE Table1.FieldType
          WHEN 'Dynamic' THEN dbo.fn_GetColumn(Table1.FieldName, Table2.ID)
          ELSE Table1.FieldName
      END
FROM Table1, Table2
WHERE Table1.GroupID = Table2.GroupID
The function is:
CREATE FUNCTION dbo.fn_GetColumn
@column varchar(100),
@id int
RETURNS varchar(500)
AS
BEGIN
     DECLARE @SQLString NVARCHAR(500)
     DECLARE @ParmDefinition NVARCHAR(500)
     DECLARE @result varchar(500)
     SET @SQLString = N'SELECT @resultOUT = ' + @column +
                                 'FROM Table2 WHERE Id = @idIN'
     SET @ParmDefinition = N'@idIN int,
                                       @resultOUT varchar(500) OUTPUT'
     EXECUTE sp_executesql
     @SQLString,
     @ParmDefinition,
     @idIN = @id,
     @resultOUT=@result OUTPUT

     RETURN @result
END
I appreciate any help, suggestions or solutions you can offer.
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

1. When posting about an error, please post the error message. The exact error message, not a description.

2. When you say calling the function on it's own, do you mean using Execute dbo.fn_GetColumn. Could you be more specific about the situation in which it works.

3. You can not use sp_executesql inside a function, so there is no way to make that function work.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ijaz SSCommitted Points: 1912 More actions · Answer 2

Hi,

I would be more useful if you can mentioned the error you are getting.

I think that error is because you are using the table2 in the outer and same in your function. I think that due to this the current record is locked and you are trying to get this in your function.

Try to use WITH (NOLOCK)

hint in your query may be it will slove your problem.

SELECT Table1.FieldType,

CASE Table1.FieldType

WHEN 'Dynamic' THEN dbo.fn_GetColumn(Table1.FieldName, Table2.ID)

ELSE Table1.FieldName

END

FROM Table1, Table2 WITH (NOLOCK)

WHERE Table1.GroupID = Table2.GroupID

cheers

ijaz SSCommitted Points: 1912 More actions · Answer 3

Hi,

Roberts you are right I did not care in my response about that there is no option avaiable to execute the dynamic sql in the function.

You are right that "You can not use sp_executesql inside a function, so there is no way to make that function work"

Cheers

cheers

Wizgod SSC Rookie Points: 39 More actions · Answer 4

Hi Guys,

Since sp_executesql is an extended stored procedure, I can run it from within a function and I am able to using the following which returns the expected result:

DECLARE @result varchar(100)

DECLARE @fieldname varchar(100)

DECLARE @id int

SET @fieldname = 'alias'

SET @id = 3

EXEC

@result = dbo.fn_GetColumn @fieldname, @id

PRINT

@result

But I get the following error when I try to run it from within the SELECT statement:

Only functions and extended stored procedures can be executed from within a function.

Since sp_executesql is an extended sproc, I shouldn't be getting this error but obviously i'm missing something.

Thanks

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

>>Since sp_executesql is an extended sproc

sp_executesql is not an extended sproc. System extended sprocs begin with the "xp_" prefix, eg xp_cmdshell.

You can't run dynamic SQL inside a function.

Wizgod SSC Rookie Points: 39 More actions · Answer 6

It is listed as an extended procedure but I found the following which clears things up. However, it does work from within a function when the function is executed with EXEC but not in a SELECT statement (most likely because it's not a true sproc or ext. sproc as described below).

In which case, would you (or anyone) have a solution for what I am trying to do - grab a value from a column in one table and get the value of the column (having the name of the value in the first column) in another table? (I'd prefer to change the structure but it's the company's database and i have to use it as is).

Thanks

Internal Procedures

A number of system-supplied stored procedures are neither true system procedures nor extended procedures—they're implemented internally by SQL Server. Examples of these include sp_executesql, sp_xml_preparedocument, most of the sp_cursor routines, sp_reset_connection, and so forth. These routines have stubs in master..sysobjects, and are listed as extended procedures, but they are actually implemented internally by the server, not within an external ODS-based DLL. This is important to know because you cannot drop these or replace them with updated DLLs. They can be replaced only by patching SQL Server itself, which normally only happens when you apply a service pack.