April 15, 2007 at 6:59 am
Guys,
I am trying compile below function where I pass tablename as variable.
CREATE FUNCTION [dbo].[GetNextValue] (@tablename varchar(30) )
RETURNS int AS
BEGIN
declare @value int
truncate table @tablename
INSERT INTO @tablename (FNAME) VALUES ('SMITH')
select @value = id from @tablename
return @value
END
I get the following message
Msg 1087, Level 15, State 2, Procedure GetNextValue, Line 7
Must declare the table variable "@tablename".
Any suggestions/inputs to get around this would be helpful
Thanks
April 15, 2007 at 2:39 pm
Freaky function, really.
Don't see any point of doing this.
But if there is some hidden magic behind you can do it this way:
CREATE FUNCTION [dbo].[GetNextValue] (@name varchar(30) )
RETURNS int AS
BEGIN
declare @value int
DECLARE @tablename TABLE (
ID int IDENTITY(1,1),
FNAME varchar(30)
)
INSERT INTO @tablename (FNAME)
VALUES ('SMITH')
select @value = id from @tablename
return @value
END
_____________
Code for TallyGenerator
April 15, 2007 at 7:39 pm
What you are trying to do is invalid SQL. You do what you want in dynamic SQL, but you cannot do dynamic SQL in a function.
April 16, 2007 at 6:36 am
I was able to do this through dynamic sql in a stored procedure.
Thanks all for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply