Control Statement with Where Clause

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 2008 Forums

Transact-SQL (2008)

Control Statement with Where Clause

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2009-06-24 : 17:27:52

Good day,

I'd like to execute different "where" criteria based on a bit param in my SP.

The following is what I have , but it doesn't work. Any help would be appreciated.


CREATE PROCEDURE [dbo].[getUsersByCriteria]
	-- Add the parameters for the stored procedure here
	@isActive bit,
	@pageIndex int,
	@numRows int,
	@ID int,
	@orderby int,
	@hasphoto bit
AS
BEGIN
	
Declare @startRowIndex INT; 
set @startRowIndex = (@PageIndex * @NumRows) + 1;

With pagedResults as (
SELECT ROW_NUMBER() OVER (ORDER BY cad.userID ASC) as Row, cad.userID
FROM Details cad JOIN Users cu ON cad.userID = cu.userID
LEFT  JOIN
(SELECT DISTINCT userID,photoid FROM Photos WHERE displayStatus = 1) 	
p ON p.userID = cad.userID
WHERE ID = @ID AND isActive = @isActive
		
/*This part im not sure of */
IF (@hasphoto = 1)
THEN AND EXISTS (select * from Photos where userID = cad.userID) 
ELSE
AND NOT EXISTS (select * from Photos where userID =  cad.userID)
END
)
	
END

Select userID
	FROM pagedResults
	WHERE Row between 
	@startRowIndex and @StartRowIndex+@NumRows-1

Hope this helps. Thanks.

rc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 18:26:42

[code]CREATE PROCEDURE dbo.getUsersByCriteria
(
@isActive bit,
@pageIndex int,
@numRows int,
@ID int,
@orderby int,
@hasphoto bit
)
AS

SET NOCOUNT ON

Declare @startRowIndex INT

set @startRowIndex = (@PageIndex * @NumRows) + 1

With pagedResults
as (
SELECT ROW_NUMBER() OVER (ORDER BY cad.userID ASC) as Row,
cad.userID
FROM Details cad
INNER JOIN Users cu ON cad.userID = cu.userID
LEFT JOIN (
SELECT DISTINCT
userID,
photoid
FROM Photos
WHERE displayStatus = 1
) as p ON p.userID = cad.userID
WHERE @hasphoto = 1 AND p.UserID IS NOT NULL
OR @hasphoto = 0 AND p.UserID IS NULL
WHERE ID = @ID
AND isActive = @isActive
)

Select userID
FROM pagedResults
WHERE Row between @startRowIndex and @StartRowIndex+@NumRows-1[/code]

E 12°55'05.63"
N 56°04'39.26"

robc
Yak Posting Veteran

60 Posts

Posted - 2009-06-29 : 15:23:35

Thank you Peso.

Although the double "WHERE" gave me syntax errors so I changed the second one to an "AND"

Now with this same query, I wanted to add something else. I'm looking to bring back membershipTypes of any,none or a specified ID

New Param
@membershiptype int = null

New code to Add (doesn't work)

LEFT JOIN orders o ON ca.userID = o.userID
WHERE @membershipType = 0 AND o.membershipType IS NULL
	OR @membershipType > 0 AND @membershiptype = isNull(@membershiptype,o.membershipType)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 13:12:43

[code]
CREATE PROCEDURE dbo.getUsersByCriteria
(
@isActive bit,
@pageIndex int,
@numRows int,
@ID int,
@orderby int,
@hasphoto bit,
@membershiptype int = null
)
AS

SET NOCOUNT ON

Declare @startRowIndex INT

set @startRowIndex = (@PageIndex * @NumRows) + 1

With pagedResults
as (
SELECT ROW_NUMBER() OVER (ORDER BY cad.userID ASC) as Row,
cad.userID
FROM Details cad
INNER JOIN Users cu ON cad.userID = cu.userID
LEFT JOIN (
SELECT DISTINCT
userID,
photoid
FROM Photos
WHERE displayStatus = 1
) as p ON p.userID = cad.userID
LEFT JOIN orders o ON ca.userID = o.userID
AND (ISNULL(@membershipType,0) = 0 OR
o.membershipType =@membershiptype)
WHERE (@hasphoto = 1 AND p.UserID IS NOT NULL
OR @hasphoto = 0 AND p.UserID IS NULL)
AND ID = @ID
AND isActive = @isActive
)

Select userID
FROM pagedResults
WHERE Row between @startRowIndex and @StartRowIndex+@NumRows-1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources