Creating stored procedure

I have an inline sql query which i want to convert to a stored procedure.
The query is simplified as follows:

string sql = "SELECT * FROM property_property WHERE Location = " + location;
if(state != null) sql += " AND State = " + state;
if(cost !=null) sql += " AND Cost = " + cost;

The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc

I know some of the basics and managed to convert every other query except this.

Any help would be appreciatedcheck out BOL for sp_executesql.

hth|||Here is a way - probably not the most elegant but:

CREATE PROCEDURE MyStoredProcedure
@.Location NVARCHAR(50),
@.State NVARCHAR(50) = NULL,
@.Cost NVARCHAR(50) = NULL

AS

DECLARE @.Query NVARCHAR(2000)
SET @.Query = 'SELECT * FROM property_property WHERE Location = ''' + @.Location + ''''

IF NOT @.State IS NULL
BEGIN
SET @.Query = @.Query + ' AND State = ''' + @.State + ''''
END

IF NOT @.Cost IS NULL
BEGIN
SET @.Query = @.Query + ' AND City = ''' + @.City + ''''
END

EXEC sp_executesql @.Query

GO

For debugging purposes, you might replace the "EXEC sp_executesql @.Query" with "PRINT @.Query" just to make sure it is building the correct SQL statement.

Hope that helps,
Ian|||

CREATE PROCEDURE dbo.ReadMoreBooks
 @.State varchar(50), -- change datatypes to match sql's
 @.Cost varchar(50), -- change datatypes to match sql's
 @.Location varchar(50) -- change datatypes to match sql's
AS
SELECT
 *, -- i hate using *. List all the fields
 StateSelect = (
 CASE
 WHEN @.State IS NOT NULL THEN
 @.State
 WHEN @.State IS NULL THEN
 Null
 END
 ),
 CostSelect = (
 CASE
 WHEN @.Cost IS NOT NULL THEN
 @.Cost
 WHEN @.Cost IS NULL THEN
 Null
 END
 )
FROM
 property_property -- weird table name, but ok.
WHERE
 Location = @.Location
AND StateSelect = @.State
AND CostSelect = @.Cost

That's one way. Didn't try, but maybe.