|
Home > Archive > MS SQL Server > June 2005 > stored procedure with different criteria
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
stored procedure with different criteria
|
|
| Grant H via SQLMonster.com 2005-06-01, 7:23 am |
| When I use transact-SQL, I would use if statement for different possible
criteria and/or how the results should be ordered depending on what the
user wants.
____________________
____________________
____________________
__
This is what it would look like in VB6.
SQL = SQL & “Select field_num, field_desc, field_other”
SQL = SQL & “from table “
SQL = SQL & “Where “
‘ user may want to search by the item number or by the item description
If chkItemNum.value then
SQL = SQL & “ field_num = “ & str & “ “
Elseif chkItemDesc.value then
SQL = SQL & “ field_desc = “ & str & “ “
End if
____________________
____________________
____________________
__
This is what I tried to do in stored procedure.
CREATE PROCEDURE learning @str char, @id int AS
SELECT field_num,
field_desc,
field_other
FROM tbl
where
IF @id = 1
BEGIN
field_num = @str
END
IF @id = 2
BEGIN
field_desc = @str
END
GO
____________________
____________________
____________________
_
--
Message posted via http://www.webservertalk.com
| |
| Alejandro Mesa 2005-06-01, 9:23 am |
| It will be good if we can see the ddl of the table, including indexes. Try:
CREATE PROCEDURE learning @str char, @id int AS
set nocount on
SELECT field_num,
field_desc,
field_other
FROM tbl
where
field_num like coalesce(case when @id = 1 then @str else null end, '%')
and field_desc like coalesce(case when @id = 2 then @str else null end, '%')
return @@error
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Grant H via webservertalk.com" wrote:
> When I use transact-SQL, I would use if statement for different possible
> criteria and/or how the results should be ordered depending on what the
> user wants.
>
> ____________________
____________________
____________________
__
> This is what it would look like in VB6.
> SQL = SQL & “Select field_num, field_desc, field_otherâ€
> SQL = SQL & “from table “
> SQL = SQL & “Where “
>
> ‘ user may want to search by the item number or by the item description
>
> If chkItemNum.value then
> SQL = SQL & “ field_num = “ & str & “ “
> Elseif chkItemDesc.value then
> SQL = SQL & “ field_desc = “ & str & “ “
> End if
> ____________________
____________________
____________________
__
>
>
> This is what I tried to do in stored procedure.
>
> CREATE PROCEDURE learning @str char, @id int AS
>
> SELECT field_num,
> field_desc,
> field_other
> FROM tbl
> where
>
> IF @id = 1
> BEGIN
> field_num = @str
> END
>
>
> IF @id = 2
> BEGIN
> field_desc = @str
> END
>
>
> GO
> ____________________
____________________
____________________
_
>
> --
> Message posted via http://www.webservertalk.com
>
| |
| Francesco Anti 2005-06-01, 9:23 am |
| Look at sp_executesql in BOL. Be careful in usig it because you statement
will be recompiled at every call and SQL query optimizer will not reuse the
execution plan perviuosly generated.
Francesco Anti
"Grant H via webservertalk.com" <forum@nospam.webservertalk.com> wrote in message
news:6f08e9e3254748e
7a5730eeee95aad2e@we
bservertalk.com...
> When I use transact-SQL, I would use if statement for different possible
> criteria and/or how the results should be ordered depending on what the
> user wants.
>
> ____________________
____________________
____________________
__
> This is what it would look like in VB6.
> SQL = SQL & "Select field_num, field_desc, field_other"
> SQL = SQL & "from table "
> SQL = SQL & "Where "
>
> ' user may want to search by the item number or by the item description
>
> If chkItemNum.value then
> SQL = SQL & " field_num = " & str & " "
> Elseif chkItemDesc.value then
> SQL = SQL & " field_desc = " & str & " "
> End if
> ____________________
____________________
____________________
__
>
>
> This is what I tried to do in stored procedure.
>
> CREATE PROCEDURE learning @str char, @id int AS
>
> SELECT field_num,
> field_desc,
> field_other
> FROM tbl
> where
>
> IF @id = 1
> BEGIN
> field_num = @str
> END
>
>
> IF @id = 2
> BEGIN
> field_desc = @str
> END
>
>
> GO
> ____________________
____________________
____________________
_
>
> --
> Message posted via http://www.webservertalk.com
|
|
|
|
|