SQL Server - store procedure return vale - Asked By shekhar kumar on 04-Feb-11 12:50 AM

I have two table
  
GROUP_MST          SUBGROUP_MST
   ACTIVITY_URL           SUBACTIVITY_URL
   GROUP_CODE          SUBGROUP_CODE 
                      GROUP_CODE
And i have writte a procedure as below

ALTER PROCEDURE PROC_GetActSubActDetails
(
    @Ip_Page_Name varchar,
    @Op_Act_Id int output,
    @Op_Subact_Id int output
)
as
BEGIN
   declare @count_act int;
   declare @count_subact int;

 select @count_subact=count(*) from SUBGROUP_MST
  where ltrim(rtrim(upper(SUBACTIVITY_URL))) = ltrim(rtrim(upper(@Ip_Page_Name)));

 select @count_act=count(*) from GROUP_MST where 
   ltrim(rtrim(upper(ACTIVITY_URL))) = ltrim(rtrim(upper(@Ip_Page_Name)));

if @count_subact = 1
 begin
  select @Op_Subact_Id=SUBGROUP_CODE,@Op_Act_Id=GROUP_CODE from SUBGROUP_MST
  where ltrim(rtrim(upper(SUBACTIVITY_URL))) = ltrim(rtrim(upper(@Ip_Page_Name)))
 end
  
 if @count_subact = 0
 begin
  select @Op_Subact_Id=0 ,@Op_Act_Id=GROUP_CODE
   from GROUP_MST
   where ltrim(rtrim(upper(ACTIVITY_URL))) = ltrim(rtrim(upper(@Ip_Page_Name)));
 end
  
end

But when I am running this procedure always gives me null values. There is one value in the Group_Mst table. and i am passing the correct Page_Name which is in the table.

Please help me out.
Thanks

Tejaswini Patil replied to shekhar kumar on 04-Feb-11 08:16 AM
Hi,


The stored procedure parameter @Ip_Page_Name is declared as varchar.

When a varchar is declared without specifing the length the by default the length is taken as 1.

So the paramter will always hold only first character of the value passed to it.

Solution:
        The stored procedure input parameter @Ip_Page_Name  should be of the same length of ACTIVITY_URL & SUBACTIVITY_URL.
        Here I assume that both these columns are of same data type & length.  If not then take whichever is longer.