InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 04:43:13 GMT20http://www.sqlservercentral.com/Forums/Topic527163-338-1.aspxthanks GrantJohannWed, 02 Jul 2008 07:37:34 GMTJohann Montforthttp://www.sqlservercentral.com/Forums/Topic527163-338-1.aspxGreat. If you did need to pursue the CASE statements, you can't use "= LIKE." So you'd have to change[code]AND lc.locstring = [/code]to[code]AND lc.locstring LIKE [/code]Wed, 02 Jul 2008 07:25:20 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic527163-338-1.aspxok solved itsomeone suggested this on another forum:-AND @ulang <> 'al' AND lang.langCode = @ulangand ((@uid=0 AND lc.string LIKE '%' + @str + '%') or (@uid=1 AND lc.string = @str))Wed, 02 Jul 2008 07:12:09 GMTJohann Montforthttp://www.sqlservercentral.com/Forums/Topic527163-338-1.aspxThanks GrantI tried thisWHERE (p.fk_projectId = @pid)AND lang.langcode = CASE WHEN @ulang <> 'al' THEN @ulang ENDAND lc.locstring = CASE WHEN @uid = 0 THEN LIKE '%' + @str + '%' WHEN @uid = 1 THEN = @str ENDbut its throwing an error near the LIKEHow can I fix that?Thanks againWed, 02 Jul 2008 06:54:00 GMTJohann Montforthttp://www.sqlservercentral.com/Forums/Topic527163-338-1.aspx[quote][b]Johann Montfort (7/2/2008)[/b][hr]Dear AllHow can I write a stored procedure based on condition. At the moment, my code is not working. Here is my exampleALTER PROCEDURE [dbo].[FindStrings]( @uid bit, -- unique string @str nvarchar(1000), --string to be searched @ulang nvarchar(20), -- language id @pid int -- projectid)ASDECLARE @strSQL varchar(4000)SET NOCOUNT ONSELECT DISTINCT pr.fk_resourceId, p.pageId, p.pageTitle, lang.langCode AS LanguageFROM PageResources AS pr INNER JOIN Pages AS p ON pr.fk_pageId = p.pageId INNER JOIN LString AS lc ON pr.fk_resourceId = lc.fk_resourceId INNER JOIN Languages AS lang ON lc.fk_langID = lang.langIdWHERE (p.fk_projectId = @pid)IF @ulang <> 'al' AND lang.langCode = @ulangIF @uid=0 AND lc. string LIKE '%' + @str + '%'IF @uid=1 AND lc. string = @strORDER BY pr.fk_resourceIdThanks for your help and timeJohann[/quote]If I understand what you're asking for, you need a CASE statement in the where clause. You can't use IF conditions there. Something like this:[code]SELECT DISTINCT pr.fk_resourceId, p.pageId, p.pageTitle, lang.langCode AS LanguageFROM PageResources AS prINNER JOIN Pages AS pON pr.fk_pageId = p.pageIdINNER JOIN LString AS lcON pr.fk_resourceId = lc.fk_resourceIdINNER JOIN Languages AS langON lc.fk_langID = lang.langIdWHERE ( p.fk_projectId = @pid ) AND lang.langcode = CASE WHEN @ulang = 'al' THEN @ulang ELSE lang.langCode END...[/code]Wed, 02 Jul 2008 06:32:12 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic527163-338-1.aspxDear AllHow can I write a stored procedure based on condition. At the moment, my code is not working. Here is my exampleALTER PROCEDURE [dbo].[FindStrings]( @uid bit, -- unique string @str nvarchar(1000), --string to be searched @ulang nvarchar(20), -- language id @pid int -- projectid)ASDECLARE @strSQL varchar(4000)SET NOCOUNT ONSELECT DISTINCT pr.fk_resourceId, p.pageId, p.pageTitle, lang.langCode AS LanguageFROM PageResources AS pr INNER JOIN Pages AS p ON pr.fk_pageId = p.pageId INNER JOIN LString AS lc ON pr.fk_resourceId = lc.fk_resourceId INNER JOIN Languages AS lang ON lc.fk_langID = lang.langIdWHERE (p.fk_projectId = @pid)IF @ulang <> 'al' AND lang.langCode = @ulangIF @uid=0 AND lc. string LIKE '%' + @str + '%'IF @uid=1 AND lc. string = @strORDER BY pr.fk_resourceIdThanks for your help and timeJohannWed, 02 Jul 2008 05:02:53 GMTJohann Montfort