SP which take where clause as parameter

[spangeman]

Hello there

Can someone show me how to write an SP or UDF which returns the results of an SQL statement after using a Where Clause as a parameter in the SP.

So the SP/UDF would have an input parameter @WHERECLAUSE and return parameter @returntable (col1, col2 etc).
Within the SP/UDF it would execute a pre-written SQL select using the inputted where clause and return the result of the SQL statement.

Regards
Spangeman

 

[SQLDenis]

create procedure prSiteLevels
@intSiteLevelID int
as
set nocount on

select cidtSiteLevelID,LeveName
from cidtSiteLevel
where cidtSiteID =@intSiteLevelID

set nocount off
GO

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[spangeman]

Sorry I didn't make that clear.

The @whereclause cannot simply one a one to one comparison inside the SP.

It needs to be a free text SQL where clause e.g.

'Order > 1 AND Completiondate < ''01/01/2003'''

Regards
Spangeman

 

[Rhys666]

Dynamic Sql and it kind of depends how you're sending the where clause to the sProc. Are you sending the 'WHERE' reserved word in the string or not? The below example implements and executes some dynamic Sql presuming the formed WHERE and AND statements are sent pre-formatted as an input parameter;

create procedure prSiteLevels
@WhereClause varchar(1000)
as
set nocount on
declare @SqlString varchar(8000)
set @SqlString = 'select cidtSiteLevelID,LeveName from cidtSiteLevel ' + @WhereClause
exec (@SqlString)
set nocount off
GO

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese

 

[hmckillop]

Rhys666 has answered your original question, however I would like to point out the following problems.

When you compile a procedure, it does exactly that "compiles". If you dont have a where clause included and use dynamic SQL, obviously recompilation has to occur (and thus negating one of the main uses of stored procedures).
Also, the validation aspect means that you may end up with errors being returned from the stored procedure due to malformed SQL syntax.

Other problems such as providing indexes etc are no longer dependant on your stored procs but also on the code being developed outside of the database.

My suggestion would be to have something like this.

CREATE Procedure usp_MyTestProc
( @p1 int = NULL,
  @p2 varchar(10) = NULL,
  @p3 varchar(20) = NULL-- ....etc representing all potential combinations of parameters with default values of NULL
)
AS
SELECT [Fieldnameist] FROM MyTable
WHERE (@p1 IS NULL -- i.e not passed in
        OR @p1 = Field1)
 AND
(@p2 IS NULL -- i.e not passed in
        OR @p2 = Field2)
AND
(@p3 IS NULL -- i.e not passed in
        OR @p3 = Field3)

This will compare any field that is passed in, thus allowing for any combination, but also can be compiled, has correct syntax and is controlled within DB.

Just my 2cents/


"I'm living so far beyond my income that we may almost be said to be living apart

 

[VBrit]

I'd like to add that using D-SQL in this way, as in Rhys666's solution would definitely be quicker and run faster, but look aesthetically untidy.

This is because although D-SQL is not compiled, the SQL engine will have lots less to do because it knows exactly which fields it will be using. This will use indexes on the fields because SQL will pick the best ones to use when it does a quick execution plan each time.

The other method will run the Where clause on all fields, even if the input parameter is null. Thats the way it works!

You won't notice much difference unless you have large tables with over a couple of hundred thousand rows.

 

[spangeman]

>> You won't notice much difference unless you have large >> tables with over a couple of hundred thousand rows.

We are dealing with up to 60,000 rows!

I think I'll go with Rhys666's solution, I am however greatful for all the extra info that had come out of the is thread.


One final question.
How would I get at the returned results of the SP within another SP?