| Author |
Topic |
|
rasheed
Starting Member
7 Posts |
 Posted - 2008-05-25 : 15:38:38
|
| Hello,It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?For example, a simple stored procedure is this:CREATE PROCEDURE SelectTop(@tableName sysname)ASBEGINExecute('Select top 10 * from ' + @tableName + ';')ENDI want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-25 : 18:29:17
|
| You can't use dynamic sql in a function.You could have all the possible tables coded in the function and use if statements.Another possibility is a clr.You know you could use a CTE without creating a temp table?seehttp://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rasheed
Starting Member
7 Posts |
Posted - 2008-05-25 : 18:45:42
|
| Hey nr, thanks for the reply. I hadn't heard about CTE's, those seem pretty cool.If it is not possible to do dynamic sql in a function, how would you go about writing a stored procedure like SelectTop [my example stored procedure above] in such a way that the result set could be queried, without the use of a temp table? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-25 : 19:20:59
|
| You can't use dynamic sql in a CTE - depends where the table name comes from.You could use openrowset but that woul dmean another connection.Why don't you want to use a temp table?How would you know what the column names are?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rasheed
Starting Member
7 Posts |
Posted - 2008-05-25 : 19:39:15
|
| Well, my situation is this: I have 15 tables. Each of these 15 tables has columns (date, transactionID, dollarAmount), which, e.g., corresponds to inputs and withdrawals from a bank account. There may be more bank account tables created in the future. One useful thing to do to any bank account table is to examine the date,sum(dollarAmount)grouped by date. So ideally what i would like to do is to have a stored procedure CREATE PROCEDURE sp_GetDailyAccountActivity(@bankTable sysname)ASBEGINExecute('Select date,sum(dollarAmount) as dollarAmount from ' + @tableName + 'group by date order by date;')ENDWhat I would like is for the user to be able to query this result set. The problem with directing the resultset to a temp table is that many users may be using this query, operating on one of the 15 bank tables simultaneously. So if the resultset is sent to a temp table, e.g. accountDailySummary, then when different users run the query there will be problems...Im not sure if I explicated my problem clearly enough... |
|
|
|
rasheed
Starting Member
7 Posts |
Posted - 2008-05-25 : 19:40:41
|
| Sorry, the procedure definition has a typo, it ought to be:CREATE PROCEDURE sp_GetDailyAccountActivity(@bankTable sysname)ASBEGINExecute('Select date,sum(dollarAmount) as dollarAmount from ' + @bankTable+ 'group by date order by date;')END(@bankTable replaces the erroneous @tableName that was there before) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-25 : 19:57:57
|
| How are the users accessing the resultset?You have a permamnent table there - a temp table is specfic to the batch so different users will get different copies of the table.Depends on how they are accessing it though. Can you give an example or say what the client is?As you only have 15 tables I don't see the issue with using a function to generate the resultset (or CTE for that matter).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rasheed
Starting Member
7 Posts |
Posted - 2008-05-25 : 20:25:57
|
| Well, users would just be running the command in a SQL Server Management Studio session. So they would be runningexec sp_GetDailyAccountActivity 'bankTable1'If a user runs the command above, they won't be able to query the output, right? Even if i direct into a temp table?I have only 15 bank tables now, but there may be more tables in the future--wouldnt that make the use of the function to generate the result set suboptimal (because then i would have to re-write the function each time a new bank table comes up)? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-25 : 20:41:50
|
| The users runcreate table #MyTbl (date datetime, dollarAmount money)insert #MyTblexec sp_GetDailyAccountActivity 'bankTable1'then they can query #MyTbl.It's a bad idea to name SPs sp_...Reserve that for system SPs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rasheed
Starting Member
7 Posts |
Posted - 2008-05-25 : 20:48:18
|
| That is very nice, I didn't know that syntax existed to direct the output of a stored procedure into a temp table.If the user does not know the column names of the output, is there still a way to direct the output into a table? Something likeselect into #MyTblexec sp_GetDailyAccountActivity[i know that syntax is not legitimate, but something analagous? or must the user know the columns of the output?] |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-25 : 21:34:25
|
| Not without using openrowset/openqueryselect *into #MyTblfrom openquery(LocalLinkedServer, 'exec mydb,,sp_GetDailyAccountActivity')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rasheed
Starting Member
7 Posts |
Posted - 2008-05-25 : 21:58:52
|
| Great advice, thanks! |
|
|
|
|
Passing table names to table valued functions