How to return Integer as a string in stored procedure?

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
-Kevinis it stored as a VARCHAR ?
what about storing as a nvarchar?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/remotedba.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.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|||Hi Kevin,
The return from the stored procedure is only ever int, if you want to pass a
value out of the stored procedure then you need to use a SELECT or OUTPUT
...
create proc test01
@.AcctNbr nvarchar(10) output
as
begin
set @.AcctNbr = '0123123123'
return( 0 )
end
Call using...
declare @.Acct nvarchar(10)
exec test01 @.AcctNbr = @.Acct output
print @.Acct
or
create proc test01
@.AcctNbr nvarchar(10) output
as
begin
select @.AcctNbr = '0123123123'
return( 0 )
end
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.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
> ---
> END
There are 3 ways to return values from a SQL Server stored procedure:
1. a Select statement that returns a resultset
--run this script in Query Analyzer (QA):
Create Procedure SelectValue
(@.input int)
AS
Select @.input + 5
go
exec SelectValue 10
go
drop procedure SelectValue
2. a Return parameter:
--run this script in QA:
create procedure ReturnValue
(@.input int)
AS
Return @.input + 5
go
declare @.returnvalue int
exec @.returnvalue = ReturnValue 10
select @.returnvalue
go
drop procedure ReturnValue
3. an Output Parameter:
--run this script in QA:
create procedure OutputValue
(@.input int output)
AS
SET @.input = @.input + 5
go
declare @.outputvalue int
SET @.outputvalue = 10
exec OutputValue @.outputvalue output
select @.outputvalue
go
drop procedure OutputValue
I do not recommend method 1 for returning a single value. A resultset is
expensive to build, in that it must contain metadata in addition to data. So
more network traffic is created, and the client app needs to expend more
resources in order to retrieve and expose the resultset to the calling
procedure.
Most developers use the Return parameter to return status codes instead of
data. This is for the sake of consistency: there is no technical reason not
to use RETURN to return data, except that RETURN can only be used to return
integers. If you need to return other datatypes, you need to use an output
parameter.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||It is stored as varchar.
-Kevin
"Jack Vamvas" wrote:

> is it stored as a VARCHAR ?
> what about storing as a nvarchar?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/remotedba.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in messag
e
> news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.microsoft.com...
> I
> dropped...this
> in
>
>|||Tony:
Thanks for the reply. Actually, here is the "complete" stored procedure. I
am doing a "lookup" of the acctnumber and storing it in another record (base
d
on the intput keys).
The issue is that I can read it, but it appears to implicity convert it to
an integer - even though it is declared as nvarchar. (I have tried both
varchar and nvarchar - both give the same result.) I don't need to return
the acctnumber from the proc, but I do need to update another record with th
e
string value of the column - not the integer conversion!
ALTER PROCEDURE [dbo].[MergeDowntimeRecords]
@.DownTimePtID int,
@.DownTimeVID int,
@.InterfacePtID int,
@.InterfaceVID int
AS
BEGIN
DECLARE @.DownTimePtMRNbr int
DECLARE @.InterfaceAcctNbr nvarchar(10)
DECLARE @.InterfacePtType nvarchar(5)
DECLARE @.InterfaceAdmDt DateTime
DECLARE @.InterfaceDschDt DateTime
DECLARE @.InterfaceHippa nvarchar(5)
DECLARE @.InterfaceLOS nvarchar(20)
---
SELECT
@.InterfaceAcctNbr = vAcctNbr,
@.InterfacePtType = vPtType,
@.InterfaceAdmDt = vAdmDtTm,
@.InterfaceDschDt = vDschDtTm,
@.InterfaceHippa = vHippa,
@.InterfaceLOS = vLOS
FROM tbl_Visit
WHERE VID = @.InterfaceVID
return @.InterfaceAcctNbr
---
UPDATE tbl_Visit
SET
vPtID = @.InterfacePtID,
vPtType = @.InterfacePtType,
vAdmDtTm = @.InterfaceAdmDt,
vDschDtTm = @.InterfaceDschDt,
vHippa = @.InterfaceHippa,
vLOS = @.InterfaceLOS
WHERE VID = @.DownTimeVID
---
DELETE FROM tbl_Visit WHERE VID = @.InterfaceVID
---
SELECT @.DownTimePtMRNbr = NULL
SELECT @.DownTimePtMRNbr = PtMrNbr FROM vw_All_Visits WHERE
VID=@.DownTimePtID
IF (@.DownTimePtMRNbr IS NULL)
BEGIN
-- If the MRNbr is Null, then it was manually entered, delete it -
-- Else the Demog record was looked up. ...so, don't delete it!!
DELETE FROM tbl_Demog WHERE PtID = @.DownTimePtID
END
ELSE
BEGIN
-- Since the patient was "looked up", reset the PtDownTime bit.
UPDATE tbl_Demog SET PtDownTime = 0
END
---
RETURN 0
END
-Kevin
"Tony Rogerson" wrote:

> Hi Kevin,
> The return from the stored procedure is only ever int, if you want to pass
a
> value out of the stored procedure then you need to use a SELECT or OUTPUT
> ...
> create proc test01
> @.AcctNbr nvarchar(10) output
> as
> begin
> set @.AcctNbr = '0123123123'
> return( 0 )
> end
> Call using...
> declare @.Acct nvarchar(10)
> exec test01 @.AcctNbr = @.Acct output
> print @.Acct
> or
> create proc test01
> @.AcctNbr nvarchar(10) output
> as
> begin
> select @.AcctNbr = '0123123123'
> return( 0 )
> end
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in messag
e
> news:E59CAC14-8538-4B21-A0A0-4ACE0525CE2C@.microsoft.com...
>
>|||Hi Kevin,
Yes, the problem is the line...

> return @.InterfaceAcctNbr
The above will stop the proc and the update will not happen anyway; if you
are debugging try this...
print @.InterfaceAcctNbr
return
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in message
news:76C046DD-06CC-41AF-9777-90290ACB8DDE@.microsoft.com...
> Tony:
> Thanks for the reply. Actually, here is the "complete" stored procedure.
> I
> am doing a "lookup" of the acctnumber and storing it in another record
> (based
> on the intput keys).
> The issue is that I can read it, but it appears to implicity convert it to
> an integer - even though it is declared as nvarchar. (I have tried both
> varchar and nvarchar - both give the same result.) I don't need to return
> the acctnumber from the proc, but I do need to update another record with
> the
> string value of the column - not the integer conversion!
>
>
> ALTER PROCEDURE [dbo].[MergeDowntimeRecords]
> @.DownTimePtID int,
> @.DownTimeVID int,
> @.InterfacePtID int,
> @.InterfaceVID int
> AS
> BEGIN
> DECLARE @.DownTimePtMRNbr int
> DECLARE @.InterfaceAcctNbr nvarchar(10)
> DECLARE @.InterfacePtType nvarchar(5)
> DECLARE @.InterfaceAdmDt DateTime
> DECLARE @.InterfaceDschDt DateTime
> DECLARE @.InterfaceHippa nvarchar(5)
> DECLARE @.InterfaceLOS nvarchar(20)
> ---
> SELECT
> @.InterfaceAcctNbr = vAcctNbr,
> @.InterfacePtType = vPtType,
> @.InterfaceAdmDt = vAdmDtTm,
> @.InterfaceDschDt = vDschDtTm,
> @.InterfaceHippa = vHippa,
> @.InterfaceLOS = vLOS
> FROM tbl_Visit
> WHERE VID = @.InterfaceVID
> return @.InterfaceAcctNbr
> ---
> UPDATE tbl_Visit
> SET
> vPtID = @.InterfacePtID,
> vPtType = @.InterfacePtType,
> vAdmDtTm = @.InterfaceAdmDt,
> vDschDtTm = @.InterfaceDschDt,
> vHippa = @.InterfaceHippa,
> vLOS = @.InterfaceLOS
> WHERE VID = @.DownTimeVID
> ---
> DELETE FROM tbl_Visit WHERE VID = @.InterfaceVID
> ---
> SELECT @.DownTimePtMRNbr = NULL
> SELECT @.DownTimePtMRNbr = PtMrNbr FROM vw_All_Visits WHERE
> VID=@.DownTimePtID
>
> IF (@.DownTimePtMRNbr IS NULL)
> BEGIN
> -- If the MRNbr is Null, then it was manually entered, delete it -
> -- Else the Demog record was looked up. ...so, don't delete it!!
> DELETE FROM tbl_Demog WHERE PtID = @.DownTimePtID
> END
> ELSE
> BEGIN
> -- Since the patient was "looked up", reset the PtDownTime bit.
> UPDATE tbl_Demog SET PtDownTime = 0
> END
> ---
> RETURN 0
>
> END
>
> --
> -Kevin
>
> "Tony Rogerson" wrote:
>|||Thanks - that was my problem.
--
-Kevin
"Bob Barrows [MVP]" wrote:

> Kevin Buchanan wrote:
> There are 3 ways to return values from a SQL Server stored procedure:
> 1. a Select statement that returns a resultset
> --run this script in Query Analyzer (QA):
> Create Procedure SelectValue
> (@.input int)
> AS
> Select @.input + 5
> go
> exec SelectValue 10
> go
> drop procedure SelectValue
> 2. a Return parameter:
> --run this script in QA:
> create procedure ReturnValue
> (@.input int)
> AS
> Return @.input + 5
> go
> declare @.returnvalue int
> exec @.returnvalue = ReturnValue 10
> select @.returnvalue
> go
> drop procedure ReturnValue
>
> 3. an Output Parameter:
> --run this script in QA:
> create procedure OutputValue
> (@.input int output)
> AS
> SET @.input = @.input + 5
> go
> declare @.outputvalue int
> SET @.outputvalue = 10
> exec OutputValue @.outputvalue output
> select @.outputvalue
> go
> drop procedure OutputValue
>
> I do not recommend method 1 for returning a single value. A resultset is
> expensive to build, in that it must contain metadata in addition to data.
So
> more network traffic is created, and the client app needs to expend more
> resources in order to retrieve and expose the resultset to the calling
> procedure.
>
> Most developers use the Return parameter to return status codes instead of
> data. This is for the sake of consistency: there is no technical reason no
t
> to use RETURN to return data, except that RETURN can only be used to retur
n
> integers. If you need to return other datatypes, you need to use an output
> parameter.
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||Yeah - I had the return to stop the proc and return the value (for debug).
Thanks.
-Kevin
"Tony Rogerson" wrote:

> Hi Kevin,
> Yes, the problem is the line...
>
> The above will stop the proc and the update will not happen anyway; if you
> are debugging try this...
> print @.InterfaceAcctNbr
> return
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Kevin Buchanan" <KevinBuchanan@.discussions.microsoft.com> wrote in messag
e
> news:76C046DD-06CC-41AF-9777-90290ACB8DDE@.microsoft.com...
>
>