May 13, 2002 at 2:46 am
First of hello, as this is my first visit to these forums.
I am fairly new to using stored procedures, and have so far only used very simple ones. Now I need to perform a somewhat more complicated task, that I can't figure out. Basically I have 2 tables for a portal you need membership to browse, one for people applying for membership([new_members]) and one for those that already has it([members]). In the admin area I have the members transfered from the one table to the other, and thats no problem, where I run into problems is I also need to extract some information from the tables simultaneously...
So far my attempt at doing it looks like this:
USE PortalSQL
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_transfer_members' AND type = 'P')
DROP PROCEDURE [sp_transfer_members]
GO
CREATE PROCEDURE [sp_transfer_members]
@new_member_id int
AS
SET NOCOUNT ON
Begin Transaction
INSERT [members]
(members.member_first_name,
members.member_last_name,
members.member_login,
members.member_password,
members.member_email,
members.member_city,
members.member_zip,
members.member_address1,
members.member_address2,
members.member_address3,
members.member_ph_work,
members.member_ph_work_ext,
members.member_fax,
members.club_status_id,
members.member_date_added,
members.security_level_id)
SELECT
new_members.member_first_name,
new_members.member_last_name,
new_members.member_login,
new_members.member_password,
new_members.member_email,
new_members.member_city,
new_members.member_zip,
new_members.member_address1,
new_members.member_address2,
new_members.member_address3,
new_members.member_ph_work,
new_members.member_ph_work_ext,
new_members.member_fax,
new_members.club_status_id,
new_members.member_date_added,
new_members.security_level_id FROM new_members
WHERE new_member_id = @new_member_id
DELETE FROM [new_members] WHERE new_member_id = @new_member_id
COMMIT
GO
but I can't figure out how to get the values: (new_)members.first_name, (new_)members.last_name, (new_)members.login, (new_)members.password and (new_)members.email. I need them to send out 2 confirmation mails(one for admin one for user)...
I already have a working solution using a sql statement done in the asp page, but would preffer a stored procedure... any hints, help, pointers etc, would be much appreciated!
I am not a very níce person
-
Signature? I don't need no stinking signature!
May 13, 2002 at 4:41 am
If you want SQL to send it then you can use xp_sendmail. If you will send via ASP page then do as a select to return the data from SP.
In either circumstance create a variable for each item. Like so
DECLARE @first_name datatypegoeshere
DECLARE @last_name datatypegoeshere
DECLARE @login datatypegoeshere
DECLARE @password datatypegoeshere
DECLARE @email datatypegoeshere
Then before you run
quote:
DELETE FROM [new_members] WHERE new_member_id = @new_member_id
Do something like this.
SELECT
@first_name = new_members.member_first_name,
@last_name = new_members.member_last_name,
@login = new_members.member_login,
@password = new_members.member_password,
@email = new_members.member_email
FROM
new_members
WHERE
new_member_id = @new_member_id
Then befoe your delete also do use
EXEC xp_sendmail ......(See BOL for details)
or if you will use ASP to send the email then drop the variables out and let the select return the data. TO make things easier to read I usually seperate different pieces by a blank line and sometimes depending of just how big a BEGIN before one step and an END after to offset it.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 05/13/2002 04:42:32 AM
May 13, 2002 at 4:55 am
Right, so I have to do a serperate select statement, I was kinda hoping I could just pass the data from the first.. hrm, oh well, it may just be my aesthetics but it looks so bulky for a relatively simple query... thx tho
-
Signature? I don't need no stinking signature!
-
Signature? I don't need no stinking signature!
May 13, 2002 at 5:40 am
Maybe I am not clear enough?
The values(new_members.first_name, new_members.last_name, new_members.login, new_members.password and new_members.email)
inserted from [new_members] into [members]
Is what I really want to be able to return to my application, is it really nercesarry for me to perform a seperate query in the Stored procedure? What I was thinking is there must be someway I can pass it from the select statement I am already doing, but then again, as you may have I am somewhat on new grounds here...
-
Signature? I don't need no stinking signature!
-
Signature? I don't need no stinking signature!
May 13, 2002 at 6:33 am
The only other way I see to avoit the extra SELECT statement is to make a varibale for each itme in the SELECT then do yuor SELECT first like so
SELECT @var1 = col1, @var2 = col2 FROM tblX WHEN colx = @valx
THEN do your insert
INSERT INTO tblZ (col1, col2) VALUE (@var1, @var2)
THEN delete you record from original table
DELETE tblX WHERE colx = @valx
And finally pass out you values you need either of two ways.
SELECT @var1 AS col1, @var2 AS col2
which comes out as a recordset
or You can use OUTPUT paramters in your SP declaration and set them when you make your first SELECT run. Like so
CREATE PROC ip_Test
@valx INT,
@var1 VARCHAR(10) OUTPUT,
@var2 VARCHAR(10) OUTPUT
AS
do SELECT like above
Just make sure you use the Ado Procedure command type so you can get your output parameters.
(Note: this is the prefered for passing single row values as it has less going across the ADO connection, but it can be a bit of a hassel)
Hope that makes sense. If not, let me know and I can get some code posted.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 13, 2002 at 6:49 am
Aye, I think ur final solution was really what I was looking for, I just couldn't figure out the syntax of it, been trying various stuff in that vicinity but never quite got near enough to figure it out, thx a lot for the fast and comprehensive help!
-
Signature? I don't need no stinking signature!
-
Signature? I don't need no stinking signature!
May 14, 2002 at 2:15 am
Still lost here, I've reworked the initial SP with:
[declare sp etc...]
@new_member_first_name nvarchar(20) OUTPUT,
@new_member_last_name nvarchar(20) OUTPUT,
@new_member_login nvarchar(15) OUTPUT,
@new_member_password nvarchar(15) OUTPUT,
@new_member_email nvarchar(30) OUTPUT
[yadda yadda yadda...]
SELECT member_first_name = @new_member_first_name,
member_last_name = @new_member_last_name,
member_login = @new_member_login,
member_email = @new_member_email,
member_password = @new_member_password
FROM new_members WHERE new_member_id = @new_member_id
And now it seems its working, but when I try to acces the variables, using:
.ActiveConnection = cn.ConnectionString
.CommandText = "sp_transfer_members"
.CommandType = adCmdStoredProc
'--Parametrer:
.Parameters.Append cnCmd.CreateParameter("@new_member_id", adInteger, adParamInput)
.Parameters("@new_member_id") = new_member_id
.Parameters.Append cnCmd.CreateParameter("@new_member_first_name", adWChar, adParamReturnValue, 20)
.Parameters.Append cnCmd.CreateParameter("@new_member_last_name", adWChar, adParamReturnValue, 20)
.Parameters.Append cnCmd.CreateParameter("@new_member_login", adWChar, adParamReturnValue, 15)
.Parameters.Append cnCmd.CreateParameter("@new_member_password", adWChar, adParamReturnValue, 15)
.Parameters.Append cnCmd.CreateParameter("@new_member_mail", adWChar, adParamReturnValue, 30)
I got NULL values... I am obviously doing some n00bish mistake, be kind enough to point it out, gently please 😀
-
Signature? I don't need no stinking signature!
-
Signature? I don't need no stinking signature!
May 14, 2002 at 4:41 am
Instead of adParamReturnValue you should be using
adParamOutput = Indicates that the parameter represents an output parameter.
Try this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 5:36 am
tried that, now I've got:
...
...("@new_member_mail", adWChar, adParamOutput, 20)
and then
... response.write cnCmd("@new_member_email")
and I get, nowt, no errors, no values passed...
-
Signature? I don't need no stinking signature!
Edited by - dna on 05/14/2002 05:37:31 AM
Edited by - dna on 05/14/2002 05:41:29 AM
-
Signature? I don't need no stinking signature!
May 14, 2002 at 6:33 am
Can yuo post the section of code back to the creation of your objects. (Note change sensitive informtion like usernames and passwords whe posting). I need to see your logic in full please.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 6:53 am
yeah sure, the full code atm(ripped heavily for debugging purposes) is:
strConn = "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog=PortalSQL; User Id=UID; Password=PWD"
...[bits n' pieces]...
dim cnCmd
set cnCmd = Server.CreateObject("ADODB.Command")
With cnCmd
.ActiveConnection = strConn
.CommandText = "sp_transfer_members"
.CommandType = adCmdStoredProc
'--Parametrer:
.Parameters.Append cnCmd.CreateParameter("@new_member_id", adInteger, adParamInput)
.Parameters("@new_member_id") = new_member_id
.Parameters.Append cnCmd.CreateParameter("@new_member_first_name", adWChar, adParamOutput, 20)
.Parameters.Append cnCmd.CreateParameter("@new_member_last_name", adWChar, adParamOutput, 20)
.Parameters.Append cnCmd.CreateParameter("@new_member_login", adWChar, adParamOutput, 15)
.Parameters.Append cnCmd.CreateParameter("@new_member_password", adWChar, adParamOutput, 15)
.Parameters.Append cnCmd.CreateParameter("@new_member_mail", adWChar, adParamOutput, 30)
End With
cnCmd.Execute
...[bits'n'pieces]....
' try to write out the variable to check if they are working...
response.write "new_member_first_name: " & cnCmd("@new_member_first_name") & "<br>"
response.write "new_member_last_name: " & cnCmd("@new_member_last_name") & "<br>"
response.write "new_member_login: " & cnCmd("@new_member_login") & "<br>"
response.write "new_member_password: " & cnCmd("@new_member_password") & "<br>"
response.write "new_member_mail: " & cnCmd("@new_member_mail") & "<br>"
-
Signature? I don't need no stinking signature!
-
Signature? I don't need no stinking signature!
May 14, 2002 at 9:48 am
Don't see anything right off and things got busy here so I cannot test right now. But try the following change to the last of your code and see what happens.
' try to write out the variable to check if they are working...
response.write "new_member_first_name: " & cnCmd(1).value & "<br>"
response.write "new_member_last_name: " &cnCmd(2).value & "<br>"
response.write "new_member_login: " & cnCmd(3).value & "<br>"
response.write "new_member_password: " & cnCmd(4).value & "<br>"
response.write "new_member_mail: " & cnCmd(5).value & "<br>"
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 4:36 am
you've earned my gratitude!
thx a lot for the help!
-
Signature? I don't need no stinking signature!
-
Signature? I don't need no stinking signature!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply