|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > CASTing
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Arpan De 2002-11-10, 6:23 pm |
| Consider the following stored procedure:
CREATE PROCEDURE spCommand
@fname varchar(50),
@lname varchar(50),
@email varchar(200),
@phone varchar(10)
AS
INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
RETURN @fname
RETURN @lname
When I am executing this stored procedure using the COMMAND object in ASP so as to INSERT the records & then retrieve & display the records in a web page, I am not getting any errors. But when I am executing it in the Query Analyzer using
EXEC spCommand 'ABCD','EFGH','ABCD@EFGH.COM','1234567'
I am being shown the following error:
Syntax error converting the varchar value 'ABCD' to a column of datatype int.
Is the error being thrown because only int datatype values can be returned from a stored procedure? If so, then why I am not getting any errors in ASP when the same procedure is being invoked?
Thanks,
Arpan
| |
| Dan Guzman 2002-11-10, 9:23 pm |
| It's a good practice to specify SET NOCOUNT ON in procedures executed
from ADO so that DONE_IN_PROC messages are suppressed. Otherwise,
you'll get the empty/closed recordsets returned to you application and
this is probably why you don't see the error in your ASP. The error
won't get thrown unless you execute the NextRecordSet method.
You're correct that you can return only an integer expression with
RETURN. The return value is often used to indicate success or failure.
Also, since RETURN immediately exits the proc, your second return will
never get executed.
You can return multiple parameters of other datatypes using OUTPUT
parameters. Example below. I'm not sure why you need to return the
parameter values back to the application in this case, though. It seems
to me the values will always be the same as the ones passed in.
CREATE PROCEDURE spCommand
@fname varchar(50) OUTPUT,
@lname varchar(50) OUTPUT,
@email varchar(200) OUTPUT,
@phone varchar(10) OUTPUT
AS
SET NOCOUNT ON
INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
IF @@ERROR = 0
RETURN 0
ELSE
RETURN 1
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Arpan De" <arpan_de5A@rediffmail.com> wrote in message
news:OtBKodRiCHA.2324@tkmsftngp08...
Consider the following stored procedure:
CREATE PROCEDURE spCommand
@fname varchar(50),
@lname varchar(50),
@email varchar(200),
@phone varchar(10)
AS
INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
RETURN @fname
RETURN @lname
When I am executing this stored procedure using the COMMAND object in
ASP so as to INSERT the records & then retrieve & display the records in
a web page, I am not getting any errors. But when I am executing it in
the Query Analyzer using
EXEC spCommand 'ABCD','EFGH','ABCD@EFGH.COM','1234567'
I am being shown the following error:
Syntax error converting the varchar value 'ABCD' to a column of datatype
int.
Is the error being thrown because only int datatype values can be
returned from a stored procedure? If so, then why I am not getting any
errors in ASP when the same procedure is being invoked?
Thanks,
Arpan
| |
| Arpan De 2002-11-11, 3:23 pm |
| Hi Dan,
Thanks for your response. What do you mean by DONE_IN_PROC messages? Yeah, you are right in saying that the values will always be the same as the ones that are being passed to the stored procedure. Actually, being a newbie in this field, I was just trying to return values from the stored procedure to the calling program (which is ASP in my case) so that they can be displayed in a web page.
Regards,
Arpan
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message news:e##GKsSiCHA.1688@tkmsftngp09...
: It's a good practice to specify SET NOCOUNT ON in procedures executed
: from ADO so that DONE_IN_PROC messages are suppressed. Otherwise,
: you'll get the empty/closed recordsets returned to you application and
: this is probably why you don't see the error in your ASP. The error
: won't get thrown unless you execute the NextRecordSet method.
:
: You're correct that you can return only an integer expression with
: RETURN. The return value is often used to indicate success or failure.
: Also, since RETURN immediately exits the proc, your second return will
: never get executed.
:
: You can return multiple parameters of other datatypes using OUTPUT
: parameters. Example below. I'm not sure why you need to return the
: parameter values back to the application in this case, though. It seems
: to me the values will always be the same as the ones passed in.
:
: CREATE PROCEDURE spCommand
: @fname varchar(50) OUTPUT,
: @lname varchar(50) OUTPUT,
: @email varchar(200) OUTPUT,
: @phone varchar(10) OUTPUT
: AS
: SET NOCOUNT ON
: INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
: IF @@ERROR = 0
: RETURN 0
: ELSE
: RETURN 1
: GO
:
: --
: Hope this helps.
:
: Dan Guzman
: SQL Server MVP
:
: -----------------------
: SQL FAQ links (courtesy Neil Pike):
:
: http://www.ntfaq.com/Articles/Index...epartmentID=800
: http://www.sqlserverfaq.com
: http://www.mssqlserver.com/faq
: -----------------------
:
: "Arpan De" <arpan_de5A@rediffmail.com> wrote in message
: news:OtBKodRiCHA.2324@tkmsftngp08...
: Consider the following stored procedure:
:
: CREATE PROCEDURE spCommand
: @fname varchar(50),
: @lname varchar(50),
: @email varchar(200),
: @phone varchar(10)
: AS
: INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
: RETURN @fname
: RETURN @lname
:
: When I am executing this stored procedure using the COMMAND object in
: ASP so as to INSERT the records & then retrieve & display the records in
: a web page, I am not getting any errors. But when I am executing it in
: the Query Analyzer using
:
: EXEC spCommand 'ABCD','EFGH','ABCD@EFGH.COM','1234567'
:
: I am being shown the following error:
:
: Syntax error converting the varchar value 'ABCD' to a column of datatype
: int.
:
: Is the error being thrown because only int datatype values can be
: returned from a stored procedure? If so, then why I am not getting any
: errors in ASP when the same procedure is being invoked?
:
: Thanks,
:
: Arpan
:
:
| |
| Dan Guzman 2002-11-11, 7:23 pm |
| DONE_IN_PROC messages are informational messages sent back to the client
as stored procedure statements complete. Here's an excerpt from the SQL
Server Books Online <tsqlref.chm::/ts_set-set_3ed0.htm>:
"SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the
client for each statement in a stored procedure. When using the
utilities provided with Microsoft® SQL ServerT to execute queries, the
results prevent "nn rows affected" from being displayed at the end
Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return
much actual data, this can provide a significant performance boost
because network traffic is greatly reduced."
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Arpan De" <arpan_de5A@rediffmail.com> wrote in message
news:u3QkZQciCHA.2460@tkmsftngp09...
Hi Dan,
Thanks for your response. What do you mean by DONE_IN_PROC messages?
Yeah, you are right in saying that the values will always be the same as
the ones that are being passed to the stored procedure. Actually, being
a newbie in this field, I was just trying to return values from the
stored procedure to the calling program (which is ASP in my case) so
that they can be displayed in a web page.
Regards,
Arpan
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:e##GKsSiCHA.1688@tkmsftngp09...
: It's a good practice to specify SET NOCOUNT ON in procedures executed
: from ADO so that DONE_IN_PROC messages are suppressed. Otherwise,
: you'll get the empty/closed recordsets returned to you application and
: this is probably why you don't see the error in your ASP. The error
: won't get thrown unless you execute the NextRecordSet method.
:
: You're correct that you can return only an integer expression with
: RETURN. The return value is often used to indicate success or
failure.
: Also, since RETURN immediately exits the proc, your second return will
: never get executed.
:
: You can return multiple parameters of other datatypes using OUTPUT
: parameters. Example below. I'm not sure why you need to return the
: parameter values back to the application in this case, though. It
seems
: to me the values will always be the same as the ones passed in.
:
: CREATE PROCEDURE spCommand
: @fname varchar(50) OUTPUT,
: @lname varchar(50) OUTPUT,
: @email varchar(200) OUTPUT,
: @phone varchar(10) OUTPUT
: AS
: SET NOCOUNT ON
: INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
: IF @@ERROR = 0
: RETURN 0
: ELSE
: RETURN 1
: GO
:
: --
: Hope this helps.
:
: Dan Guzman
: SQL Server MVP
:
: -----------------------
: SQL FAQ links (courtesy Neil Pike):
:
: http://www.ntfaq.com/Articles/Index...epartmentID=800
: http://www.sqlserverfaq.com
: http://www.mssqlserver.com/faq
: -----------------------
:
: "Arpan De" <arpan_de5A@rediffmail.com> wrote in message
: news:OtBKodRiCHA.2324@tkmsftngp08...
: Consider the following stored procedure:
:
: CREATE PROCEDURE spCommand
: @fname varchar(50),
: @lname varchar(50),
: @email varchar(200),
: @phone varchar(10)
: AS
: INSERT INTO tblCommand VALUES(@fname,@lname,@email,@p
hone)
: RETURN @fname
: RETURN @lname
:
: When I am executing this stored procedure using the COMMAND object in
: ASP so as to INSERT the records & then retrieve & display the records
in
: a web page, I am not getting any errors. But when I am executing it in
: the Query Analyzer using
:
: EXEC spCommand 'ABCD','EFGH','ABCD@EFGH.COM','1234567'
:
: I am being shown the following error:
:
: Syntax error converting the varchar value 'ABCD' to a column of
datatype
: int.
:
: Is the error being thrown because only int datatype values can be
: returned from a stored procedure? If so, then why I am not getting any
: errors in ASP when the same procedure is being invoked?
:
: Thanks,
:
: Arpan
:
:
|
|
|
|
|