InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:59:13 GMT20http://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxPlease read any book on basic Software Engineering and study the parts on coupling and cohesion. [i][b]This is bad programming in any language [/b][/i]. You will see it referered to as "Automobiles, Squids and Lady Gaga" procedure or tables in SQL; other languages have their slang, most of which is not printable. It is incoherent, controlled by exrternal flags and will have awful performance. Again, please read any book on basic Software Engineering. Mon, 07 Jan 2013 13:00:15 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspx[quote][b]davoscollective (1/6/2013)[/b][hr][quote][b]dwain.c (1/2/2013)[/b]You should use this instead:[code="sql"]CAST(VARCHAR(19), @EndDate,120)[/code]Or if additional precision is required (milliseconds) use:[code="sql"]CAST(VARCHAR(23), @EndDate,121)[/code][/quote]I think that should have been convert, not cast. I'm sure just a typo. [/quote]Yup. You're correct.Sun, 06 Jan 2013 18:42:55 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspx[quote][b]dwain.c (1/2/2013)[/b]You should use this instead:[code="sql"]CAST(VARCHAR(19), @EndDate,120)[/code]Or if additional precision is required (milliseconds) use:[code="sql"]CAST(VARCHAR(23), @EndDate,121)[/code][/quote]I think that should have been convert, not cast. I'm sure just a typo. 120 also assumes you are using MDY ordering. I am wondering about this part in the original query (and similar parts):'AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''If the CreationTime field is a datetime, then you should compare it to @StartDate parameter as a datetime. Apart from NULLs it should then behave as intended.If the CreationTime field is a varchar, you might be safer to cast CreationTime as a datetime before comparing it to @StartDate, rather than casting @StartDate as a varchar.Comparing them both as varchars may well implicitly convert both to datetime values prior to comparison but you can't guarantee that and it might give unexpected results, particularly if this is a manually entered field in the front end application which could have dodgy values entered.Sun, 06 Jan 2013 18:06:26 GMTdavoscollectivehttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxTake a look at Gail's post. [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]She explains how to deal with this type of thing very cleanly.Thu, 03 Jan 2013 07:25:34 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxSo any chance of building the Dynamic Where Clause Query for the above statements assuming that some parameters could be NULL ..Thanks In AdvanceThu, 03 Jan 2013 05:43:43 GMTRamStevehttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspx[quote][b]RamSteve (1/2/2013)[/b][hr]Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find my Query as Below and I need to make AND Operator to be dynamic instead of hard coding it as i was doing it as belowso the query will look as select * from xyx where cu.unitNumber like '%@SearchValue%' AND cd.CaseDispoID=5 and StartDate >= '12/01/2012' and EndDate<=GetDate()Thanks In Advance...SELECT @Where =CASE @SearchByWHEN 1 THEN 'WHERE CAST(Ce.CustomerEventID AS VARCHAR)'WHEN 2 THEN 'WHERE u.UnitNumber' WHEN 3 THEN 'WHERE l.LocationNumber' WHEN 4 THEN 'WHERE q.SPName' WHEN 5 THEN 'WHERE ce.UserName' WHEN 6 THEN 'WHERE c.City + c.StateName' WHEN 7 THEN 'WHERE ce.UserName' WHEN 8 THEN 'WHERE cu.Name' WHEN 9 THEN 'WHERE cu.CustomerNumber' ELSE'WHERE'ENDIF(@SearchValue IS NOT NULL) BEGIN[b]SET @WhSearchValue ='LIKE ''%'+@SearchValue+'%'' '[/b]ENDELSEBEGINSET @WhSearchValue =' LIKE''%'++'%'' 'ENDIF(@CaseDispoID IS NOT NULL)BEGINSET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''ENDELSEBEGINSET @WhCaseDispoID=''ENDIF(@StartDate IS NOT NULL)BEGIN[b]SET @WhStartDate ='AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''[/b]ENDELSEBEGINSET @WhStartDate=''ENDIF(@EndDate IS NOT NULL)BEGIN[b]SET @WhEndDate ='AND ce.CreationTime<='''+CAST(@EndDate AS VARCHAR)+''''[/b]ENDELSEBEGIN[b]SET @WhEndDate ='AND ce.CreationTime <= GetDate()'[/b]ENDSELECT @SQL= ' '+@Select+' '+@From+' '+@Where+' '+@WhSearchValue+' '+@WhCaseDispoID+' '+@WhStartDate+' '+@WhEndDate+' 'EXEC (@SQL);[/quote]I see a couple of issues here:1. Note where I have made some of your script [b]bold[/b]. In those cases, you are missing a blank before either the AND or the LIKE.2. This CAST of the DATETIME variable may not give you a format the SQL can easily compare (and it is likely to be truncated):[code="sql"]CAST(@EndDate AS VARCHAR)[/code]You should use this instead:[code="sql"]CAST(VARCHAR(19), @EndDate,120)[/code]Or if additional precision is required (milliseconds) use:[code="sql"]CAST(VARCHAR(23), @EndDate,121)[/code]You can also significantly condense all the statements after the assignment to @WhSearchValue, something like this:[code="sql"]SELECT @WhCaseDispoID='' ,@WhStartDate='' ,@WhEndDate=''IF @CaseDispoID IS NOT NULL SET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''ELSE IF @StartDate IS NOT NULL SET @WhStartDate =' AND ce.CreationTime>='''+CAST(VARCHAR(19), @StartDate,120)+''''ELSE IF @EndDate IS NOT NULL SET @WhEndDate =' AND ce.CreationTime<='''+CAST(VARCHAR(19), @EndDate,120)+''''ELSE SET @WhEndDate =' AND ce.CreationTime <GetDate()'[/code] That last assuming of course that only one of the 3 input parameters is NOT NULL.Wed, 02 Jan 2013 22:08:23 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxHI in the above 9 parameters it can have only one based on case statement or it could be NULL for example In @Where parameter i was giving cu.customerNumber Like '%1234%' or ce.CustomerEventID Like '%12334%' ..etc So @Where parameter and @SerchValue are related and Rest of the parameters (@CaseDispoID ,StartDate ,EndDate) can have one value or could be NULL so the final query will be like below if you have all the parametersselect * from abc Where ce.CustomerEventID Like '%12345%' and cd.CaseDispoID=5 AND ce.StartDate >='12/01/2012' and ce.EndDate<= GETDATESo for example in the above Query IF the Parameters @Where and @SearchValue is NULL then the query looks like below select * from abc where cd.CaseDispoID=5 AND ce.StartDate >='12/01/2012' and ce.EndDate<= GETDATESo my whole point is to build Dynamic Where Clause Query using AND Operator based on parameters i get in case statement for each parameter(@Where,@WhSearchValue,@WhCaseDispoID,@WhStartDate,@WhEndDate)Wed, 02 Jan 2013 20:33:15 GMTRamStevehttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspx[quote][b]RamSteve (1/2/2013)[/b][hr]I will not be expecting any other parameters ..Only i do have only 6 parameters in where Clause[/quote]So of the 9 parameters you listed, which 6 are possible? Or are you saying that you can have up to 6 parameters at once?Wed, 02 Jan 2013 19:08:15 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxI will not be expecting any other parameters ..Only i do have only 6 parameters in where ClauseWed, 02 Jan 2013 19:05:07 GMTRamStevehttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxOther than Case 6, do you expect to have the ability to have multiple conditions in the where clause?Wed, 02 Jan 2013 18:40:52 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1402126-149-1.aspxHi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find my Query as Below and I need to make AND Operator to be dynamic instead of hard coding it as i was doing it as belowso the query will look as select * from xyx where cu.unitNumber like '%@SearchValue%' AND cd.CaseDispoID=5 and StartDate >= '12/01/2012' and EndDate<=GetDate()Thanks In Advance...SELECT @Where =CASE @SearchByWHEN 1 THEN 'WHERE CAST(Ce.CustomerEventID AS VARCHAR)'WHEN 2 THEN 'WHERE u.UnitNumber' WHEN 3 THEN 'WHERE l.LocationNumber' WHEN 4 THEN 'WHERE q.SPName' WHEN 5 THEN 'WHERE ce.UserName' WHEN 6 THEN 'WHERE c.City + c.StateName' WHEN 7 THEN 'WHERE ce.UserName' WHEN 8 THEN 'WHERE cu.Name' WHEN 9 THEN 'WHERE cu.CustomerNumber' ELSE'WHERE'ENDIF(@SearchValue IS NOT NULL) BEGINSET @WhSearchValue ='LIKE ''%'+@SearchValue+'%'' 'ENDELSEBEGINSET @WhSearchValue =' LIKE''%'++'%'' 'ENDIF(@CaseDispoID IS NOT NULL)BEGINSET @WhCaseDispoID =' AND cd.CaseDispoID='+CAST(@CaseDispoID AS VARCHAR)+''ENDELSEBEGINSET @WhCaseDispoID=''ENDIF(@StartDate IS NOT NULL)BEGINSET @WhStartDate ='AND ce.CreationTime>='''+CAST(@StartDate AS VARCHAR)+''''ENDELSEBEGINSET @WhStartDate=''ENDIF(@EndDate IS NOT NULL)BEGINSET @WhEndDate ='AND ce.CreationTime<='''+CAST(@EndDate AS VARCHAR)+''''ENDELSEBEGINSET @WhEndDate ='AND ce.CreationTime <= GetDate()'ENDSELECT @SQL= ' '+@Select+' '+@From+' '+@Where+' '+@WhSearchValue+' '+@WhCaseDispoID+' '+@WhStartDate+' '+@WhEndDate+' 'EXEC (@SQL);Wed, 02 Jan 2013 17:42:34 GMTRamSteve