Execute dynamic sql in query

Question

I would like to have a query like this

SELECT
    param1,
    param2,
    param3,
    getHidden(param1, param3)
FROM xxx

In which my function has to build a dynamic query, execute it and return 0 or 1.

CREATE FUNCTION getHidden (@param1 VARCHAR(50), @param2 INT) RETURNS INT AS
BEGIN
    DECLARE @firstSelect VARCHAR(200);
    DECLARE @query VARCHAR(1000);
    SELECT @firstSelect = restriction FROM xxxx WHERE param1 = @param1 AND param2
    SET @query = 'SELECT (CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END) FROM xxx WHERE ' + @firstSelect;
    -- EXEC @query?
    RETURN 1
END

I'm blocked in the dynamic query execution, is there a workaround to get through this problem?

Thx, Jeremy

EDIT : Details In jasper ireport, I have to make a query (only one) which should be able to return and display a list of questions. In the database, each line has a question and may have a restriction field. This field contains a WHERE constraint (example of content: BEIE_Emp_Nb = 0) In my php program, I use this field for building my query. My final query will be:

SELECT param1, param2 FROM questions WHERE 1 = 1 AND $constraint
which gives me
SELECT param1, param2 FROM questions WHERE 1 = 1 AND BEIE_Emp_Nb = 0

I don't know if this is clear enough. The problem is the fact that I have to query the constraint field for the actual question and THEN build my query and return the result.

What problem are you trying to solve here? I'd be surprised if there weren't a better way if you can explain in more detail. — Adrian Wragg
– Adrian Wragg, Commented Dec 9, 2014 at 9:32
I can't find a solution to execute @query and return the value (1 or 0 — jeremyb
– jeremyb, Commented Dec 9, 2014 at 9:34
@user1474188 That's what you're trying to do here, I can see that. I'm suggesting that you take a step back from the code and explain the actual problem you're trying to solve, which might have a better solution than the one you're implementing. — Adrian Wragg
– Adrian Wragg, Commented Dec 9, 2014 at 9:37

Alberto Solano · Accepted Answer · 2014-12-09 11:12:24Z

1

You cannot call stored procedures, like sp_executesql, in a SQL Server function. This means that you can't define and execute dynamic SQL code inside a function. The reason is that functions are not allowed to have side-effects (like calling themselves and changing data or parameters, as you're doing with your dynamic SQL query, and executing something like INSERT, DELETE or UPDATE).

Only stored procedures can call functions and not the other way around. Check here the limitations and restrictions of functions. You should then create a stored procedure, instead of a function, to solve your problem.

edited Dec 9, 2014 at 11:12

answered Dec 9, 2014 at 10:47

Alberto Solano

8,2553 gold badges40 silver badges62 bronze badges

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

2 Comments

jeremyb Over a year ago

Will I be abble to call it the way I want then? (SELECT param1, EXEC proc @param1 = 'xxx' FROM mytable)

Alberto Solano Over a year ago

@jeremyb Yes, you will be able to execute dynamic SQL code inside a stored procedure.

Veera · Accepted Answer · 2014-12-09 09:48:25Z

0

To execute dynamic query use EXEC with output variable.

DECLARE @nResult INT

EXEC sp_executesql @query, N'@nResultNew INTEGER OUTPUT', @nResult OUTPUT

RETURN @nResult

answered Dec 9, 2014 at 9:48

Veera

3,4922 gold badges16 silver badges27 bronze badges

1 Comment

Alberto Solano Over a year ago

You cannot call stored procedures in a SQL Server function. See here.

Collectives™ on Stack Overflow

Execute dynamic sql in query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related