SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Declaring table variable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 04/15/2007 :  08:54:08  Show Profile  Reply with Quote
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
17584 Posts

Posted - 04/15/2007 :  09:04:58  Show Profile  Reply with Quote
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

Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 04/15/2007 :  09:22:08  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 04/15/2007 :  09:26:13  Show Profile  Reply with Quote
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

Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 04/15/2007 :  10:45:37  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 04/15/2007 :  10:47:04  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000