use table name as parameter in sql function

Question

I want create function, which use table name as parameter. As I search I need use dynamic sql. I try such code:

CREATE FUNCTION get_column_id
    (
    @TableName VARCHAR(30),
    @ColumnName VARCHAR(30),
    )
RETURNS int 
AS
BEGIN
IF EXISTS 
    (
    DECLARE @SQL VARCHAR(50)
    SET @sql = 'SELECT' + @ColumnName + 'FROM' + @TableName + 'WHERE @ColumnName =    @ColumnNameValue';
    EXEC(@sql)
    )
BEGIN

But get errors. Is where any way to procceed this?

I try use dynamic sql in such way

DECLARE @SQL VARCHAR(50)
SET @SQL = 'SELECT' + @ColumnName + 'FROM' + @Table + 'WHERE @ColumnName = @ColumnNameValue'
EXEC(@SQL)
DECLARE @TableName table (Name VARCHAR(30))
INSERT INTO @TableName VALUES (@SQL)
IF EXISTS 
    (SELECT Name FROM @TableName WHERE Name = @ColumnNameValue)

But get Invalid use of a side-effecting operator 'EXECUTE STRING' within a function. Does anyone knows how bypass this constraint?

Well, you can't use dynamic sql on a function on SQL Server

Lamak
– Lamak

2013-03-20 13:41:38 +00:00
Commented Mar 20, 2013 at 13:41 — Lamak
– Lamak, Commented Mar 20, 2013 at 13:41

John Woo · Accepted Answer · 2013-03-20 13:45:20Z

4

The error is the concatenation of string which lacks space in between,

SET @sql = 'SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @ColumnNameValue;
               -- ^ SPACE HERE        ^    ^                  ^ and here

if for instance the data type of the column is string, you need to wrap the value with single quotes,

SET @sql = 'SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ''' + @ColumnNameValue + '''';

UPDATE 1

You also need to declare the parameter @ColumnNameValue, eg

CREATE FUNCTION get_column_id
(
    @TableName VARCHAR(30),
    @ColumnName VARCHAR(30),
    @ColumnNameValue VARCHAR(30)
)

edited Mar 20, 2013 at 13:45

answered Mar 20, 2013 at 13:30

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

András Ottó Over a year ago

And don't forget to mention that the @ColumnNameValue is not declared.

gustavodidomenico Over a year ago

All your thoughts are right, but you cannot use side-effecting statements inside a SQL SERVER user defined function. I think that he won't be able to accomplish his task.

Lamak Over a year ago

Why do you think he meant STORED PROCEDURE?, the error is most likely just that he wants to use dynamic sql on a function and is unable to

gustavodidomenico · Accepted Answer · 2013-03-20 13:46:55Z

1

A UDF (user defined function) in Sql Server must be deterministic. Beside your syntax errors you won't be able to accomplish your task.

if you check this article on MSDN:

http://msdn.microsoft.com/en-us/library/ms178091.aspx

You can see the citation below:

Deterministic functions always return the same result any time they are called 
with a specific set of input values and given the same state of the database.    

Nondeterministic functions may return different results each time they are 
called with a specific set of input values even if the database state that 
they access remains the same.

answered Mar 20, 2013 at 13:46

gustavodidomenico

4,6911 gold badge37 silver badges50 bronze badges

Collectives™ on Stack Overflow

use table name as parameter in sql function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related