| 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 |
|
Declaring table variable