CODEWITHSUNDEEP
sql-server-2008
Shine
Shine

Reputation: 1423

could any one help me finding the Error with SP

create function Fun12(@InsCd varchar)
returns varchar(100)
as 
begin
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?

Upvotes: 0

Views: 42

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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))

Upvotes: 2

npclaudiu
npclaudiu

Reputation: 2441

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.

Upvotes: 0

Related Questions