dynamic function - 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 2000 Forums

Transact-SQL (2000)

dynamic function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-16 : 09:33:32

David writes "hello, hope you can help me...
i want to write this kind of function:

ALTER FUNCTION [dbo].[getsize]
(
@tableId int,
)
RETURNS int
AS 
BEGIN

DECLARE @table nvarchar(40)
SET @table = 'table_' + convert(nvarchar(10), @tableId)

DECLARE @SQL nvarchar(512)
SET @SQL = 'SELECT @value = count(*) FROM dbo.[' + @table+ ']'

DECLARE @value int
EXEC sp_executesql @SQL, N'@value int out', @value out
                                
RETURN @value
END

but, as you sure now, this function is not good
as i get an error, "only functions and ext. proc. can be used"
is there any other solution?"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-16 : 09:46:11

[code]
CREATE FUNCTION [dbo].[getsize]
(
@tableId int,
)
RETURNS int
AS
BEGIN
DECLARE @table nvarchar(40), @RowCount int
SET @table = 'table_' + convert(nvarchar(10), @tableId)

SELECT @RowCount = ind.rowcnt
FROM sysobjects sys
LEFT OUTER JOIN sysindexes ind
ON ind.[id] = ind.[id]
WHERE ind.xtype = 'U' AND ind.indid < 2 and ind.[name] = @table

return @RowCount
END
[/code]

Chirag

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 10:46:36

David,
Beware that, the count u r getting is not accurate, if u follow the path of chirag!!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70483

Srinika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 11:47:28

How is the following (however this is a stored procedure and not working exactly same as a function):


Create procedure Row_Count (@tblName varchar(100), @Rowcount nvarchar(30) output)
as
declare @strSQL nvarchar (4000), @out int
set @out = 0

SELECT @strSQL = 'select @outInside = count(*) from ' + @tblName 
SELECT @Rowcount = N' @outInside int OUTPUT'

EXEC sp_ExecuteSQL @strSQL, @Rowcount, @outInside = @out OUTPUT

Select @out

-- Call this as :
-- Row_Count 'urTblName', 0

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-16 : 12:25:41

http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

david_tm
Starting Member

2 Posts

Posted - 2006-08-19 : 07:25:08

Srinika...
thanx

but i need function
it's not hard to write such sp

per asoera ad astra

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 08:50:17

David,
I dont think then you will be able to acheive the same using function, you can try the function which i posted, but for that there are lots of contraints for its accuracy as its posted in Srinika's thread.

But what is the purpose of writing this kind of function? may be someone over here, can give you some better option to do it in a different way.

BTW

quote:
per asoera ad astra

What does this means ???

Chirag

david_tm
Starting Member

2 Posts

Posted - 2006-08-26 : 02:01:04

@chiragkhabaria

oh my, it was misspeled

from WikiPedia

per aspera ad astra
"through hardships to the stars"

From Seneca the Younger. Motto of NASA and the South African Air Force. A common variant, ad astra per aspera ("to the stars through hardships"), is the state motto of Kansas. Ad Astra ("To the Stars") is the title of a magazine published by the National Space Society.

per aspera ad astra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources