We help IT Professionals succeed at work.

Error message in User Defined Function - Must declare the variable

1,000 Views
Last Modified: 2012-06-27
This is a followon question to

https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20883452.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
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for your answer.

Biang
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
@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

Commented:
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

Author

Commented:
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.

Commented:
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,
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.