June 17, 2004 at 8:48 am
Hi all,
I have a web search form that lets a user set several search criteria such. I used to build my SQL statement in ASP when the form was submitted and run the SQL statement from within the ASP page.
I now want to build the query in a stored procedure.
In ASP I could do the following:
strSQL = strSQL & "SELECT * FROM t_table1 "
If strPhrase1 <> "" Then strWHERE = "WHERE Title = '" & strPhrase1 & "' "
If strPhrase2 <> "" Then strWhere = strWhere & " AND Title = '" & strPhrase2 & "' "
If dteExpires <> "" Then strWhere = " AND dteExpires > dteExpires"
strSQL = strSQL & strWhere
How can I do the equivalent in a stored procedure?
The SP input parameters (search criteria) may be NULL or they may hold a value.
Can I build a string variable that holds the WHERE clause and then substitute that variable into a SELECT statement as I can in ASP?
Otherwise I would have endless
IF @Param1 = NULL Then
type statements (with 5 search criteria - 5 x 5 = 25 potential different search criteria this would be a very long winded and messy SP)
thanks,
Nick
June 17, 2004 at 11:18 am
The way to do this is by using the sp_executesql stored procedure. It has one main input variable, and that is the actual statement you want to run, all you have to do is dynamically build the rest, like so:
SET QUOTED_IDENTIFIER OFF
DECLARE @SQLStatement VARCHAR(300)
DECLARE @Select VARCHAR(100)
DECLARE @From VARCHAR(100)
DECLARE @Where VARCHAR(100)
SET @Select = "SELECT COL1, COL2, COL3 "
SET @From = "FROM Table1 "
IF (condition) = 1
BEGIN
SET @Where = "WHERE COL1 = 'ABC' "
END
ELSE
SET @Where = "WHERE COL1 = '123' "
END
SET @SQLStatement = @Select + @From + @Where
EXEC sp_executesql @SQLStatement
NOTE: You must set quoted identifiers off, so that you can use " to mark your text, this allows ' to be used in the WHERE clause.
Does this make sense?
June 18, 2004 at 2:08 am
Hi Carl,
thanks for the answer. I have it working but it would only work if I changed the variable data types to nvarchar rather than varchar.
If using varchar then I got the following errror message:
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 19
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
thanks,
Nick
June 18, 2004 at 4:32 am
There's another way to handle this problem:
Create Procedure myProc
@cond1 varchar(1) )= null
@cond2 int = null
AS
SELECT * FROM MyTable
WHERE
(Column1 = @cond1 OR @cond2 is null)
AND
(Column2 = @cond2 OR @cond2 is null)
.....
then in your page you pass only the values you want to chek, and null for those you want to ignore...
HTH
Luigi
June 18, 2004 at 7:33 pm
Luigi, on a query that'll need to handle big resultsets, the dynamic sql will do one comparison, but yours will do four...
June 19, 2004 at 4:41 am
Hi Mark, that's true, but all the nulled values will be evaluated once, during the parse of the query, so I think the execution time will not be affected.
Luigi
June 21, 2004 at 8:33 am
http://www.sommarskog.se/dyn-search.html. Erland discusses there exactly what you're after.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply