October 1, 2010 at 6:30 am
Hi,
I'm in process of rewriting a function which is returning a table.. below is the sample code of the function...
create function [dbo].[udfgetdata]
(
@parm1 varchar(10)
,@parm2 varchar(10)
,@parm3 varchar(10)
,@parm4 varchar(10)
,@parm5 varchar(10)
,@parm6 varchar(10)
,@parm7 varchar(10)
)
returns table as return
(
SELECT
col1, col2, col3....
from
T1 with (nolock)
join
t2 on and so on.....
WHERE
T1.gcardId = ISNULL( @param1,T1.gcardId)
AND
(
( isnull( @param2, 1 ) = 1 and T1.taskCompleteBit = 0 )
or
isnull( @param2, 1 ) <> 1
)
AND
T1.prptyId = isnull( @param3, T1.prptyId)
)
I thought of rewriting this query in in nested if conditions based on which columns are null and which not out of the all supplied of the supplied parameter to avoid the isnull function.... but onec I've written the query I'm unable to create the function... its giving syntax error....
As my first stmt of the function is sumthing like below
IF (@param1 null and @param2 not null.....)
Begin
select .....
else if (next case...)
end
and so on till all the conditions are satisfied...
Now I wanna know if this is possible if I can write this function in this way or there is any work around for doing this
Any help will be appreciated...
Many Thanks,
Rohit
October 3, 2010 at 1:17 am
Please provide the complete code of the function and the syntax error you are getting.
Meanwhile the BEGIN...END in the below part of your function looks suspiscious
IF (@param1 null and @param2 not null.....)
Begin
select .....
else if (next case...)
end
It should be like this
IF (@param1 null and @param2 not null.....)
Begin
select .....
End
else if (next case...)
Begin
select .....
End
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply