SP & Passing Var - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP & Passing Var

Author

Topic

cfusionit
Starting Member

1 Post

Posted - 2004-02-27 : 13:57:01

i have a query that is used for 2 purposes, (1)to display all results and (2) to display results based on a variable. i am trying to complete the SQL statement below, but im getting debugging errors, and im not sure what the correct syntax is.

if @search_field was passed, then i want to execute the where clause, otherwise do nothing

CREATE PROCEDURE sp_raw_stock
@partner_company char(2),
@search_field char(50)

AS

begin
SELECT *
FROM view

where 0=0
case
when @search_field is not null then and sku like '%@search_field%'
end

thanks, ryan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 13:58:47

You'll need to use COALESCE function:

http://www.sqlteam.com/item.asp?ItemID=2077

The above article shows you how to do a dynamic WHERE clause such as yours.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources