July 2, 2008 at 5:02 am
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
July 2, 2008 at 6:32 am
Johann Montfort (7/2/2008)
Dear AllHow 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
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:
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
...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2008 at 6:54 am
July 2, 2008 at 7:25 am
Great. If you did need to pursue the CASE statements, you can't use "= LIKE." So you'd have to change
AND lc.locstring =
to
AND lc.locstring LIKE
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2008 at 7:37 am
thanks Grant
Johann
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply