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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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
"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
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
ASKER
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.
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,
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,
ASKER
Biang