pass table name as parameer in function

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)

pass table name as parameer in function

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-05-26 : 06:19:05

hi!
any body knows how to to pass a table name as parameter in function using sql server 2005

i have this example but gives me an error...
---------------------------------------------------------
ALTER FUNCTION dbo.AutoIDWithString (@FieldName as varchar(50),@TableName as varchar(50))
RETURNS varchar(800)
AS
begin
declare @RESULT varchar(1000)

SET @RESULT = SELECT MAX(CAST(LEFT(@FieldName,2)AS CHAR(2))) +
RIGHT('000' + CAST(MAX(CAST(RIGHT(@FieldName,LEN(@FieldName)-2)+1 AS INT))AS VARCHAR(5)),3)
FROM @TableName
RETURN @RESULT
END
---------------------------------------------------

the result would be like this

+-----------------------
|ID |
+-----------------------
|CL005 |
------------------------

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-26 : 06:45:35

wt is the use of @tablename in this function

ur getting the values from @fieldname only

u can use this one
ALTER FUNCTION dbo.AutoIDWithString (@FieldName as varchar(50),@TableName as varchar(50))
RETURNS varchar(800)
AS
begin
declare @RESULT varchar(1000)

SET @RESULT = SELECT MAX(CAST(LEFT(@FieldName,2)AS CHAR(2))) +
RIGHT('000' + CAST(MAX(CAST(RIGHT(@FieldName,LEN(@FieldName)-2)+1 AS INT))AS VARCHAR(5)),3)

RETURN @RESULT
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-26 : 06:52:09

Make sure you read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-26 : 07:44:55

Dfinately read the link Madhi posted. It's an awesome resource.

Let me paraphrase the important bit for you though:

You can't use dynamic SQL in functions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-05-26 : 22:26:34

quote:
Originally posted by bklr

wt is the use of @tablename in this function

ur getting the values from @fieldname only

u can use this one
ALTER FUNCTION dbo.AutoIDWithString (@FieldName as varchar(50),@TableName as varchar(50))
RETURNS varchar(800)
AS
begin
declare @RESULT varchar(1000)

SET @RESULT = SELECT MAX(CAST(LEFT(@FieldName,2)AS CHAR(2))) +
RIGHT('000' + CAST(MAX(CAST(RIGHT(@FieldName,LEN(@FieldName)-2)+1 AS INT))AS VARCHAR(5)),3)

RETURN @RESULT
END

thanks bklr..it works for me..maybe i don't need to make the table name as parameter..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources