-1

Using SQL Server 2014...

I'm trying to convert this code to be used in a function... 

SET @sqlCommand = N'SELECT @text = ' + @fieldname + ' FROM ' + @tablename +' WHERE ID=''' + CONVERT(NVARCHAR(200), @ID)  + ''''
EXECUTE sp_executesql...

...but functions doesn't accept sp_executesql.

How can I in a function get data from a table where the tablename is coming from a parameter?

Pranav Singh
  • 17,079
  • 30
  • 77
  • 104
MojoDK
  • 4,410
  • 10
  • 42
  • 80
  • @Tanner no duplicate there, my question is about dynamic in a FUNCTION. – MojoDK Jul 09 '14 at 08:00
  • Ok, have retracted, what's the reason for using a function rather than stored proc? – Tanner Jul 09 '14 at 08:12
  • This is a similar question: http://stackoverflow.com/questions/150552/executing-dynamic-sql-in-a-sqlserver-2005-function – Tanner Jul 09 '14 at 08:12
  • @Tanner I need to run the function many times during a single stored proc, so to keep code to a minimum I want to use functions. – MojoDK Jul 09 '14 at 09:42
  • @Tanner the similar question you refer to is for SQL Server 2005 - lots must have happen since then. – MojoDK Jul 09 '14 at 09:42
  • You haven't stated in the question or tags what version you're using – Tanner Jul 09 '14 at 09:55

2 Answers2

-1

Have no way to do this. If you want you can use some options, like:

case when @TableName = 'X' then
   ...
when @TableName = 'Y' then
   ...
else
   ...
inon
  • 1,689
  • 1
  • 19
  • 34
-1

Try This:

SET @sqlCommand = N'SELECT ' + @fieldname + ' FROM ' + @tablename +' WHERE ID=''' +   CONVERT(NVARCHAR(200), @ID)  + ''''
EXEC (@sqlCommand)

I removed the '@text =' The reuslt wil be given by the query

user3815462
  • 61
  • 2