I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select
i.e
if...@where = @where + ' llll '
if...@where = @where + ' llll '
select @statement = @statement + @where
exec(@statement)
I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.
Is it possible to use either statement inside a where clause??
This is a CASE statement that I am using in the WHERE clause of my query. SQL Syntax checker keeps returning an error (Incorrect syntax near '='). Can anyone help me figure out what I need to do to get this to work?
Case When (@Weekday=-1 and @Saturday=0 and @Sunday=0) Then (L1.[Weekday]=-1 AND L2.[Weekday]=-1 AND L3.[Weekday]=-1 AND L4.[Weekday]=-1) When (@Weekday=0 and @Saturday=-1 and @Sunday=0) Then (L1.Saturday=-1 AND L2.Saturday=-1 AND L3.Saturday=-1 AND L4.Saturday=-1) When (@Weekday=0 and @Saturday=0 and @Sunday=-1) Then (L1.Sunday=-1 AND L2.Sunday=-1 AND L3.Sunday=-1 AND L4.Sunday=-1) When (@Weekday=-1 and @Saturday=-1 and @Sunday=-0) Then (L1.Sunday=0 AND L2.Sunday=0 AND L3.Sunday=0 AND L4.Sunday=0) When (@Weekday=-1 and @Saturday=0 and @Sunday=-1) Then (L1.Saturday=0 AND L2.Saturday=0 AND L3.Saturday=0 AND L4.Saturday=0) When (@Weekday=0 and @Saturday=-1 and @Sunday=-1) Then (L1.[Weekday]=0 AND L2.[Weekday]=0 AND L3.[Weekday]=0 AND L4.[Weekday]=0) Else ((L1.[Weekday]=-1 AND L2.[Weekday]=-1 AND L3.[Weekday]=-1 AND L4.[Weekday]=-1) OR (L1.Saturday=-1 AND L2.Saturday=-1 AND L3.Saturday=-1 AND L4.Saturday=-1) OR (L1.Sunday=-1 AND L2.Sunday=-1 AND L3.Sunday=-1 AND L4.Sunday=-1)) End
I am stumped trying to use case/if type conditions in having clause. Not sure if it's possible or my syntax or both.
Trying to do something like this:
CASE WHEN (dbo.t_COT_Summary.TCD >= dbo.ReportDate(CONVERT(nvarchar(30), GETDATE(), 101))) THEN
HAVING dbo.t_COT_AP_Exclude.Primary_ID IS NOT NULL
ELSE
HAVING dbo.t_COT_AP_Exclude.Primary_ID IS NULL
END
dbo.t_COT_Summary.TCD = Target Completion Date dbo.ReportDate = Previous Business Day Function dbo.t_COT_AP_Exclude.Primary_ID = A left joined ID value I wish to exclude or include in the main query's having.
I hope this makes sense... any suggestions on a better way to do this would be greatly appreciated!
A deveoper just asked me if there is a way to use a case in a where clause. Is this feasible or will we have to do some dynamic sql
where (cr.cb_routine = 1 or cr.cb_urgent = 1 or cr.cb_emergency_room = 1 or cr.cb_on_site_clinic = 1 or cr.cb_retro_request = 1 or cr.cb_initial = 1 or cr.cb_followup = 1 or cr.cb_in_person = 1 or cr.cb_telemed = 1 or cr.df_within is not null or cr.df_provider is not null or cr.df_proc_test_spec is not null or cr.df_provider_area is not null) and p.privacy_level = 10 and pe.Location_ID = @Location and case when @status = 'Pended' then cr.cb_supp_info_need1 = 1 case when @status = 'Criteria for service not met' then cr.cb_criteria_not_1 = 1 case when @status = 'Other' then cr.cb_other_1 = 1 case when @status = 'All' then
WHERE weekdayname(weekday(sfa_admin_sbaccount.add_time)) = case when "Monday" then (((SFA_ADMIN_SBACCOUNT.ADD_TIME)>=Date()-3 And (SFA_ADMIN_SBACCOUNT.ADD_TIME)<Date())) else (((SFA_ADMIN_SBACCOUNT.ADD_TIME)>=Date()-1 And (SFA_ADMIN_SBACCOUNT.ADD_TIME)<Date()))); end
This keeps telling me I'm missing an operator. What I want the query to do is to evaluate the current day's date, then use that to determine whether it needs to set a WHERE clause that goes back 3 days (if it's a Monday) or one day (if it's not a Monday)
need your help... i am now trying to create a report using SQL reporting services... when declare all the @parameters needed in where clause, i have come across a problem. where one of the parameters that prompting user to key in...i need to put in some condition.
select..................(blah blah)...... ......(SELECT CASE WHEN ((SELECT COUNT(*) FROM tbl_OutpatientReg OPT WHERE OPT.PatientID = tbl_Patient.PatientID)) = 1 THEN 0 ELSE 1 END) AS PTType ............................(blah blah).......
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @FromDate AND @ToDate) OR (@FromDate = ' ') OR (@ToDate = ' ') AND (@PatientType = CASE WHEN (SELECT COUNT(*) FROM tbl_OutpatientReg OPT WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0 ELSE 1 END)
my situition is something like above, i know i have done something wrong in teh WHERE clause for the @PatientType.... can i ask how to restrict the parameters entered by user, let's say if user enter parameter "0", then the visitcount is 1, if enter "1" then the visit count refers to more than 1...
Select * from dtree WHERE Subtype=848 AND (MODIFYDATE between to_date(%2,'dd/mon/yyyy HH24:MI:SS') AND to_date(%3,'dd/mon/yyyy HH24:MI:SS') )
the numnbers %1, %2, %3 are variables and they get the values from another application. It works so far, but there case I can't handle.If the date values (%2,%3) are empty.I want that my query finds everything without considering the Date condition..I tried it with a CASE condition:
Select * from dtree WHERE Subtype=848 AND AND (MODIFYDATE between to_date(CASE LEN(%2)=0 THEN '03/Feb/2015 00:00:00' END,'dd/mon/yyyy HH24:MI:SS') AND to_date(CASE LEN(%3)=0 THEN '05/Feb/2015 00:00:00' END,'dd/mon/yyyy HH24:MI:SS') )
but it doesn't work. How I can handle empty values?
Can anybody see why this is failing at line 19 incorrect syntax near = ?
declare @Date datetime declare @type int declare @isnew int declare @isreturn int declare @isold int SET @Date = '2008-03-04' SET @type = 1 SET @isnew=1
SELECT [date], SUM(amount) as s_amount FROM values WHERE convert(char(10),[date],23) = @Date AND status > 0 AND
CASE WHEN @isnew=1 THEN (loan.isnew=1) WHEN @isreturn=1 THEN ((loan.isreturn=1) and loan.isold=0) WHEN @isold=1 THEN loan.isold=1 END AND type = @type GROUP BY [date]
I am attempting to write a stored procedure that will accept a column name in the form of an nvarchar parameter along with a corresponding value to use to filter the returned results. Obviously I can write a CASE statement with the query repeated for each case with the applicable WHERE clause, but after some research I did find that according to some sites, using the CASE statement in the WHERE clause is perfectly legal. However, I have tried the following code, which is basically a cut & paste version of what I found described, but SQL Server keeps generating errors at the first WHEN clause. I would appreciate anyone's guidance getting this right.
SELECT * FROM tblTest WHERE CASE @FilterKey WHEN 'Description' THEN [Description]=@FilterValue WHEN 'UpdateTime' THEN [UpdateTime]=@FilterValue END
In this case, the table [tblTest] has two columns: [Description] and [UpdateTime] and the parameters @FilterKey and @FilterValue would be defined in the sproc definition - or inline with DECLARE/SET statements in Query Analyzer.
Keep in mind that the goal is to NOT have a separate parameter for each property so the (@p is null OR p=@p) method is not appropriate.
Oh, I have also tried to bring the CASE condition inline with the WHEN clause, for instance, WHEN @FilterKey='Description' THEN..., with no change in the results.
I need a SQL statement that selects a specific year (@yr type int) in the "createddate" column...if this @yr is equal to 0 then I want to select ALL columns regardless of the year...This is what I have so far, but it doesnt work...SELECT * FROM tblUsersWHERE year(CreatedDate)=CASEWHEN @yr<>'0' THEN @yrELSE NOT NULLEND
Hi!I want to make search engine and I have problem with query for this search. User can write username to search or text to search or both. So at first I made query for each event individually: ALTER PROCEDURE [dbo].[Show_Search_Topics] @username varchar(200), @search_text varchar(200), @days int AS DECLARE @date DATETIME SET @date = DATEADD(day,@days,GETDATE()) IF @username IS NOT NULL AND @search_text IS NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (dbo.aspnet_Users.UserName = @username) AND (forum_topics_1.post_current_date >= @date) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END ELSE IF @username IS NULL AND @search_text IS NOT NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END ELSE IF @username IS NOT NULL AND @search_text IS NOT NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') AND (dbo.aspnet_Users.UserName = @username) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END RETURN This 3 queries are different only by Having clause. So I want to put If/Case in Having clause, but I have problem. Can anyone help me?Also I want to make paging from SQL level, so if anyone will be so helpful and make working this query with this: ALTER PROCEDURE [dbo].[Show_Search_Topics] @username varchar(200), @search_text varchar(200), @days int, @page int, @page_size int AS WITH Results As ( //QUERY)
) SELECT * FROM Results WHERE RowNumber BETWEEN (@page_size * @page + 1) AND (@page_size * (@page + 1)) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC RETURN I will be grateful :-)
Hi, I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following: SELECT ... FROM ... WHERE (condition) AND (condition) AND (condition) AND ( CASE Table.Category WHEN 'Drinks' THEN Table1.Field1 = 1 -- Problem line ELSE Table1.Field1 = 1 AND Table1.Field2 = 1 -- Problem line END ) In the above case, my WHERE Clause is dependent on one of the fields in a table. If it has a certain value then only Table1.Field1 is used otherwise Table1.Field1 & Field2 come into action. I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working... Thanks in advance...
For Example:SELECT Column1, Column2 FROM Table WHERE Column1 = ( SELECT CASE @Test4Nulls WHEN 1 THEN NULL ELSE Column1 END) Basically, I want to test for Nulls in a column if my variable @Test4Nulls is True, however, I never get any results back. I believe it is because I am Criteria = NULL instead of Criteria IS NULL, however, I cannot use IS in a case statement How do I test for nulls in a case statement
can i use case statement in where clause. The scenario is as follow
declare @param int select * from table1 where column1 = 'asdf' column2= @param
In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value.
can i use case in where clause so that if @param is '' then i will not include in the where clause
Can I do the following? Keep getting an error stopping at the first < of the where clause.
declare @mon as int, @yr as int, @myDate as varChar(20) set @yr=2006 set @mon=1 set @mydate='01/31/2006 23:59:59'
select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v, (select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1 where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and (case when @mon=1 then dateDiff(month,dateService,@myDate)< 216 when @mon=2 then dateDiff(month,dateService,@myDate)<244 when @mon=3 then dateDiff(month,dateService,@myDate)<275 when @mon=4 then dateDiff(month,dateService,@myDate)<305 when @mon=5 then dateDiff(month,dateService,@myDate)<336 when @mon=6 then dateDiff(month,dateService,@myDate)<366 when @mon=7 then dateDiff(month,dateService,@myDate)<32 when @mon=8 then dateDiff(month,dateService,@myDate)<63 when @mon=9 then dateDiff(month,dateService,@myDate)<93 when @mon=10 then dateDiff(month,dateService,@myDate)<124 when @mon=11 then dateDiff(month,dateService,@myDate)<154 when @mon=12 then dateDiff(month,dateService,@myDate)<185 end) group by v.chartID, rs1.procedureKey
i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:
(simple ex)
Select order_no from table1 inner join table2 on table1.order_no = table2.order_no group by order_no having table1.Qty<> table2.Qty
BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:
CASE WHEN table1.Qty<> table3.Qty THEN table3.Qty<> table2.Qty ELSE table1.Qty<> table2.Qty END
I need to have a CASE statement inside a WHERE clause. Is this possible? Here is my WEHRE clause. Any suggestions are appreciated:
WHERE --r.pBOM_ID=d.fEngr_BOM_ID and r.fItem_ID=a.pEngr_Item_ID and r.level<=@v_level+1 and case when @v_showrootlevel=1 then r.level>1 else r.level>1 end order by r.pID
I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error:
Line 15: Incorrect syntax near '='.
this is my query that lies on a PROC:
SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a INNER JOIN Engr_BOM_Control b ON a.pEngr_Item_ID=b.fEngr_Item_ID WHERE b.Bill_Type=@v_Bill_Type and a.Item_No=@v_Item_No and case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end
If you could help me with my syntax i would really appreciateit. I'm trying to change the where clause on the fly, but it'sgiving me a syntax error.Hopefully from what I have below you can tell what i'm afterthe first part should evaluate a boolean expression, then if true,search using one field, otherwisesearch using a different fieldWHERECase WHEN @myCompany = 933THEN tblClient.companycode = 933 --problem lineELSEtblCase.clientcode = @myClient --problem lineENDAnd tblCase.status = 'Active'thank you!!
Is there an equivalant construction to the CASE WHEN statement thatcan be used in the WHERE clause?For example, this works:SELECTFirstName = CASE WHEN c.FirstName = 'Bob' THEN'Robert'ELSEc.FirstNameEND,c.LastNameFROMContacts cWHERE(c.FirstName = 'Bob')OR(c.FirstName = 'Robert')But is there a way to build somehting resembling this (without usingExec-SQL)@FirstName nvarchar(35)SELECTc.FirstNamec.LastNameFROMContacts cWHERECASE WHEN @FirstName = 'Bob' THENc.BlueHair = 1ELSE CASE WHEN @FirstName = 'Frank' THENc.PastaEater = 1ELSEc.HatSize 5END END
Can anyone tell me if it's possible to use a Case statement in a Whereclause, and if so, the proper syntax?J.R.Largo SQL ToolsThe Finest Collection of SQL Tools Availablehttp://www.largosqltools.com
I have a query which is something like this, but it returns an error:
SELECT * FROM tablex
WHERE
CASE WHEN 'sexec' IS NOT NULL THEN
dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE
dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END
GROUP BY dm_marque
In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else. I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one.
I have my where clause as follows, but it's not working:
WHERE (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2) = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)
I give the user two paramters.
The first parameter is to populate the first two characters of a string. There is a "." then another two characters. What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters. If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5.
Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot. CREATE PROCEDURE dbo.GetUsers (@UserId uniqueidentifier,@Gender Int,@PageNumber Int,@UsersPerPage Int,@HowManyUsers INT OUTPUT)ASSET NOCOUNT ONDECLARE @Users TABLE(RowNumber Int,UserId uniqueidentifier,LastName Varchar(50),FirstName Varchar(50))
INSERT INTO @Users SELECT ROW_NUMBER() OVER (ORDER BY P.UserId), P.UserId, P.LastName, P.FirstNameFROM dbo.Profile PWHERE P.UserId <> @UserIdCASE WHEN @Gender =1 THEN 'AND P.GenderId = 1' WHEN @Gender = 0 THEN 'AND P.GenderId = 0'END
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
Hi, I need help on this store procedure. I tried to put a case in the where clause. The Gender could be 0, 1 or 2. I want to have the case only when gender is 0 or 1, but not 2. But it gives me error. And if I try to build a dynamic on where query, the I get other error on the temporary table @Users table saying it is not declare. Can anyone help me out? Thanks a lot.
CREATE PROCEDURE dbo.GetUsers (@UserId uniqueidentifier, @Gender Int, @PageNumber Int, @UsersPerPage Int, @HowManyUsers INT OUTPUT ) AS SET NOCOUNT ON DECLARE @Users TABLE (RowNumber Int, UserId uniqueidentifier, LastName Varchar(50), FirstName Varchar(50))
INSERT INTO @Users SELECT ROW_NUMBER() OVER (ORDER BY P.UserId), P.UserId, P.LastName, P.FirstName FROM dbo.Profile P WHERE P.UserId <> @UserId CASE WHEN @Gender =1 THEN 'AND P.GenderId = 1' WHEN @Gender = 0 THEN 'AND P.GenderId = 0' END
SELECT @HowManyUsers = COUNT(UserId) FROM @Users SELECT UserId, LastName, FirstName FROM @Users WHERE RowNumber > (@PageNumber - 1)*@UsersPerPage AND RowNumber <= @PageNumber * @UsersPerPage
I have a scenario where the End User is going to select two parameters. @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.
If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.
@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).
@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:
WHERE
gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate) AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END AND gl.Location = @Location
