stored procedure based on conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure based on conditions

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-02 : 07:04:02

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 08:57:02

not really sure why you've got those IF's in there to be honest, but i think you may just be over complicating it and perhaps just need an OR ?

as in...


WHERE (p.fk_projectId = @pid)
AND @ulang <> 'al' AND lang.langCode = @ulang
and ((@uid=0 AND lc. string LIKE '%' + @str + '%') or (@uid=1 AND lc. string = @str))
ORDER BY pr.fk_resourceId

may not be the right logic you're after, but i'm not very clear from your example

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:05:46

Also refer www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-02 : 09:10:38

wow

excellent, thanks

but what does
AND @ulang <> 'al' AND lang.langCode = @ulang

and

and ((@uid=0 AND lc. string LIKE '%' + @str + '%') or (@uid=1 AND lc. string = @str))

mean?

Sorry quite new to T-SQL

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-02 : 09:14:59

hi

this is not working

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

when lang = 'al', its not returning any results, and it should return all languages

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 09:15:05

well the first one is just the same as you had in the first IF

the 2nd one basically says check all the other conditions and also check that...
(@uid = 0 and lc.string like '%' + @str + '%') OR (@uid=1 AND lc. string = @str)

...so only one of those pairs of conditions can evaluate to true. make sense?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 09:19:33

so, if it's not 'al' then it should return everything?

try this line instead (i think)...

AND ((@ulang = 'al') or (@ulang <> 'al' and lang.langcode = @ulang))

Em

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-02 : 09:21:40

no, if it is al, then it should return all the languages.

if its something else, then it will be a language code

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 09:22:45

right, the line i gave you above should do it then

Em

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-02 : 09:28:51

excellent

it works

so
what exactly does this mean?

AND (@ulang = 'al')

I understand the second part of the clause

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 09:31:23

it's just checking if @ulang = 'al' and by putting it in an OR with the other part only one of those conditions will evaulate to true. in this case if @ulang = 'al' evaluates to true then it doesn't limit the langcode column at all

Em

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-07-02 : 09:36:14

excellent stuff mate

much appreciated.

Have another 2 stored procs to do, hopefully will get them right this time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 14:03:06

quote:
Originally posted by elancaster

so, if it's not 'al' then it should return everything?

try this line instead (i think)...

AND ((@ulang = 'al') or (@ulang <> 'al' and lang.langcode = @ulang))

Em

i dont think you even want that condition check its redundant. it evaluates second part only when @ulang = 'al' is false i.e @ulang <> 'al' so you dont have explicitly specify it again. i think this is enough


AND (@ulang = 'al' or  lang.langcode = @ulang)

similarly other conditions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources