SQL2000-'Where' clause as input parameter??

[bschmitz]

I am trying to create a stored procedure that accepts the 'where' clause as an input parameter. I cannot get SQL to recognize the variable as part of the syntax of the stored procedure. The 'where' clause can be different everytime. As in
"WHERE SlprsnId = '1234' AND DocNmbr = 'A35rt'" or "WHERE SlprsnFNam = 'Frank' AND CustId = '589gyAA' AND SaleAmt > 50".
...
@WhereClause VarChar(100)
AS
SELECT *
FROM ABCDB.dbo.TBLKEY00
@WhereClause

Is this even possible and if so what is the proper syntax??? Thanks for the help.

 

[RobertT687]

Try Dynamic SQL

Create Procedure sp_dynamic
@WhereClause Varchar(200)
AS
DECLARE @SQLString Varchar(500)
SET @SQLString = "SELECT * FROM ABCDB.dbo.TBLKEY00 " + @WhereClause
EXEC sp_executesql @SQLString
go

 

[bperry]

At least one issue to be aware of when runnning dynamic sql (i.e. via the exec command) inside a stored procedure, is that the security context changes from the SP itself, to the user actually running the SP.

Suppose a SP does 'Select * From aTable'. A user running that SP will not need any access rights to aTable itself; he will just need execute permission on the SP.

Now suppose instead the SP does these 2 commands:
set @Sql='Select * from aTable'
exec(@Sql)

Now, the user needs not only execute permission on the SP, BUT ALSO read permission on the table itself.

It is a common (and well-accepted) practice, when setting up security for an application, to give users no permissions at all to the tables, and instead give them execute permission on the SPs. This insures that users can only get at the tables through the SPs provided for them. But if the SPs have exec code in them, then that approach will no longer be possible.

Just something to be aware of.

rgrds, etc
brian perry

 

[bschmitz]

Thanks for the help!!!