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
ASSELECT
*, -- 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.
