Retrieve GUID from SQL 2005 Stored Procedure

I have a stored procedure that returns GUID and BIT datatypes (see
below). I am using the VS 2005 TableAdapter.GetData to execute and
return the values. BUT, I can't pass a null value for the GUID output
parameter.
- Why is it required to pass a value for an output value?
- How do I retrieve the GUID value? If I remove the GUID field from
the SP, I can retrieve the BIT fields...so I am confident the SP works
fine.
Thanks for any ideas.
-KB
STORED PROCEDURE:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_UserLogin]
(
@.UserName nvarchar(50),
@.Password nvarchar(50),
@.Authorized bit output,
@.UserID uniqueidentifier output,
@.Security_Expeditor bit output,
@.Security_Tech bit output,
@.Security_WS bit output,
@.Security_RN bit output,
@.Security_MD bit output,
@.Security_SuperUser bit output,
@.Security_ReportAccess bit output,
@.Security_Admin bit output
)
AS
SET NOCOUNT ON;
BEGIN
Select @.Authorized = 'False'
Select @.Security_Expeditor = 'False';
Select @.Security_Tech = 'False';
Select @.Security_WS = 'False';
Select @.Security_RN = 'False';
Select @.Security_MD = 'False';
Select @.Security_SuperUser = 'False';
Select @.Security_ReportAccess = 'False';
Select @.Security_Admin = 'False';
END
BEGIN
select @.UserID = (select top 1 UserID from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and
IsApproved = 'True');
if @.@.rowcount > 0
set @.Authorized = 'True'
END
Begin
select * from vw_UserInfoRoles
where UserName = @.UserName and Password = @.Password and IsApproved =
'False'
end
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'expeditor' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Expeditor = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'tech' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Tech = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'ws' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_WS = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'rn' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_RN = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'md' and UserName = @.UserName and Password =
@.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_MD = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'superuser' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_SuperUser = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'reportaccess' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_ReportAccess = 'True'
END
BEGIN
select UserID from vw_UserInfoRoles
where LoweredRoleName = 'adminsecurity' and UserName = @.UserName and
Password = @.Password and IsApproved = 'True'
if @.@.rowcount > 0
set @.Security_Admin = 'True'
END(corsspost to unofficial group removed)
On 19 Apr 2006 10:09:21 -0700, kb wrote:

>I have a stored procedure that returns GUID and BIT datatypes (see
>below). I am using the VS 2005 TableAdapter.GetData to execute and
>return the values. BUT, I can't pass a null value for the GUID output
>parameter.
Hi kb,
How do you attempt to pass NULL? For an OUTPUT variable, you have to
pass in a variable, never a constant - but that variable can be NULL
(see code example below).

>- Why is it required to pass a value for an output value?
Not a value, but a variable - because an output variable can be changed
from the stored proc. You can't change a constant!

>- How do I retrieve the GUID value?
See this example:
CREATE PROC Test @.guid uniqueidentifier OUTPUT
AS
SET @.guid = NEWID()
go
DECLARE @.x uniqueidentifier
SET @.x = NULL
SELECT @.x
EXEC Test @.guid = @.x OUTPUT
SELECT @.x
go
DROP PROC Test
go
Hugo Kornelis, SQL Server MVP