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
-KevinStored 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 thi
s
> 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 messag
e
> 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) Outp
ut
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