| Author |
 Topic  |
|
redbrad0
Advanced Member
USA
3725 Posts |
 Posted - 22 May 2004 : 12:39:15
|
I have been creating some stored procedures, but having a small problem on just one. When specifying the table name, I have to but the database name in front of it. So an example would be
CREATE procedure sp_CheckThis (@MemberName nvarchar)
as
select MEMBER_ID
from eznetideas.MEMBERS
where NAME = @MemberName
GO
Does anyone know a reason for this? |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 22 May 2004 : 13:46:18
|
Firstly, never prefix stored procedures with sp_ as this causes SQL Server to presume it is a System stored procedure and will execute slower.
You shouldn't need to specify the database name if the stored proc is in the same db as the table.
Also, specify the size of the nvarchar column
egCREATE procedure sp_CheckThis (@MemberName nvarchar(50)) |
The UK MkIVs Forum |
 |
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 22 May 2004 : 14:30:44
|
If you need to ever specify the database name, then you also need to specify the schema owner as well before the table name.
databasename.schemaowner.tablename
eznetideas.dbo.MEMBERS
but if the table is in the same database as the stored proc then you might as well not specify the database name.
You should also take note of Davids comments as well. If you want to prefix your stored procs with something then anything other than sp_ will do, e.g. usp_ |
Edited by - pweighill on 22 May 2004 14:31:37 |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 22 May 2004 : 18:06:39
|
Oh well I saw on http://www.4guysfromrolla.com/webtech/111499-1.shtml that he named his stuff sp_. I will change it. Basically I am having to specify the username that the sp was created under. I was incorrect in saying it was the database name. So I am hoping to not have to say the username as that would make these scripts not as "generic" and to where they could be used on other sites. Basically what I am doing is creating a snitz forum that uses SP's. I have most of the code done, but was having a problem using if then statments in the SP. do you guys know whats wrong with this?
CREATE procedure sp_TestSP (@ShowAdminOnly nvarchar(5), @TopicID int)
as
select M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.M_HOMEPAGE, M.MEMBER_ID, M.M_LEVEL, M.M_POSTS, M.M_COUNTRY, T.T_DATE, T.T_SUBJECT, T.T_AUTHOR, T.TOPIC_ID, T.T_STATUS, T.T_LAST_EDIT, T.T_LAST_EDITBY, T.T_LAST_POST, T.T_SIG, T.T_REPLIES, C.CAT_STATUS, C.CAT_ID, C.CAT_NAME, C.CAT_SUBSCRIPTION, C.CAT_MODERATION, F.F_STATUS, F.FORUM_ID, F.F_SUBSCRIPTION, F.F_SUBJECT, F.F_MODERATION, T.T_MESSAGE
if @ShowAdminOnly='true'
, M.M_SIG
end if
FROM dbo.FORUM_TOPICS T, dbo.FORUM_FORUM F, dbo.FORUM_CATEGORY C, dbo.FORUM_MEMBERS M WHERE T.TOPIC_ID = @TopicID AND F.FORUM_ID = T.FORUM_ID AND C.CAT_ID = T.CAT_ID AND M.MEMBER_ID = T.T_AUTHOR
else
Go
|
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 22 May 2004 : 18:43:09
|
you can't do that unless you are building up a sql string to execute which would then be pointless.
Have a look at the Case statement |
The UK MkIVs Forum |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 07 June 2004 : 15:15:17
|
Brad,
I started the same thing a couple of months ago. Your best bet would be to pull the SIG all the time and only display it if needed. You could also create two queries and decide which query to pull in the ASP.
Another issue with using cases or if's to change the actual SQL is that you lose the advantage that the query has been precompiled. Since the query can be different with every request the query will be precompiled opposed to having a static query. I tend to use COALESCE() a lot when I might or might not need to pass a variable. Like:
@mname varchar(10) = null
WHERE m.name = COALESCE(@mname,m.name)
If you don't pass the @mname var from the asp page then it will be NULL, using the COALECSE function, the value in the column will be used, resulting in an always true statement. I have used this on searches with very good results.
Hope that helps.
|
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 07 June 2004 : 19:26:39
|
| Thanks for the reply. So you went thru and change snitz to a pretty much SP forum? I went thru and did all of the included functions and the results seemed pretty good. Seemed to run faster, but wish there was a way to make every query a SP to see how well the site would run. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 08 June 2004 : 11:47:39
|
hehe, that was/is the plan. I originally was going to make it a MOD. Therefore, I created a page that will create the SPROCS in the database. I also wanted to make it compatible with Access and MSSQL. I have only done the Members Page  . Creating the most efficient sprocs that support Access and MSSQL has not been too much fun. Doing it in MSSQL would be a lot easier and more efficient. I am considering doing just that. For example, in MSSQL I could create a single sproc for the posting of a message; you could update counts, last post date, last post member, etc. I am still debating with myself whether to make it an Access version or just MSSQL, which is what I will be using anyways. |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 08 June 2004 : 12:25:50
|
My thought was to just make a mssql version. The way I did it was add a additional field in the config.asp file which looked like below..
strDBType = "sqlserver"
'strDBType = "access"
'strDBType = "mysql"
strDBType2 = "sp" ' # WITH THIS UN COMMENTED THIS FORUM WILL USE STORED PROCEDURES FOR ALOT OF FUNCTIONS
' # IF YOU USE THE ABOVE STORED PROCEDURE YOUR FORUM MOST USE DB FOR AUTHORIZATION
' # AND ALL OF YOUR FORUM TABLES MUST START WITH FORUM_
then in the forum files I was doing this...
set rsBadWord = Server.CreateObject("ADODB.Recordset")
If strDBType2="sp" Then
' # LETS RUN THE STORED PROCEDURE
Set rsBadWord = my_Conn.Execute ("chkBadWords")
Else
strSqlb = "SELECT B_BADWORD, B_REPLACE "
strSqlb = strSqlb & " FROM " & strFilterTablePrefix & "BADWORDS "
rsBadWord.open strSqlb, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
End If
This way I could turn on and off the SP's |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
Edited by - redbrad0 on 08 June 2004 12:26:17 |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 08 June 2004 : 12:53:47
|
| Did you only do the functions, or did you do all the pages? I was just going to replace the query. In fact, I thought about doing a scipt that would (on unmodded) forums could search for the sql and replace with the respective sproc. But, if it's unmodded then you could just replace the file - so what would be the point. |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 09 June 2004 : 09:12:38
|
So.....when is an entire forum package going to be released.  |
Erick
Snowmobile Fanatics
|
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 09 June 2004 : 10:11:51
|
Mine will be a while...
erick, are you Access SQLServer based? |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 09 June 2004 : 12:09:46
|
| MSSQL, MySQL, or Access. Those are my choices. Right now I'm using MSSQL and continue to develop on it because it does so much more than either MySQL or Access. |
Erick
Snowmobile Fanatics
|
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 09 June 2004 : 12:14:46
|
| OK. I think I'm going to chuck Access support - it will be so much easier to support MSSQL only. |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
Topic |
|
Topic Locked
