Passing a query as a stored procedure parameter

Hi all

In MS SQL Server you can create a stored procedure which will execute any SQL decided at run time like this:

CREATE PROCEDURE SP_QUERY(@p1 varchar(1000))
AS
BEGIN
DECLARE @SQL varchar(1000)
SET @SQL = @p1
EXEC(@SQL)
END

So for example you may execute it with:
EXEC PROCEDURE SP_QUERY @p1 = 'SELECT * FROM TABLE'
A trivial example but I hope you get the idea.

Why would I want to do this?
We may have a situation where we can only query the database using stored procedures, no queries.

I've been trying to do this with Firebird 2.0 for a while with no success. I suspect that it isn't possible because of the way RETURNS must be specified.

Any help would be very much appreciated.

I don't think it will work for the reasons you wrote.
BTW, while it's common to allow access to stored procs and not to query the database directly using stored procs like the one you posted destroys the security reasons behind this ...

Many thanks pabloj. I suspected as much .

Well, you could return the whole query result packed in a varchar(3000) field or something like that, but I doubt it would be the solution you are looking for.

Haven't been around for a while or would have responded sooner. I think we need to come up with another solution but thanks for your input.