Link to home
Start Free TrialLog in
Avatar of Member_2_1316035
Member_2_1316035

asked on

Error message in User Defined Function - Must declare the variable

This is a followon question to

https://www.experts-exchange.com/questions/20883452/achieve-row-level-operation-in-a-view.html

You may not need to go there.

My problem is I get the error "Must declare the variable @tblP".  I can not figure it out.

Thanks,

Biang

The sql:


CREATE FUNCTION [dbo].[str_Conca] (@fld_pk1 varchar(400),  @fld_pk2 varchar(400), @tblP varchar(400),
  @tblF varchar(400), @fld_pk varchar(400), @fld_str varchar(400))

RETURNS varchar(4000) AS  
BEGIN
  declare @result varchar(4000)
  set @result = ''                                                                
  select @result = @result                                                        
               + case when len(@result)>0 then ', ' else '' end                  
               + @fld_str                                                
    from (select @fld_pk1, @fld_str from @tblP inner join tblF on fld_pk2=fld_pk) x                                            
    where @fld_pk1 = @fld_pk
  return @result    
END
ASKER CERTIFIED SOLUTION
Avatar of vc01778
vc01778

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Member_2_1316035
Member_2_1316035

ASKER

Thanks for your answer.

Biang
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@biang,

"I suggest you try this:
just replace the following line in your query :
from (select @fld_pk1, @fld_str from @tblP inner join tblF on fld_pk2=fld_pk) x  

by this one:
    + 'from (select ' + @fld_pk1 + ', ' + @fld_str + ' from ' + @tblP + ' inner join tblF on fld_pk2=fld_pk) x'

The error you get is because you cannot write "from @oneVariable".
"

Pls. ignore the above.

You just cannot use dynamic sql inside a UDF:

1> create function f2(@table_name varchar(100)) returns int as
2> begin
3>   declare @x int
4>   select @x=x from @table_name
5> end
6> go
Msg 1087, Level 15, State 2, Server , Procedure f2, Line 4
, Must declare the table variable '@tabl
e_name'.
1>

Rgds.

VC
To eliminate any further doubt on anybody's part, neither can you:

1> create function f2(@tabe_name varchar(100)) returns int as
2> begin
3>   declare @x int
4>   exec( 'select from @table_name ')
5>   return @x
6> end
7> go
Msg 443, Level 16, State 2, , Procedure f2, Line 4
, Invalid use of 'EXECUTE' within a function.
1>

VC
swril,

It is a great way.  Error message is gone.  But a related problem occurs, maybe tied to what VC said.

You probably need to reference to the problem I linked in the original posting.  In short, I have to pass the integer values of fld_pk1 to the function in addtion to pass fld_pk1's field name.  So I add another premeter to the UDF.  It reads "...[str_Conca] (@pkVal int, @fld_pk1 varchar(400), ... "  and the WHERE statement changes to  

"... where @fld_pk1 = @pkVal ..."

When I use the beautiful UDF in a view, an error says

"...[ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'xxxx' to a columno of data type int.''"  Note: xxxx is the real field name of @fld_pk1.

I even tried to put all where statement into the string, as you recommend for FROM part.  The similar error poped up.

Please help.
Biang,

I'm very sorry for the inconvenience caused but VC is right.

I had the same problem but I was in a stored procedure not in a UDF. So you can ignore my answer as it is not working with UDF.

regards,