Stored Procedures

[vsilver]

CREATE PROCEDURE sp_SoilRetrieve
@whereclause varchar (150)

AS

IF (@whereclause IS NOT NULL)

SELECT*
FROM Soil
WHERE @whereclause

ELSE

SELECT*
FROM Soil

RETURN 0

i get an error saying wrong syntax near ELSE. anybody help. i cant see why

 

[mwolf00]

CREATE PROCEDURE sp_SoilRetrieve (
@whereclause varchar (150) = null
)
AS

IF (@whereclause IS NOT NULL)
EXEC ('SELECT *
FROM tblrequest
WHERE ' + @whereclause)

ELSE
SELECT *
FROM tblrequest

RETURN 0
go

exec sp_soilretrieve Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[SQLSister]

Note that when you use dynamic SQL you cannot manage user security at the stored procedure level. You must grant rights to the base tables or views.

Also it is better to return only the columns you need instead of using *. The less data returned, the less stress on the system, the faster everything runs, the longer until you need more powerful equipment. Using * is sloppy programming technique.