| Author |
 Topic  |
|
|
e3stone
Average Member
USA
885 Posts |
 Posted - 01 August 2002 : 11:38:44
|
I'm messing around with some stored procedures and can't seem to figure out how to pass the strTablePrefix value into it. Here's what I have:
CREATE PROCEDURE sp_GetStatistics2
(
@strTablePrefix nvarchar(100)
)
AS
SELECT
*
FROM
@strTablePrefix + TOTALS
However, I've tried +, &, etc, and can't figure out what operator to use in order to join the two. I get an "Error near '+'" error dialog. Can anyone help me out with being able to use the strTablePrefix in my stored procedures? ....or should I just hard-code the table prefix in the stored procedure?
<-- Eric --> |
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 01 August 2002 : 12:49:42
|
You can't set the table name at run time without using dynamic SQL. SqlTeam's FAQ (http://www.sqlteam.com/FAQ.asp) has links to some related articles. Basically when you run dynamic SQL you're tossing the recompilation benefits of a stored procedure.
Whenever I've need to do this I've just hardcoded the prefix in the proc just like you mentioned. Works like a champ.
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums
Edited by - grazman on 01 August 2002 12:50:56 |
 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 01 August 2002 : 18:35:20
|
Hey Bill are you planning on trying to create stored procedures for snitz 3.4? it would be nice if this was in the next version where you could check to use stored procedures or dynamic sql.
Brad
Web Hosting with SQL Server @ $24.95 per month
|
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 01 August 2002 : 23:40:43
|
quote:
Hey Bill are you planning on trying to create stored procedures for snitz 3.4? it would be nice if this was in the next version where you could check to use stored procedures or dynamic sql.
FYI - I sort of already wrote these (http://www.sqlteam.com/snitz/Articles/storedprocs.htm). They work just fine with 3.3.03. I use them on SqlTeam.com to transfer questions to the forum.
I'd be happy to help with that on 3.4 also. In 3.4 the database access is soooo cleaned up you probably won't need stored procedures though. A stored procedure wrapped around a single SELECT statement doesn't buy you much at all. SQL Server will compile the SELECT statement and reuse the query plan when other users run the same query (i.e. list the topics in a forum). The one exception might be posting. There are a couple of tables that need to be updated.
After 3.4 comes out we'll see what the final data access piece looks like and go from there.
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums
Edited by - grazman on 01 August 2002 23:41:17 |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 06 August 2002 : 18:09:14
|
Does anyone have a list of stored procedures and the code for them that you've used on your forum? I'm in the process of doing this, but there are a lot of stinkin' queries. Plus, I just wanted to see if the ones I've written are similar to the pre-existing ones.
<-- Eric --> |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 07 August 2002 : 22:45:47
|
quote:
Does anyone have a list of stored procedures and the code for them that you've used on your forum? I'm in the process of doing this, but there are a lot of stinkin' queries. Plus, I just wanted to see if the ones I've written are similar to the pre-existing ones.
I'm not sure exactly what you mean by this. The stored procedures I wrote are linked to above. In terms of performance benefit, 3.4 will vastly outweigh anything you could do by replacing client-side SQL with stored procedures. According to a post in the Announcements forum, they are planning to upgrade these forums in a day or so. We should get a copy soon after that. I personally think you'd be better to build on that base than the current version.
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 16 August 2002 : 10:44:09
|
CREATE PROCEDURE sp_GetStatistics2
( @strTablePrefix nvarchar(100) )
AS
exec ('SELECT * FROM ' + @strTablePrefix + 'TOTALS')
hth |
|
|
| |
Topic |
|
Topic Locked
