how to use OR operator in storedprosedure

Question

0.00/5 (No votes)

See more:

hai,

my stored procedure is like this

SQL

create proc Procedure
@ProductName varchar(50),
@CompanyName varchar(50)
as
begin
select P.ProductName,P.Category,P.Description,P.Cost,P.Onsale,P.SalePrice,P.CreatedUserId,P.CreatedDateTime,P.UpdatedUserId,P.UpdatedDateTime,C.CompanyName from CompanyProduct P,Company C where P.CompanyId=C.Id and P.ProductName=@ProductName or C.CompanyName=@CompanyName
end

this query was not working, how to write quary for this by using OR operator.
when i execute this query by giving @CompanyNAme then the error was like this "Must declare the scalar variable "@ProductName".

thanks in advance..

Posted 3-May-12 18:39pm

sandeep nagabhairava

Updated 3-May-12 18:52pm

v2

Add a Solution

6 solutions

Solution 1

Try this out

SQL

create proc Procedure1
@ProductName varchar(50),
@CompanyName varchar(50)
as
begin
select P.ProductName,P.Category,P.Description,P.Cost,P.Onsale,P.SalePrice,P.CreatedUserId,P.CreatedDateTime,P.UpdatedUserId,P.UpdatedDateTime,C.CompanyName from CompanyProduct P,Company C where P.CompanyId=C.Id and (P.ProductName=@ProductName or C.CompanyName=@CompanyName)
end

Posted 3-May-12 18:44pm

sachin10d

Updated 3-May-12 21:38pm

v2

Comments

sandeep nagabhairava 4-May-12 0:50am

sorry bro it's not working.. when execute query by giving @company Name then the error was like this "Must declare the scalar variable "@ProductName"."

sachin10d 4-May-12 3:38am

Changes made for the above error.

Solution 3

TRY THIS

SQL

create proc Procedure
@ProductName varchar(50) =NULL,
@CompanyName varchar(50) =NULL
as
begin
select P.ProductName,P.Category,P.Description,P.Cost,P.Onsale,P.SalePrice,P.CreatedUserId,P.CreatedDateTime,P.UpdatedUserId,P.UpdatedDateTime,C.CompanyName from CompanyProduct P,Company C where P.CompanyId=C.Id and (P.ProductName=@ProductName OR @ProductName IS NULL) or (C.CompanyName=@CompanyName OR @CompanyName IS NULL)
end

Posted 3-May-12 20:21pm

Arav Pradeep Gupta

Updated 3-May-12 20:31pm

v4

Comments

sandeep nagabhairava 4-May-12 3:42am

thanks for your time.

Solution 4

Try this

SQL

create proc ProcedureName
@ProductName varchar(50),
@CompanyName varchar(50)
as
begin
select P.ProductName,P.Category,P.Description,P.Cost,P.Onsale,P.SalePrice,P.CreatedUserId,P.CreatedDateTime,P.UpdatedUserId,P.UpdatedDateTime,C.CompanyName from CompanyProduct P,Company C where P.CompanyId=C.Id and (P.ProductName=isnull(@ProductName,'') or C.CompanyName=isnull(@CompanyName,''))
end

Posted 3-May-12 21:37pm

sachin10d

Updated 3-May-12 21:40pm

v2

Comments

sandeep nagabhairava 4-May-12 3:42am

thanks for your time

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-05-03T22:27:00

Theoretical example:

SQL

USE [YourDataBaseName]
GO
/****** Object:  StoredProcedure [dbo].[GetProductsByNameOfCompanyOrProduct]    Script Date: 05/04/2012 09:53:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[GetProductsByNameOfCompanyOrProduct] 
	-- Add the parameters for the stored procedure here
	@CompanyName NVARCHAR(50) = NULL, 
	@ProductName NVARCHAR(50) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(1000)

IF (@CompanyName IS NULL AND @ProductName IS NULL)
BEGIN
	SET @SQL = 'SELECT ''CompanyName IS EMPTY; ProductName is EMPTY!'' AS [Result]'
END;

IF (NOT @CompanyName IS NULL AND @ProductName IS NULL)
BEGIN
	SET @SQL = 'SELECT ''CompanyName = ' + @CompanyName + '; ProductName is EMPTY!'' AS [Result]'
END;

IF (@CompanyName IS NULL AND NOT @ProductName IS NULL)
BEGIN
	SET @SQL = 'SELECT ''CompanyName is EMPTY; ProductName = ' + @ProductName + ''' AS [Result]'
END;

IF (NOT @CompanyName IS NULL AND NOT @ProductName IS NULL)
BEGIN
	SET @SQL = 'SELECT ''CompanyName = ' + @CompanyName + '; ProductName = ' + @ProductName + ''' AS [Result]'
END;

	EXEC (@sql)
END

Usage:

SQL

DECLARE @RC int
DECLARE @CompanyName nvarchar(50)
DECLARE @ProductName nvarchar(50)

-- TODO: Set parameter values here.
SET @CompanyName = NULL
SET @ProductName = NULL
EXECUTE @RC = [A_TEST].[dbo].[GetProductsByNameOfCompanyOrProduct] 
   @CompanyName,@ProductName

SET @CompanyName = 'Company1'
SET @ProductName = NULL
EXECUTE @RC = [A_TEST].[dbo].[GetProductsByNameOfCompanyOrProduct] 
   @CompanyName,@ProductName

SET @CompanyName = NULL
SET @ProductName = 'Product1'
EXECUTE @RC = [A_TEST].[dbo].[GetProductsByNameOfCompanyOrProduct] 
   @CompanyName,@ProductName

SET @CompanyName = 'Company1'
SET @ProductName = 'Product1'
EXECUTE @RC = [A_TEST].[dbo].[GetProductsByNameOfCompanyOrProduct] 
   @CompanyName,@ProductName

Results:

<br />
CompanyName IS EMPTY; ProductName is EMPTY!<br />
CompanyName = Company1; ProductName is EMPTY!<br />
CompanyName is EMPTY; ProductName = Product1<br />
CompanyName = Company1; ProductName = Product1

Conclusion:
You can built queries depend on input parameters.

**tanweer** · Accepted Answer · 2012-05-03T18:48:00

Solution 2

hi,
its seems you are missing C.Id in your select statement as you are using C.Id in the where clause.
try this.

SQL

create proc Procedure
@ProductName varchar(50),
@CompanyName varchar(50)
as
begin
select P.ProductName,P.Category,P.Description,
P.Cost,P.Onsale,P.SalePrice,P.CreatedUserId,P.CreatedDateTime,
P.UpdatedUserId,P.UpdatedDateTime,C.CompanyName, C.Id 
from CompanyProduct p inner join Company c on p.CompanyId= C.Id
where  P.ProductName=@ProductName 
or C.CompanyName=@CompanyName
end

hope this will help you..

Posted 3-May-12 18:48pm

tanweer

Updated 3-May-12 19:09pm

v2

Comments

sandeep nagabhairava 4-May-12 0:54am

when execute query by giving @company Name then the error was like this "Must declare the scalar variable "@ProductName"

tanweer 4-May-12 1:10am

I have edited the above answer with adding join

Maciej Los 4-May-12 4:30am

Almost perfect answer. Why? Please see my solution (theoretical example). My 4!

tanweer 4-May-12 5:19am

you are right..

Maciej Los 4-May-12 5:37am

Hey, your answer is really good, especially by usage join instruction. My theoretical example is not answer, but it shows how to build queries in SP. The OP should use your code to built a correct query.

sandeep nagabhairava 4-May-12 5:46am

thank you tanweer

tanweer 4-May-12 7:37am

welcome

sandeep nagabhairava · Accepted Answer · 2012-05-03T21:43:00

Solution 5

SQL

Create proc [dbo].[ProductDetailsSelect]
@Name varchar(50)
as
begin
select P.ProductName,P.Category,P.Description,P.Cost,P.Onsale,P.SalePrice,P.CreatedUserId,P.CreatedDateTime,P.UpdatedUserId,P.UpdatedDateTime,C.CompanyName from CompanyProduct P,Company C
where P.CompanyId=C.Id and ((P.ProductName like coalesce('%'+@Name+'%','')) or (C.CompanyName like coalesce('%'+@Name+'%','')))
end
GO

Posted 3-May-12 21:43pm

sandeep nagabhairava

Comments

Maciej Los 4-May-12 7:06am

Clever :) but not enough ;(
As i wrote in my comment to the "solution 2" JOIN instruction is more optimal then WHERE P.CompanyId=C.Id. Please, correct it (Use "Improve solution" button) and i'll give a 5.