Author |
Topic  |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 04/15/2007 : 08:54:08
|
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
|
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 04/15/2007 : 09:04:58
|
quote: @tablename varchar(30) ... INSERT INTO @tablename (FNAME) VALUES ('SMITH')
You can't use table name as a variable in a DML statement. The only way you can do that is via Dynamic SQL. But dynamic SQL is not supported in function
quote: truncate table @tablename INSERT INTO @tablename (FNAME) VALUES ('SMITH')
ignoring the table name as a variable issue aside, you can't perform INSERT / UPDATE / DELETE in a function other than table variable that is local to the function.
Why are you doing this in a function ? What are you trying to achieve ?
KH
|
 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 04/15/2007 : 09:22:08
|
Current code in our application uses max(id)+1 to autogenerate the id values in 60 tables. Since we didnt want to the change the code and insert dmls in the code. I am creating 60 tables with corresponding identity id and dummy column in these tables. What this function will do is it will get nextvalue of the id column, this all max(id)+1 code will be replaced by getnextvalue(tablename).
Is there any way to acheive this
Thanks |
 |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 04/15/2007 : 09:26:13
|
Well, you can use create a function for each of the tables or create a function and use IF . . ELSE x 60 times or stored procedure with dynamic sql.
Read this http://www.sommarskog.se/dynamic_sql.html before you decide what to do.
KH
|
 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 04/15/2007 : 10:45:37
|
I have created a stored procedure where I pass the tablename into dynamic sql and spit the id.
How can create a function with tablename as variable which executes this stored procedure. Is this possible??
Thanks
alter PROCEDURE general_select1 @tblname sysname AS
DECLARE @sql2 nvarchar(4000) SELECT @sql2 = ' DELETE FROM' + ' dbo.' + quotename(@tblname) EXEC sp_executesql @sql2
DECLARE @sql1 nvarchar(4000) SELECT @sql1 = ' insert into' + ' dbo.' + quotename(@tblname) + ' values (''smith'')' EXEC sp_executesql @sql1
DECLARE @sql nvarchar(4000) SELECT @sql = ' SELECT ID+1' + ' FROM dbo.' + quotename(@tblname) + ' WHERE FNAME = ''smith''' EXEC sp_executesql @sql
execute general_select1 'FACTS_CASE_ID_SEQ' --- output = 6 |
 |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 04/15/2007 : 10:47:04
|
quote: How can create a function with tablename as variable which executes this stored procedure. Is this possible??
No. that's also one of the things you can't do with function.
KH
|
 |
|
|
Topic  |
|