Johann Montfort (7/2/2008)

---

Dear All

How can I write a stored procedure based on condition. At the moment, my code is not working. Here is my example

ALTER PROCEDURE [dbo].[FindStrings]
(
@uid bit, -- unique string
@str nvarchar(1000), --string to be searched
@ulang nvarchar(20), -- language id
@pid int -- projectid
)
AS

DECLARE @strSQL varchar(4000)

SET NOCOUNT ON

SELECT DISTINCT pr.fk_resourceId, p.pageId, p.pageTitle, lang.langCode AS Language
FROM 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.langId
WHERE (p.fk_projectId = @pid)

IF @ulang <> 'al'
AND lang.langCode = @ulang

IF @uid=0
AND lc. string LIKE '%' + @str + '%'
IF @uid=1
AND lc. string = @str

ORDER BY pr.fk_resourceId

Thanks for your help and time

Johann

SELECT DISTINCT
        pr.fk_resourceId,
        p.pageId,
        p.pageTitle,
        lang.langCode AS Language
FROM    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.langId
WHERE   ( p.fk_projectId = @pid )
        AND lang.langcode = CASE WHEN @ulang = 'al' THEN @ulang
                                 ELSE lang.langCode
                            END
...