serious problem

Question

Post reply

serious problem

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
April 26, 2004 at 1:25 am

#107774

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]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 2

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]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 3

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 5

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]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 6

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 8

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]

David Burrows SSC Guru Points: 65144 More actions · Answer 9

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.

Robert A. Booey SSC-Addicted Points: 426 More actions · Answer 10

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'

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 11

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]