How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:

Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.

Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:

You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!