exec (' string ') ??

[Drake]

Ok, I got a good one for you guys.. I am trying to do the below statement,
but within a stored procedure. Usually it isn't a problem using the exec
command, but since there is arithmetic, I am having issues.. Can anyone help
out..

This statement works, no problem...

UPDATE tblSchedPaper
SET IntPaperID= 42 ,
IntListRollSizeID= 95 ,
IntQty = round(floor( ( (cast( 40 as decimal(18,6))/cast( 50 as decimal(18,6)))*(s2.IntQty
* .0453592)) + 499 ),-3)
FROM tblCustProdSche INNER JOIN tblSchedPaper s2 ON s2.IntScheduleID = tblCustProdSche.IntScheduleID

WHERE IntPaperID IS NOT NULL


When I try to pass in parameters, that is when I am having issues:

CREATE Procedure usp_Migration_Rounding

@DestIntPaperID int,
@DestIntListID int,
@Destgrade decimal (18,6),
@Sourcegrade decimal (18,6),
@Factor decimal (18,6),
@where varchar(200)

AS

exec ('
UPDATE tblSchedPaper
SET IntPaperID= '+ @DestIntPaperID + ' ,
IntListRollSizeID= ' + @DestIntListID + ' ,
IntQty = round(floor( ( (cast( ' + @destgrade + ' as decimal(18,6))/cast(
' + @sourcegrade + ' as decimal(18,6)))*(s2.IntQty * ' + @factor + ')) +
499 ),-3)

FROM tblCustProdSche INNER JOIN tblSchedPaper s2 ON s2.IntScheduleID = tblCustProdSche.IntScheduleID

WHERE ' + @where )

RETURN @@ROWCOUNT


I get the following error:
Server: Msg 170, Level 15, State 1, Line 0
Line 3: Incorrect syntax near '*'.
Server: Msg 170, Level 15, State 1, Line 0
Line 5: Incorrect syntax near 'cast'.

[DaveSatz]

you should be CASTing all the numeric to char, i.e.:
SET IntPaperID= '+ @DestIntPaperID + ' ,
s/b
SET IntPaperID= '+ cast(@DestIntPaperID as varchar(30)) + ' ,

you may be to build a string of the sql to PRINT out to help debugging.

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------

"Drake" <dteran@nextiersolutions.com> wrote in message
news:3a4cd2ff$1@news.devx.com...
>
> Ok, I got a good one for you guys.. I am trying to do the below statement,
> but within a stored procedure. Usually it isn't a problem using the exec
> command, but since there is arithmetic, I am having issues.. Can anyone
help
> out..
>
> This statement works, no problem...
>
> UPDATE tblSchedPaper
> SET IntPaperID= 42 ,
> IntListRollSizeID= 95 ,
> IntQty = round(floor( ( (cast( 40 as decimal(18,6))/cast( 50 as
decimal(18,6)))*(s2.IntQty
> * .0453592)) + 499 ),-3)
> FROM tblCustProdSche INNER JOIN tblSchedPaper s2 ON s2.IntScheduleID =
tblCustProdSche.IntScheduleID
>
> WHERE IntPaperID IS NOT NULL
>
>
> When I try to pass in parameters, that is when I am having issues:
>
> CREATE Procedure usp_Migration_Rounding
>
> @DestIntPaperID int,
> @DestIntListID int,
> @Destgrade decimal (18,6),
> @Sourcegrade decimal (18,6),
> @Factor decimal (18,6),
> @where varchar(200)
>
> AS
>
> exec ('
> UPDATE tblSchedPaper
> SET IntPaperID= '+ @DestIntPaperID + ' ,
> IntListRollSizeID= ' + @DestIntListID + ' ,
> IntQty = round(floor( ( (cast( ' + @destgrade + ' as
decimal(18,6))/cast(
> ' + @sourcegrade + ' as decimal(18,6)))*(s2.IntQty * ' + @factor + ')) +
> 499 ),-3)
>
> FROM tblCustProdSche INNER JOIN tblSchedPaper s2 ON s2.IntScheduleID =
tblCustProdSche.IntScheduleID
>
> WHERE ' + @where )
>
> RETURN @@ROWCOUNT
>
>
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 0
> Line 3: Incorrect syntax near '*'.
> Server: Msg 170, Level 15, State 1, Line 0
> Line 5: Incorrect syntax near 'cast'.

[Drake]

DaveSatz,

Thanks for the second look. It sometimes takes a second set of eyes to see
what is going on. I thank you. I wound up creating a work around and bringing
in strings to set the parameters because of the limited time. I will definitely
look back to fixing the original problem, when time permits.. hehe

Here is the work around I created and thank you very much for the reply:


CREATE Procedure usp_Migration_Rounding

@DestIntPaperID varchar (25),
@DestIntListID varchar (25),
@Destgrade varchar (25),
@Sourcegrade varchar (25),
@Factor varchar (25),
@where varchar(2000),
@flag int

AS

IF @flag = 1
Begin
Declare @math varchar (150),
@set varchar (50),
@destP varchar (50),
@destL varchar (50)

SET @math = ' Set @math = (cast( ' + @destgrade + ' as decimal(18,6))/cast(
' + @sourcegrade + ' as decimal(18,6))) '
SET @set = ' Set @set = ' + @Factor + ' '
SET @destP = ' Set @destP = ' + @DestIntPaperID + ' '
SET @destL = ' Set @destL = ' + @DestIntListID + ' '

exec (' DECLARE @math decimal (18,6), @set decimal (18,6), @destP int,
@destL int '
+ @math + @set + @destP + @destL + '
Update tblSchedPaper
SET IntPaperID= @destP ,
IntListRollSizeID= @destL ,
IntQty = round(floor((@math * (s2.IntQty * @set)) + 499 ),-3)

FROM tblCustProdSche JOIN tblSchedPaper s2 ON s2.IntScheduleID = tblCustProdSche.IntScheduleID
where ' + @where )
End

ELSE
Begin

exec (' UPDATE tblSchedPaper
SET IntPaperID= ' + @DestIntPaperID + ' ,
IntListRollSizeID= ' + @DestIntListID + ' ,
IntQty= (convert(numeric, ' + @destgrade + ')/convert(numeric, ' + @sourcegrade
+ ' ))*s2.IntQty
FROM tblCustProdSche JOIN tblSchedPaper s2 ON s2.IntScheduleID = tblCustProdSche.IntScheduleID

where ' + @where )
End



RETURN @@ROWCOUNT



Thanks again,

Drake