0

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.

Edited by Dani: Formatting fixed

2
Contributors
7
Replies
9
Views
5 Years
Discussion Span
Last Post by Tess James
0

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

Edited by Tess James: n/a

0

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

0

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.

Edited by Tess James: n/a

0

Hi,

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

0

CREATE PROCEDURE ProcedureName
(
@Parameter DataType
)

AS
BEGIN
// Stored Procedure Contents
END

Edited by Tess James: n/a

0

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

exec dbo.procname

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.