CODEWITHSUNDEEP
sqlsql-server
user1429322
user1429322

Reputation: 1286

How to pass the actual where clause to SQL function?

This is the function that I am trying to create

CREATE FUNCTION fnGetValue
(
     @WhereClause VARCHAR(256)
)
RETURNS TABLE
AS
RETURN
     SELECT A.Name, B.Value
     FROM A
     INNER JOIN B ON A.Akey = B.AKey
     WHERE + @WhereClause +
GO

The parameter @WhereClause is built from my PHP, something like 

A.Akey IN (2,3)

But when I try this I get this error

An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

I want to let everyone know, I know query is wrong because SQL is expecting an expression that can be evaluated to boolean. But my question is how to achieve the thing I am trying for. Please help me.

Upvotes: 2

Views: 2104

Answers (4)

Biswabid
Biswabid

Reputation: 1411

Create Dynamic query to solve this. Folowing is the query that will work 

CREATE FUNCTION fnGetValue( @WhereClause VARCHAR(256))
RETURNS @value TABLE
    (
    Name varchar(50),
    value int
    )
AS
BEGIN
    DECLARE @Query varchar(2000)
    set @Query='INSERT INTO @value SELECT A.Name, B.Value FROM A INNER JOIN B ON A.Akey = B.AKey WHERE' + @WhereClause


execute(@Query) 
RETURN

Upvotes: 0

Urvesh Purohit
Urvesh Purohit

Reputation: 18

you can use excute command to run query in string format. In your case your function body should be

DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT A.Name, B.Value FROM A INNER JOIN B ON A.Akey = B.AKey WHERE ' + @WhereClause;
EXECUTE(@query);

Hope this will solve your problem.

Upvotes: -1

Praveen
Praveen

Reputation: 9335

Here the where clause is dynamically send, hence you have to use dynamic sql;

One limitation here is in a function you cannot use dynamic sql.

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

see ==> Getting an error when executing a dynamic sql within a function (SQL Server)?

A procedure can you used here like

CREATE procedure fnGetValue (
     @WhereClause VARCHAR(256)
)
AS
    declare @sql NVARCHAR(1000) =
        'SELECT A.Name, B.Value
        FROM A
        INNER JOIN B ON A.Akey = B.AKey
        WHERE ' + @WhereClause
EXEC sp_executesql @sql
return
GO

Then,

exec fnGetValue 'any condition'

Upvotes: 1

Gareth
Gareth

Reputation: 36

I think that you would be better off building the SQL function in PHP and calling that. Using your approach it would be very hard to guarantee what went into the query and it's just asking for an SQL injection attack.

Using something similar to (but not necessarily) Laravel would help with building the query and it would also address some of the security concerns associated with dynamically creating queries.

If you are determined to do it this way, you could try what is suggested in this question.

Upvotes: 2

Related Questions