Returning more than one value from a stored procedure? [modified]

Belfast Child

Hi, I'm quite new to stored procedures but have been fine with them up until now(OUTPUT params?? - If that's what I even need!:confused:) when i need to return more that one value to a dataset in my C# code ie. @actorName and @actorLogon. I thought I could do something like this but I get a syntax error on last line... (Msg 102, Level 15, State 1, Procedure sp_GetAssignedDetails, Line 60 Incorrect syntax near ','.) I've tried a few different ways but something tells me this is not the way to go. Here's the code - Any help appreciated. CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50), @actorName nVarChar(50) OUTPUT, @actorLogon nVarChar(5) OUTPUT AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SET @actorName= ( SELECT dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1') SET @actorLogon= ( SELECT dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1') END --Get ActorName and ActorLogon if division is South ELSE IF @division = 'South' BEGIN SET @actorName= ( SELECT dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1') SET @actorLogon= ( SELECT dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1') END --Get ActorName and ActorLogon if division is West ELSE IF @division = 'West' BEGIN SET @actorName= ( SELECT dbo.Actor.ActorName FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1') SET @actorLogon= ( SELECT dbo.Actor.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1') END RETURN @actorName, @actorLogon GO ps. What kind of value would this return if I didn't create any Output parameters and just went through the selec

Belfast Child

...or would this be better?? CREATE PROCEDURE sp_GetAssignedDetails @roleName nVarChar(50), @division nVarChar(50) AS IF @division = 'North' BEGIN --Get ActorName and ActorLogon if division is North SELECT dbo.Actor.ActorName, dbo.ActorRole.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.North = '1' END --Get ActorName and ActorLogon if division is South ELSE IF @division = 'South' BEGIN SELECT dbo.Actor.ActorName, dbo.ActorRole.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.South = '1' END --Get ActorName and ActorLogon if division is West ELSE IF @division = 'West' BEGIN SELECT dbo.Actor.ActorName, dbo.ActorRole.ActorLogon FROM dbo.Actor INNER JOIN dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id WHERE dbo.ActorRole.RoleName = @roleName and dbo.ActorRole.West = '1' END RETURN GO

Elina Blank

In order to return the value from stored procedure, it is enought to declare a parameter as an OUTPUT. in your C# code use ParameterDirection.Output when adding this parameter. You can retrieve value with the Value property of the created parameter. Usually, at the end of the execution of the stored procedure, it returns number of affected rows, so in case of a SELECT clause the return is 0 (but I am not sure, I understood your last question correctly :sigh: )

Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O

Elina Blank

You do not have to put a RETURN statement (at least when using SQL 2005)

Sincerely, Elina Life is great!!! Enjoy every moment of it! :-O

Scott Serl

The most efficient way to return values is with OUTPUT parameters. When the code returns, the parameter values will have the values in them; just check the cmd.Parameters("@actorName").Value and cmd.Parameters("@actorLogin").Value properties. If you did not have any output parameters and did the selects the way you had them, then the return value would have been 0 (zero), I think. If you just had regular SELECT statements without any OUTPUT parameters, you could have ruturned 2 result sets, each with 1 value in them. You would then use a DataReader to read the value from the first result set, then call .NextResult (or something like that, I can't remember), and then read the value out of the second result set. OUTPUT parameters are much more efficient. Notice that I changed your SET statements to just SELECT. The SET statements will work, but the SELECT way is more common among db developers.

CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50),
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
AS

IF @division = 'North'
BEGIN
--Get ActorName and ActorLogon if division is North
SELECT @ActorName = dbo.Actor.ActorName
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and dbo.ActorRole.North = '1'

SELECT @actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and dbo.ActorRole.North = '1'
END
--Get ActorName and ActorLogon if division is South
ELSE IF @division = 'South'
BEGIN
SELECT @actorName = dbo.Actor.ActorName
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and dbo.ActorRole.South = '1'

SELECT @actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and dbo.ActorRole.South = '1'
END
--Get ActorName and ActorLogon if division is West
ELSE IF @division = 'West'
BEGIN
SELECT @actorName = dbo.Actor.ActorName
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and dbo.ActorRole.West = '1'

SELECT @actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRol

Belfast Child

Cheers Scott, That was very informative. Yeah, unfortunately I'm not a DB developer. Just trying to think this stuff out for myself as we have no DB expertise in work. From the above code I'm trying to sequentially chose the above @actorName so that the workload is spread evenly amongst employees from the different divisions. At the moment it is picking the first one off the database table. Are there any handy stored proc functions that allows a sequential generator of names so that each @actorName in the database will get picked from their appropraite divisons?? Any help would be apprecaited. Kind Regards, BC