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 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
>
>|||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:
>> 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
>
---
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