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.
| Author |
Topic |
|
zosonc
Starting Member
4 Posts |
 Posted - 2008-03-02 : 08:15:06
|
| I'm trying to write a function that I can run with passing the table name as a parameter. I want to return an integer. The tables wll be differnet types (different colums) but they all have a similar field that i want to get a count of. Someting alng the lines of this:Create FUNCTION [dbo].[fn_GetItemsToPromote] ( @TableName nvarchar(100) )RETURNS intASBEGINDeclare @SQL nvarchar(500)return select count(*) from [@TableName]where promote = 1ENDIt doesnt like the @TableName. Can anyone show me how to do this correctly?Thanks! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-03-02 : 08:40:37
|
You will have to use Dynamic SQL to do that. But Dynamic SQL does not work in Function only in Stored Procedure
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-02 : 08:54:31
|
| This usually indicates a bad database design; all of your items should be stored in the same table if you are using a common function or stored procedure to access them.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
ranganath
Posting Yak Master
209 Posts |
Posted - 2008-03-02 : 23:53:43
|
| Hi,try with this DECLARE @Tablename varchar(100)SET @Tablename = ''DECLARE @sql varchar(1000)SET @Sql = ''SET @Sql = 'select count(*) From '+@Tablename +' where promote = 1'PRINT @sql EXEC (@Sql) |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
|
|
|
Table Name as Function Parameter