could any one help me finding the Error with SP

create function Fun12(@InsCd varchar)
returns varchar(100)
as 
be开发者_Python百科gin
declare @ret varchar(52)
set @ret = (select [InsName] from [Assignment4].[dbo].[1466] 
   where rtrim(ltrim([InsCd]))= @InsCd)
return @ret
end

Executing:

declare @r varchar(50)
exec @r = dbo.Fun12 '436'

select @r

I am getting NULL value. Could any one please help me finding the error?

You need to specify a size for your parameter @InsCd.

Some thing like this but you might want to use another value than 20 depending on the size of field InsCd.

create function Fun12(@InsCd varchar(20))

First, you should make sure that the code contained by the function actually returns something when you run it directly in SQL Server Management Studio (SSMS):

-- SQL
select InsName from [1466] where rtrim(ltrim([InsCd])) = '436';

In this case, I would use a stored procedure rather than a function. You could also use the SSMS Profiler (Tools > Profiler) to monitor the traffic to SQL Server. This way, you can actually see what gets executed, see the parameters for SPs, etc.