2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1429322
  • 1,266
  • 2
  • 24
  • 38
  • why r u creating the function for this in SQL? Why don't you create the PHP function? – Mr.P Aug 10 '15 at 03:52
  • @Mr.P So that it can be used from other part of software like C++. Of course I can call PHP function from C++ but I think function here in the DB would be more clear and easy in the long run. – user1429322 Aug 10 '15 at 03:55
  • If you intend to pass the entire `where` clause, there's not much point making a function: you should just make a View instead and execute `select * from view where (where clause here)` – Blorgbeard Aug 10 '15 at 03:58
  • @user1429322 is that MySQL? – Mr.P Aug 10 '15 at 04:00
  • Shouldn't you be using dynamic SQL query here? – Pradeep Kumar Aug 10 '15 at 04:43

4 Answers4

2

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.

Community
  • 1
  • 1
Gareth
  • 36
  • 3
  • not necessarily with the SQLi if he will filter the input .. but yes, it is possible and i would also recommend to go off this solution.. +1 – Mr.P Aug 10 '15 at 04:40
  • Good point about the SQL injections and the security concerns. I built the query in PHP and called this function from C++ whenver necessary. – user1429322 Aug 10 '15 at 13:59
1

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'
Community
  • 1
  • 1
Praveen
  • 8,945
  • 4
  • 31
  • 49
  • This is an answer I would accept if not for the concerns about security. When I posted the question, this is the answer I was expecting. – user1429322 Aug 10 '15 at 14:00
0

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

Biswabid
  • 1,378
  • 11
  • 26
-1

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.

Urvesh Purohit
  • 20
  • 4