Can''t change the query into Stored Procedure

Question

Post reply

Can''t change the query into Stored Procedure

namarta

SSC Journeyman

Points: 82
More actions
September 27, 2006 at 5:51 am

#174592

Hi All,

I can't change the below query into stored procedure for long time. Please send me the Procedure using this example only. I am using SQL server7.0. thanks.

"SELECT DISTINCT O.ODID,R.RCID,R.RQTY,R.RDATE,NetQty FROM RECEIVE R ,ORDER12 O WHERE O.PARTID = (SELECT PARTID FROM PART WHERE [DESC]= '"+y+"') AND O.ODID = R.ODID AND R.NetQty >0 " ;

Viewing 14 posts - 1 through 13 (of 13 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

create procedure myproc

as

SELECT DISTINCT O.ODID,R.RCID,R.RQTY,R.RDATE,NetQty FROM RECEIVE R ,ORDER12 O WHERE O.PARTID = (SELECT PARTID FROM PART WHERE [DESC]= '+y+') AND O.ODID = R.ODID AND R.NetQty >0

GO

(i'm assuming you're looking for the string '+y+' in [desc])

please post your error message back from the original attempt to create the proc so i can ensure i've sent you the correct code

MVDBA

cje Mr or Mrs. 500 Points: 513 More actions · Answer 2

Try this:

create procedure dbo.myproc

as

select distinct o.odid, r.rcid, r.rqty, r.date, netQty

from receive r inner join order12 o on o.odid = r.odid

where o.partid = (selectpartid from part where [desc]= '+y+') and r.netQty > 0

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 3

I think you can't do it is because the PARTID returned from table PART has more than 1 record. you should use the keyword IN

CREATE PROCEDURE dbo.MyProcedure

AS

SELECT DISTINCT O.ODID, R.RCID, R.RQTY, R.RDATE, NetQty FROM RECEIVE R, ORDER12 O WHERE O.PARTID IN (SELECT PARTID FROM PART WHERE [DESC]= '"+y+"') AND O.ODID = R.ODID AND R.NetQty >0

GO

Urbis, an urban transformation company

namarta SSC Journeyman Points: 82 More actions · Answer 4

PARTID is Id so it can not be repeated .

can you please help me to know the parameter passing.

cje Mr or Mrs. 500 Points: 513 More actions · Answer 5

cje

Mr or Mrs. 500

Points: 513

September 29, 2006 at 11:12 am

#663406

what is the exact error message?

namarta SSC Journeyman Points: 82 More actions · Answer 6

CREATE PROCEDURE MyProcedure

@des nvarchar(50)

AS

SELECT DISTINCT O.ODID, R.RCID, R.RQTY, R.RDATE, NetQty FROM RECEIVE R, ORDER12 O WHERE O.PARTID = (SELECT PARTID FROM PART WHERE [DESC]= @des) AND O.ODID = R.ODID AND R.NetQty >0

GO

It work like this and can you please help me to know how to create procedure for more than 2 queries simultaneously Thanks

namarta SSC Journeyman Points: 82 More actions · Answer 7

CREATE PROCEDURE sp_myInsert1243

@odid12 int,

@rqty12 int,

@wqty12 int,

@rdate12 datetime,

@badge12 nvarchar(10),

@des12 nvarchar(50),

@stat12 nchar(1),

@remark12 nvarchar(10)

As

declare @rqty12 int

INSERT INTO RECEIVE (ODID,RQTY,WQTY,RDATE,BADGEID,STATUS,REMARKS) VALUES(@odid12,

@rqty12,'0',@rdate12,@badge12,@stat12,@remark12)

go

UPDATE PART SET OHQTY = OHQTY + @rqty123 , OOQTY= OOQTY - @rqty123 WHERE PARTID =

(SELECT PARTID FROM PART WHERE [DESC] = @des123)

go

UPDATE ORDER12 SET RQTY = RQTY + @rqty12 WHERE ODID = @odid12

GO

Error is:Server: Msg 137, Level 15, State 2, Line 1

Must declare the variable '@rqty123'.

Server: Msg 137, Level 15, State 1, Line 3

Must declare the variable '@rqty123'.

Server: Msg 137, Level 15, State 1, Line 4

Must declare the variable '@des123'.

Server: Msg 137, Level 15, State 2, Line 1

Must declare the variable '@rqty12'.

Even I have declared all of them..

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 8

I know that, but if there are more than 1 record with the same description, you will get all of them, example:

Part #1 description is "Radio"

Part #2 description is "FM Radio"

Part #3 description is "AM Radio"

if you say SELECT PartID FROM Parts WHERE Description LIKE '%Radio%' then you will get 3 records, with PartID 1, 2, and 3.

that '"+y+"' in the description, are you trying to pass a parameter?

Urbis, an urban transformation company

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 9

CREATE PROCEDURE sp_myInsert1243

@odid12 int,

@rqty12 int,

@wqty12 int,

@rdate12 datetime,

@badge12 nvarchar(10),

@des12 nvarchar(50),

@stat12 nchar(1),

@remark12 nvarchar(10)

As

INSERT INTO RECEIVE (ODID,RQTY,WQTY,RDATE,BADGEID,STATUS,REMARKS) VALUES(@odid12,@rqty12,'0',@rdate12,@badge12,@stat12,@remark12)

UPDATE PART SET OHQTY = OHQTY + @rqty123 , OOQTY= OOQTY - @rqty123 WHERE PARTID = (SELECT PARTID FROM PART WHERE [DESC] = @des123)

UPDATE ORDER12 SET RQTY = RQTY + @rqty12 WHERE ODID = @odid12

GO

You don't put "GO" until the end of your procedure. and you don't declare the parameter again in the procedure if your procedure already receiving it.

Urbis, an urban transformation company

namarta SSC Journeyman Points: 82 More actions · Answer 10

Thanks for ur help its working now.Actually I am new to this programming .

Can u please help me to know how to display error messages of these queries on front end screen(I am using netbeans at front end).

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 11

Use the RAISERROR function

Urbis, an urban transformation company

namarta SSC Journeyman Points: 82 More actions · Answer 12

Thanks but can u please send one of the example how to write this command in stored procedure.

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 13

You should try using the Books Online, it's a great resource to learn. They even have examples.

This is what I usually do:

--do something here

IF @@ERROR > 0

RAISERROR('Your error message goes here', 11, 1)

GO

Then you catch this error on your front end. I don't know anything about netbean, so can't help you with catching the errors.

Urbis, an urban transformation company