Declaring table variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Declaring table variable

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-15 : 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)

17689 Posts

Posted - 2007-04-15 : 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 ?

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-15 : 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)

17689 Posts

Posted - 2007-04-15 : 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.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-15 : 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)

17689 Posts

Posted - 2007-04-15 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources