SQL Server 7 Stored Procedure

[caskey]

Is it possible to pass the WHERE clause to the SELECT statement as a global variable or parameter? If so, what syntax would I use?

For example:

CREATE PROCEDURE [SP_SEARCH] AS
declare @whereclause varchar(255)
set @whereclause = " lastname = 'SMITH' "

SELECT * FROM ATTENDEE
WHERE @whereclause
ORDER BY attendeeid

Note- this gives an error.. but may help describe what I am trying to accomplish...

 

[orientalsupport]

There is another way you can use to do that.
Give a call to sp_executesql in your sp or directly.

 

[caskey]

Yes, that does appear to do what I want... thanks!

CREATE PROCEDURE [SP_SEARCH] AS
declare @selectclause nvarchar(500)
declare @whereclause nvarchar(500)
declare @orderbyclause nvarchar(500)
declare @sqlclause nvarchar(1500)
set @selectclause ="SELECT * FROM ATTENDEE"
set @whereclause = " WHERE LASTNAME = 'Smith'"
set @orderbyclause = " ORDER BY attendeeid"
set @sqlclause = @selectclause+@whereclause+@orderbyclause
EXEC sp_executesql @sqlclause

(Note- my ultimate goal is to make the whereclause dynamic so I can pass it in as a parameter.)

Is there a big performance hit in using sp_executesql?

 

[JNeave]

I also use soft WHERE clauses, but like this.

EXECUTE('
SELECT Fields
FROM Table '
+ @nvcWHEREClause +
' GROUP BY Groups
ORDER BY Fields
')

OR you can write the WHERE clause to a table, and pass in the unique ID of the row with your WHERE Clause (the only way to do this with a sproc and Crystal Reports)

OR You can use psuedo-Optional Parameters.

CREATE PROC Bob
(
   @Param1 AS INTEGER --Bit Parameter
   @Param2 AS STRING  --String Parameter
)
AS
BEGIN
SELECT *
FROM Table
WHERE (FIELD1 = @Param1 OR @Param1 = -1)
      AND
      (FIELD2 = @Param2 OR @Param2 = 'ZZZZZZZZZZ')
END

This way, if Param1 is -1, it is ignored. Following that, if Param2 is 'ZZZZZetc.' then it is ignored.

Jim.