How to return a integer as a string?

In a table, I have a 10 digit account number that is stored as a string
(Example Acct number: 0601600030). However, in the stored procedure, when I
try to store the account number in a local variable, it appears to be
implicity converted to an integer because the leading zero is dropped...this
stored procedure returns 601600030.
Here the stored procedure. How can I return the column exactly as it is in
the table?
ALTER PROCEDURE [dbo].[test001]
AS
BEGIN
DECLARE @.InterfaceAcctNbr nvarchar(10)
SELECT
@.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
FROM tbl_Visit
WHERE VID = 95799
return @.InterfaceAcctNbr
END
-Kevin
Stored procedure cannot return values of other type than integer - and this
value is used to be a status code not actual returning value. If you need
some programmability which returns scalar value then use UDF (user-defined
function) instead
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:D52F21AC-A1BF-460D-9619-55CDCA09B9CE@.microsoft.com...
> In a table, I have a 10 digit account number that is stored as a string
> (Example Acct number: 0601600030). However, in the stored procedure, when
> I
> try to store the account number in a local variable, it appears to be
> implicity converted to an integer because the leading zero is
> dropped...this
> stored procedure returns 601600030.
> Here the stored procedure. How can I return the column exactly as it is
> in
> the table?
>
> ALTER PROCEDURE [dbo].[test001]
> AS
> BEGIN
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> SELECT
> @.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
> FROM tbl_Visit
> WHERE VID = 95799
> return @.InterfaceAcctNbr
> END
>
> --
> -Kevin
|||Kevin Buchanan wrote:
> In a table, I have a 10 digit account number that is stored as a
> string (Example Acct number: 0601600030). However, in the stored
> procedure, when I try to store the account number in a local
> variable, it appears to be implicity converted to an integer because
> the leading zero is dropped...this stored procedure returns 601600030.
> Here the stored procedure. How can I return the column exactly as it
> is in the table?
>
> ALTER PROCEDURE [dbo].[test001]
> AS
> BEGIN
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> SELECT
> @.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
> FROM tbl_Visit
> WHERE VID = 95799
> return @.InterfaceAcctNbr
> ---
The db should return this unmodified. I suspect this is a client side
problem. Did you do a test with osql or QueryAnalyzer? Or which client
side tool did you use?
robert
|||Thanks. That was my problem!
-Kevin
"Alex Cieszinski" wrote:

> Stored procedure cannot return values of other type than integer - and this
> value is used to be a status code not actual returning value. If you need
> some programmability which returns scalar value then use UDF (user-defined
> function) instead
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
> news:D52F21AC-A1BF-460D-9619-55CDCA09B9CE@.microsoft.com...
>
>
|||Either you have to use a UDF or use an output parameter in your SP
instead.
ALTER PROCEDURE [dbo].[test001] @.InterfaceAcctNbr nVarChar(10) Output
AS
BEGIN
SELECT
@.InterfaceAcctNbr = cast(vAcctNbr as nvarchar(10))
FROM tbl_Visit
WHERE VID = 95799
Call the SP like this in T-SQL :
Declare @.InterfaceAcctNbr nVarChar(10)
Exec dbo.test001 @.InterfaceAcctNbr Output
Select @.InterfaceAcctNbr
If you use ADO in your Appl. you can declare an output parameter in your
SQL
command.
Regards .)
Bobby henningsen
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23e5rTgrGGHA.648@.TK2MSFTNGP14.phx.gbl...
> Kevin Buchanan wrote:
> The db should return this unmodified. I suspect this is a client side
> problem. Did you do a test with osql or QueryAnalyzer? Or which client
> side tool did you use?
> robert
>
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at f? 968 spam-mails.
Betalende brugere f?r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk