April 26, 2004 at 1:25 am
i need to provide reports to the management in a very short time
iam stuck with this stored procedure.
pls find a solution for this.
iam writing a stored procedure after a very long time, so iam encountring some silly errors.
now
declare @EchVPoin as varchar(5)
declare @noofVisits as varchar(5)
set @EchVpoin = 1/@noofVisits
now when i execute the stored procedure
iam printing everything
now when the value in @noofvisits=2
then @EchVpoin value should be 0.5
Right ??
but its showing Zero. why is that
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
April 26, 2004 at 1:41 am
Why
declare @EchVPoin as varchar(5)
declare @noofVisits as varchar(5)
and not
declare @EchVPoin as decimal(8,2)
declare @noofVisits as decimal(8,2)
???
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 26, 2004 at 2:17 am
its working .
but after geting a value iam updating a some rows in a particular table and the column is numeric, and iam updating using decimal
so it is throwing error.
in sql Server 7.00
i encountered same problem and the datatype was int in a table
i declared variables as varchar and then i converted them to int and updated the records, but its not happening in sql 2000
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
April 26, 2004 at 2:22 am
I TRIED THIS OPTION
WITH
FIRST I TRIED WITH
SET @EchVpoin = 1/2
THEN
seLECT @EchVpoin = 1/2
IT IS STILL SHWOING @EchVpoin =.00
??
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
April 26, 2004 at 2:32 am
Change it to something like
SET @EchVpoin = 1/2.
or
SET @EchVpoin = 1./2
to force SQL Server not to assume that you want an integer result
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 26, 2004 at 2:37 am
Great it works, but when i try to update it throws an error becoz datatype is numeric(18,2)
should i convert it to decimal ?
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
April 26, 2004 at 2:42 am
set @SQlViUp= 'Update RMCPL_DAILY_ACTIVITY set RMCPL_MD_POINTS='+@EchVpoin +' where
RMCPL_USER_CD=4'
my query in stored procedure is as above, i just copied and pasted i tried datatype converting it into decimal it doesnt work..
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
April 26, 2004 at 2:44 am
The datatypes DECIMAL and NUMERIC are the same in SQL Server.
Will ...SET RMCPL_MD_POINTS= +@EchVpoin not work?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 26, 2004 at 2:49 am
my exact code is like this.. i copying and pasting
set @SQlViUp= 'Update RMCPL_DAILY_ACTIVITY set RMCPL_MD_POINTS='+@EchVpoin +' where
RMCPL_USER_CD=4'
execute sp_executesql @SqlViup
and error message is
Server: Msg 8115, Level 16, State 6, Procedure Pd_GivePoints, Line 89
Arithmetic overflow error converting varchar to data type numeric.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
April 26, 2004 at 6:42 am
Difficult to give exact answer without procedure listing but this would work.
declare @EchVPoin as decimal(18,2)
declare @noofVisits as int
set @noofVisits = 2
set @EchVPoin = 1.0 / @noofVisits
Update RMCPL_DAILY_ACTIVITY
set RMCPL_MD_POINTS = @EchVpoin
where RMCPL_USER_CD=4
Far away is close at hand in the images of elsewhere.
Anon.
April 27, 2004 at 5:39 am
Your problem is in the string you're creating - it's trying to implicitly convert a numeric to nvarchar (I assume that's what your using for the variable @SQLViUp).
Try casting the numeric as a varchar before using sp_executesql.
set @SQlViUp= 'Update RMCPL_DAILY_ACTIVITY set RMCPL_MD_POINTS='+cast(@EchVpoin as varchar(10)) +' where
RMCPL_USER_CD=4'
April 27, 2004 at 5:59 am
i dont think it will work, still i will try
becoz for experimental purpose i tried dividing it directly by integer
1/2 like this.
and it was throwing error
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply