getting error in my stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting error in my stored procedure

Author

Topic

simi28
Starting Member

27 Posts

Posted - 2007-04-03 : 04:20:31

Hi All

I have created a stored procedure for transferring data from one table to another table
with a simple condition.
but i am getting error 'Incorrect syntax near the keyword 'select'.
may be it is a syntax error but i m not able to resolved it.as i have not worked in stored procedures before.

below is my code

///

alter procedure trans_data3
@nop int,
@nop1 int
as
set @nop=select noofparts from test_products
set @nop=select noofparts from test_products1
insert test_products1(prod_name,rate,qty)
select prod_name,rate,qty from test_products
--if (select @nop=noofparts from test_products1) > 0

if @nop > 0 and @nop1 > 0
insert test_products1_parts(noofparts,weight,height)
select noofparts,weight,height from test_products_parts

go

///

in my code I want if column 'noofparts'of table 'test_products'>0
then second insert command should execute.

Please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 04:27:32

[code]
alter procedure trans_data3
@nop int,
@nop1 int
as
BEGIN

set @nop = (select noofparts from test_products)
set @nop1 = (select noofparts from test_products1)

insert test_products1(prod_name, rate, qty)
select prod_name, rate, qty
from test_products

--if (select @nop=noofparts from test_products1) > 0

if @nop > 0 and @nop1 > 0
begin
insert test_products1_parts(noofparts, weight, height)
select noofparts, weight, height
from test_products_parts
end

END
[/code]

What are you trying to do for the part in red ? As you have basically assigning one of the records value to the variable. Or do you want number of records in the table ?

simi28
Starting Member

27 Posts

Posted - 2007-04-03 : 04:32:47

'noofparts' is a column in test_products table
I want if the value of noofparts >0 then
the second insert query should execute.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-03 : 04:33:35

like this?

If exists(select * from test_products where noofparts > 0)
begin
  insert test_products1_parts(noofparts,weight,height)
  select noofparts,weight,height from test_products_parts
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

simi28
Starting Member

27 Posts

Posted - 2007-04-03 : 04:47:39

its working now thanks a lot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-03 : 05:37:41

Also note that this assignment will lead to error if the query retunrs more than one value

set @nop = (select noofparts from test_products)
set @nop1 = (select noofparts from test_products1)

Safer method is

Select @nop = noofparts from test_products
select @nop1 = noofparts from test_products1

Although it maynot return the value you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources