outputting from SP - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

outputting from SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-30 : 00:34:21

How can I return an output to my ASP from my SP. I havent done this before but this is what I have. Can somebody help me out here or point me to a good article, I can't seem to find any.

I have a couple lines commented out where I would like to end the SP and spit out some info to my ASP. They are when the emailaddress or the nameonline is already taken, I want to notify my ASP thats the case. Also, if everything runs ok I am going to add one more select statement that returns the GUID. Could somebody please give me some direction??

Thanks alot!

MIke123

CREATE PROCEDURE [insert_newUser_TESTING]
(
@NameOnline [varchar](15),
@EmailAddress [varchar](50),
@Response1 [varchar](250) = NULL,
@Response2 [varchar](250) = NULL,
@Response3 [varchar](250) = NULL,
@Response4 [varchar](250) = NULL,
@Response5 [varchar](250) = NULL,
@Response6 [varchar](250) = NULL
)
AS SET NOCOUNT ON

declare @userID as int

--CHECK TO SEE IF THE NAMEONLINE IS TAKEN
if isnumeric(right(@nameonline,1)) =1
IF Exists (SELECT userID FROM tblUserDetails WHERE nameOnline = substring(@nameOnline,1,len(@nameOnline)-1))
BEGIN
--SET @????
Return
END

else
IF Exists (SELECT userID FROM tblUserDetails WHERE nameOnline = @nameOnline)
BEGIN
--SET @????
Return
END

IF Exists (SELECT userID FROM tblUserDetails WHERE emailAddress = @emailAddress)
BEGIN
--SET@???
Return
END

--Start a transaction
BEGIN TRANSACTION
--First Action
INSERT INTO [tblUserDetails]
(
[NameOnline],
[EmailAddress],
)

VALUES
(
@NameOnline,
@EmailAddress,
)

SELECT @userID = @@IDENTITY

--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert New Member tblUserDetails',16,1)
RETURN(@@ERROR)
END

--Second Action

INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
(@userID,
1,
@response1)
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Question 1 into tblQuestions',16,1)
RETURN(@@ERROR)
END

--Third Action
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
('Identity',
2,
@response2)
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Question 2 into tblQuestions',16,1)
RETURN(@@ERROR)
END

--Fourth Action
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
('Identity',
3,
@response3)
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Question 3 into tblQuestions',16,1)
RETURN(@@ERROR)
END

--Fifth Action
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
('Identity',
4,
@response4)
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Question 4 into tblQuestions',16,1)
RETURN(@@ERROR)
END

--Sixth Action
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
('Identity',
5,
@response5)
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Question 5 into tblQuestions',16,1)
RETURN(@@ERROR)
END

--Seventh Action
INSERT INTO [tblQuestions]
( [userID],
[questionID],
[response])

VALUES
('Identity',
6,
@response6)
--Some checking
IF NOT @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not Insert Question 6 into tblQuestions',16,1)
RETURN(@@ERROR)
END

ELSE COMMIT TRANSACTION

GO

Nico
Starting Member

18 Posts

Posted - 2002-08-30 : 09:46:19

If you're talking about ASP.NET then:

CREATE PROCEDURE sp_whatever
AS
IF EXISTS( ... )
RETURN 0
ELSE
RETURN 1

when calling your SP:

SqlConnection con = new SqlConnection( System.Configuration.ConfigurationSettings.AppSettings["DSN"] );

SqlParameter prm = null;
SqlCommand cmd = new SqlCommand( "sp_whatever", con );
cmd.CommandType = CommandType.StoredProcedure;
prm = cmd.Parameters.Add( "RETURN_VALUE", SqlDbType.Int );
prm.Direction = ParameterDirection.ReturnValue;

con.Open();
cmd.ExecuteNonQuery();
result = Convert.ToInt32( cmd.Parameters["RETURN_VALUE"].Value );
con.Close();

If you're talking about basic ASP then:

try www.google.com
search for "stored procedure asp return"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-30 : 20:32:28

is it not possible to return a string value??

Thanks
mike123

Edited by - mike123 on 08/30/2002 20:32:45

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-30 : 20:55:04

sure, use an output parameter ... its limited to 8000 characters or 4000 characters if you are using unicode... i'm hoping you know how to use output parameters

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-31 : 19:36:23

No, I dont know how to use output parameters :)

That is actually what I was trying to get at in my first post, since Im going to need to return the GUID as an output, or return "EMAILTAKEN" or "NAMEONLINETAKEN" depending on what happens.

Any suggestions on how to take this on?

Thanks alot

mike123

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-09-01 : 08:50:48

SQL Server only returns int parameters ( Int32 in the .NET world ).

The closest equivalent is a char/varchar output parameter ( a ref string argument to a method in .NET ).

You define the procedure as follows:
create procedure OutputParmProc
@input1 int,
@input2 int,
@output varchar(255) out
as
...
{some DML to SET @output = {something}}

You then add an output parameter to a SqlCommand object as follows:
...
prm = cmd.Parameters.Add( "@output", SqlDbType.VarChar );
prm.Direction = ParameterDirection.Output;
prm.ExecuteNonQuery();

then the parameter value can be recovered with
prm.Value();

Jonathan
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources