Select statement as stored procedure

Question

I have this query that I want to use as a stored procedure:

  SELECT ISNULL(P.firstname, s.firstname) AS Expr1,ISNULL(P.lastname,
  s.lastname) AS Expr2 FROM comment AS C LEFT OUTER JOIN professor AS P ON P.ID =   

  C.PID LEFT OUTER JOIN student AS s ON s.ID = C.SID

  WHERE (C.VID = @VID)

it should return values of Expr1 and Expr2

I've tried to declare inputs and outputs but with no luck

please help

EDIT :

  CREATE PROCEDURE [dbo].[GetDepartmentName]
  @ID int,
  @fName varchar(50) OUTPUT
   @lName varchar(50) OUTPUT
  AS


SELECT @fName=COALESCE(p.firstname, s.firstname)   @lName=COALESCE(p.lastname,     

   s.lastname)as 
  FROM comment c
  LEFT JOIN Professor p
    ON c.pid = p.id
  LEFT JOIN Student s
    ON c.sid = s.id
 WHERE c.vid = @ID

Answer 1

ALTER PROCEDURE [dbo].[GetDepartmentName]
  @ID    INT,
  @fName varchar(50) OUTPUT, -- added missing comma here
  @lName varchar(50) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT  
    @fName = COALESCE(p.firstname, s.firstname), -- added missing comma here
    @lName = COALESCE(p.lastname,  s.lastname)   -- removed strange "as" here 
  FROM dbo.comment AS c
  LEFT OUTER JOIN dbo.Professor AS p
    ON c.pid = p.id
  LEFT OUTER JOIN dbo.Student AS s
    ON c.sid = s.id
  WHERE c.vid = @ID;
END
GO

Answer 2

The short answer is the below stored procedure, modified from @Aaron Bertrand's CORRECT answer, will return the results you want in the way it seems like you need the data to me.

CREATE PROCEDURE [dbo].[GetDepartmentName]
  @ID    INT 
  AS
  BEGIN
  SET NOCOUNT ON;

  SELECT  
  COALESCE(p.firstname, s.firstname) as firstname,  
  COALESCE(p.lastname,  s.lastname) as lastname 
  FROM dbo.comment AS c
  LEFT OUTER JOIN dbo.Professor AS p
  ON c.pid = p.pid
  LEFT OUTER JOIN dbo.Student AS s
  ON c.sid = s.sid
  WHERE c.vid = @ID;
  END
  GO

There are several things going on here. I am going to walk you through exactly what I did in and effort to help you and explain how to move forward, as this is this 4th question you have opened related to this in the last couple of days.

OK - @Aaron Bertrand's stored procedure is good. He took the query you provided in your question and made it work and gave you a great answer to your question. However, I think there are some problems with your question. To get good answers you need to ask a good question.

I went to ONE of the other related questions you already asked and got your table structures and IN MANAGEMENT STUDIO: created the tables and inserted your provided data , compiled his sproc locally and got:

Msg 207, Level 16, State 1, Procedure GetDepartmentName, Line 14
Invalid column name 'id'.
Msg 207, Level 16, State 1, Procedure GetDepartmentName, Line 16
Invalid column name 'id'.

I know from the table structures from your OTHER question that I needed to change the joins:

LEFT OUTER JOIN dbo.Professor AS p
    ON c.pid = p.pid -- changed from c.pid = p.id, p.id is not a column
LEFT OUTER JOIN dbo.Student AS s
    ON c.sid = s.sid -- changed from c.pid = s.id, s.id is not a column

I copied the variable declaration and the query outside of the stored procedure create statement and made the changes I mentioned and set the variables and highlighted and ran JUST this. It worked. You can do this to test if there is simply a syntax or simple problem with your query when you are having problems with a stored proc.

I made the just tested changes to the JOINS within the stored procedure. I compiled it and successfully ran it.

Here's how to run a stored proc from Management Studio (NOTE I use EXEC in MS testing because its easy to type, but LEARN the difference between SP_EXECUTESQL and EXEC):

Execute a stored procedure:

[ [ EXEC [ UTE ] ] 
    { 
        [ @return_status = ] 
            { procedure_name [ ;number ] | @procedure_name_var 
    } 
    [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ] 
        [ ,...n ] 
[ WITH RECOMPILE ]

You said "I've tried to declare inputs and outputs but with no luck". I don't think you really want Output Variables - and it matters. I have a very strong feeling you want it returned as a Result Set. Do some research and LEARN about the different ways to return data from stored procedures. So anyway - here you go - Drop or Alter your old sproc and try this - it is quickly modified from @Aaron's and has no output variables - it is the same as the first one in the beginning of my answer.

CREATE PROCEDURE [dbo].[GetDepartmentName]
  @ID    INT 
  AS
  BEGIN
  SET NOCOUNT ON;

 SELECT  
 COALESCE(p.firstname, s.firstname) as firstname,  
 COALESCE(p.lastname,  s.lastname) as lastname 
 FROM dbo.comment AS c
 LEFT OUTER JOIN dbo.Professor AS p
    ON c.pid = p.pid
 LEFT OUTER JOIN dbo.Student AS s
    ON c.sid = s.sid
 WHERE c.vid = @ID;
END
GO 

Run it in SSMS using 'EXEC' (management studio):

EXEC [GetDepartmentName] 1

Returns:

firstname   lastname
--------------------
mark    abram
john    adam
sean    hoak

EDITED - DECIDED TO REMOVE SOME STUFF

Answer 3

Assuming your select works when you run it, i.e. that the joins are correct and this would return data, then the create procedure script below should do what you need.

CREATE PROCEDURE uspYourProcedureName 
  @VID INT
  AS 
  SET NOCOUNT ON;

  SELECT ISNULL(P.firstname, s.firstname) AS Expr1,
         ISNULL(P.lastname, s.lastname) AS Expr2 
  FROM   comment AS C 
         LEFT OUTER JOIN professor AS P ON P.ID = C.PID 
         LEFT OUTER JOIN student AS s ON s.ID = C.SID
  WHERE (C.VID = @VID)

If you just need to return the Expr1 and Expr2 values there is no need to define them as OUTPUT parameters, as in your edit. Anything that's returned by the select statement will be present in your results.

Try running the procedure from the web page but with SQL Profiler to see whether your @VID parameter is passed as you expect.