Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure based on conditions Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2008 5:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
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
Post #527163
Posted Wednesday, July 2, 2008 6:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 15,527, Visits: 27,909
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


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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #527240
Posted Wednesday, July 2, 2008 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
Thanks Grant

I tried this

WHERE (p.fk_projectId = @pid)
AND lang.langcode =
CASE
WHEN @ulang <> 'al' THEN @ulang
END
AND lc.locstring =
CASE
WHEN @uid = 0 THEN LIKE '%' + @str + '%'
WHEN @uid = 1 THEN = @str
END

but its throwing an error near the LIKE

How can I fix that?

Thanks again
Post #527265
Posted Wednesday, July 2, 2008 7:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
ok solved it

someone suggested this on another forum:-

AND @ulang <> 'al' AND lang.langCode = @ulang
and ((@uid=0 AND lc.string LIKE '%' + @str + '%') or (@uid=1 AND lc.string = @str))
Post #527283
Posted Wednesday, July 2, 2008 7:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 15,527, Visits: 27,909
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #527302
Posted Wednesday, July 2, 2008 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 14, 2009 6:45 AM
Points: 151, Visits: 107
thanks Grant

Johann
Post #527314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse