Selecting from different table base on parameter

[ndp]

Hi,
I am new to sql(sql server 2000). I am writing a stored procedure which has two parameters
1)@Dist as int
2)@QC as bit.
I want to select from a perticular table based on the parameter value.
while I am using this statement..
if @QC = 0
insert into #Students select * from Students
else
insert into #Students select * from Students_QC

It gives me following error...

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@QC'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'else'.
Server: Msg 137, Level 15, State 1, Line 61
Must declare the variable '@Dist'.

I can not determine what is wrong. Can anybody suggest a correct way to do this?

Thanks in advance,
ndp

 

[vidru]

Can you post the procedure? To declare parameters, the proc should look something like this:

CREATE PROC myProc
  @Dist INT,
  @QC INT
AS
if @QC = 0
    insert into #Students select *  from Students
else 
    insert into #Students select * from Students_QC
....
....
....

-dave

 

[ndp]

Thanks Dave for responding so quickly! I am really stuck on this.

Here is the store procedure...

CREATE PROCEDURE dbo.CycleI_ISR_test
@Dist as int,
@QC as bit
AS

CREATE TABLE #Students (
...
)

if @QC = 0
insert into #Students select * from Students
else
insert into #Students select * from Students_QC

Select Student_ID, LName, FName...
from #Students
inner join OtherTables.....

Do I need to declare the variables again? I just want to use different table based on parameter value!

 

[kkielak]

I did somethig like this

> create procedure ttt
> @a int,
> @b bit
>as
>begin
>
> create table #t (
> idd int
> )
>
> if @b=1
> print "test1"
> else
> print "test2"
>end


and it works like this

> exec ttt 1,1
gives test1
> exec ttt 1,2
gives test2

I think you missed begin ... end in the procedure

Chris

 

[ndp]

I figured out the problem!
There was a little 'Go' statement after Create table statement which was cuasing the problem. I removed that and it works fine!

Thank you very much for the help! Learned something new about sql.

ndp

 

[vidru]

I was just about to ask you if there was a 'Go' in there somewhere.

-dave