Hi all,
Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)
My store procedure as follow:
CREATE PROC NewAcctType
(@.acctType VARCHAR(20))
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
PRINT 'The account type is already exist'
RETURN
END
BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
PRINT 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
Thanks for all your replies
The best thing to do would be to either create another parameter and set its type to output or return the statement as a select.
@.message varchar(100) output
set @.message = 'The account type is already exists'
or
select 'The account type is already exist' as message
Nick
As you stated:
@.message varchar(100) output
set @.message = 'The account type is already exists'
Do I put a return statement like "Return @.message"?
How about if there is no errror in the procedure, do I still need to return any value to the .aspx page?
Thanks.|||Hi Nick,
when I executed my store procedure in SQL 2000 server, I got an error said"Cannot use the OUTPUT option in a DECLARE statement"
But without the Declare keyword, I got an incorrect syntax error, so how can I solve this problem? Is that necessary to put the "output" keyword at the end of the declare varaible statement?
Thanks|||syntax is:
create procedure whateverName
@.message varchar(100) output
as
set @.message = ''
if @.@.ERROR
set @.message = 'your text here.'
If you have no error, the top set statement will allow a blank to be passed back.
Nick|||
Nick,
Here is my syntax:
CREATE PROC DeleteCust
(@.SSN VARCHAR(12), @.message VARCHAR(40) output)
AS
BEGIN
--checks if the SSN is already exist
IF NOT EXISTS (SELECT * FROM Customer WHERE SSN = @.SSN)
BEGIN
SET @.message = 'The SSN is not exist!'
RETURN
END
BEGIN TRANSACTION
DELETE FROM Customer WHERE SSN = @.SSN
--if there is an error on the delete, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Delete failure on Customer table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
I executed this proc as:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
Given that SSN is invalid, I suppose got the message 'The SSN is not exist!", however, I didn't get that message from the execution instead the system message showed "The command(s) completed successfully.", so anywhere I was wrong with the above SP or the execute statement?
Appreciated your reply
If your just looking for the value after a run in QA, add:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
select @.message
Nick
Thanks nick. I got the message when I run the Store Procedure in SQL server. However, how can I get the error message when I called the Store Procedure on my .aspx page?
I have these codes on my page: ('DeleteCust' is my store procedure name, 'SSN.Text' is the value from the input box)
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var myCommand : SqlDataAdapter = new SqlDataAdapter("DeleteCust", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.SSN", SqlDbType.VarChar, 12)).Value = SSN.Text;
Then, what should I put it here to catch the @.message value? The @.message is VARCHAR, and I have a Return keyword within my procedure, and the return value type is INT?
ping
Add (Code is in VB):
dim parm as new sqlParameter("@.message", sqlDBtype.varchar, 100)
parm.direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parm)
After you do your call to stored proc:
strMessage(Or whatever variable you are adding to) = myCommand.SelectCommand.Parameters(1).Value
Nick
Could u explain more detail for these statements coz I am new to doing asp, I want to know it more about the meaning of those codes.
parm.direction = ParameterDirection.Output
strMessage(what variable should I add it here? could u give me an example? are u talking about @.message?)
when do I use strMessage() ?
Many thanks.
|||
Here is a quick and dirty article on output parms.
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624
Nick
