1

This is my query inside my stored procedure 

DECLARE @branch uniqueidentifier

SELECT @branch = Branch_Id 
FROM Student_Master 
WHERE Student_Id = @studentid

SELECT
    CompanyMaster.unique_id_company, 
    Job_College.College_Id,
    Job_Branch.Branch_Id,
    CompanyMaster.name,
    Job_Master.Job_Post_Date, Job_Master.Job_Title,
    Job_Master.Salary, Job_Master.Job_Location,
    Department_Master.Department_Name 
FROM
    Job_Master
INNER JOIN
    Company_Master ON Job_Master.Company_ID = CompanyMaster.unique_id_company 
INNER JOIN
    Department_Master ON Department_Master.Department_Id = Job_Master.Department_Id 
INNER JOIN
    Job_College ON Job_Master.Job_ID = Job_College.Job_Id 
INNER JOIN
    Job_Branch ON Job_Branch.Job_College_ID = Job_College.Job_College_ID
WHERE
    Job_Branch.Branch_Id = @branch

Logically query is correct and is producing expected results if I pass value of @branch directly to it (instead of using SELECT query in the beginning) but with this method shown in code by populating value of @branch with select query at the beginning, this query results 0 results although @branch is receiving value from it.

Things I have tried

  1. Using SELECT query directly inside WHERE clause
  2. I have seen that PRINT @branch gives right output so it should work in WHERE clause but it is not working

Please help!! I am stuck for past many hours

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mvikhona
  • 101
  • 7
  • `SELECT Branch_Id from Student_Master where Student_Id=@studentid` Does this query returns single result – Pரதீப் Mar 05 '16 at 17:23
  • yes and if i directly paste it in WHERE clause,Complete query works – mvikhona Mar 05 '16 at 17:28
  • @MotoGP I think there is some CAST problem which I m not able to figure out – mvikhona Mar 05 '16 at 17:30
  • 1
    rewrite it this way: `SET @branch = (SELECT Branch_Id FROM Student_Master WHERE Student_Id = @studentid)` - it will become self-testing and avoiding ambiguous row problem. – Ivan Starostin Mar 05 '16 at 18:12
  • @IvanStarostin Sorry , It didn't help – mvikhona Mar 05 '16 at 18:25
  • Of course it did - now you may be ever sure that this piece of code returns exactly one row. Now pleas show DDL of `Student_Master` table. – Ivan Starostin Mar 05 '16 at 18:31
  • And what is the issue: your select returns nothing, fails with error, returns select for random branchid? Is this a full source of SP or you have other code assigning anything to `@branchid`? – Ivan Starostin Mar 05 '16 at 18:37
  • 'SET @branch = (SELECT Branch_Id FROM Student_Master WHERE Student_Id = @studentid)' This returns 1 row. BUT final query results nothing( 0 rows) . but if i use directly result of 1st query instead of '@branchid' ,it gives the result – mvikhona Mar 05 '16 at 18:44
  • 2
    @mvikhona always "nothing" or "unexpected result" i.e. output may vary? And DDL please (create table). For `Job_Branch` and `student_master`. – Ivan Starostin Mar 05 '16 at 18:50
  • @IvanStarostin I m very sorry for having your time. I have found my mistake(so silly that I cannot mention it here :x ) . Anyway , thank you for sparing your time for me. – mvikhona Mar 06 '16 at 07:32

0 Answers0