How to use select query based upon the while and if condition?

[jacksantho]

Hi,
Please help me out to get this issue solve. Am don't know, how to get the output.

This is the way, i need. But i don't know.
(a)select distinct empid from emptable where year=2011 order by empid
(b)I needs to pass this empid with the while loop:

 while()
 {
      select count from empdetails where year=2011 and empid=?
      if(count==0)
      {
            'print statement
      }
 }

How can i write a query using Transact-SQL. Please suggest me.

[Tess James]

Get the output of query (a) to a temp table with an identity column as RowID

declare @EmpIds table
(
  RowID identity(1,1) int,
  empid varchar(20)
)
declare @count int

select @count = max(RowID) from @EmpIds 
while (@count > 0) 
begin
select @empid = empid from @EmpIds where RowID = @count
// do the operations here  
select @count = @count - 1
end

[jacksantho]

Hi, thanks for your query. I can't understand your query. Can you post it the query with some clear explanation.

Getting, the count of the total records and till looping to "Zero" count, the while loop executes. How can i pass the empid(emptable) to the inner table empdetails. Please can you explain it

[Tess James]

Suppose this query

select distinct empid from emptable where year=2011 order by empid

returns two records--> Emp1 and Emp2.

After the temp table population, @EmpIds will contain

RowID EmpID
----- ------
1 Emp1
2 Emp2

SELECT @count = max(RowID) FROM @EmpIds

The above query will return @count = 2

In the while loop,

SELECT @empid = empid FROM @EmpIds WHERE RowID = 2

The above query will return @empid = Emp2

Use this @empid in your query

select count from empdetails where year=2011 and empid= @empid

Hope this is what you are looking for.

[jacksantho]

Hi,

Thanks for your steps. I close to my output. How can i create the stored procedure with passing parameters. Thanks once more

[Tess James]

CREATE PROCEDURE ProcedureName
(
@Parameter DataType
)

AS
BEGIN
// Stored Procedure Contents
END

[jacksantho]

how to run the stored procedure is not running. Shows the error message on running stored procedure.

exec dbo.procname

[Tess James]

What is the error message?