help on production for creating an sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help on production for creating an sp

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-05-08 : 21:25:37

Hello !

I just deleted a stored procedure in order to replace it with a corrected version (DotNetNuke).
Now I had the following problem : When I try to create the SP with the following :

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetUserRolesByUsername') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetUserRolesByUsername
GO

CREATE PROCEDURE dbo.GetUserRolesByUsername

@PortalId int,
@Username nvarchar(100),
@Rolename nvarchar(50)

AS

IF @UserName Is Null
BEGIN
SELECT R.*,
U.DisplayName As FullName,
UR.UserRoleID,
UR.UserID,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM {databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE R.PortalId = @PortalId
AND (R.Rolename = @Rolename or @RoleName is NULL)
END
ELSE
BEGIN
IF @RoleName Is NULL
BEGIN
SELECT R.*,
U.DisplayName As FullName,
UR.UserRoleID,
UR.UserID,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM {databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE R.PortalId = @PortalId
AND (U.Username = @Username or @Username is NULL)
END
ELSE
BEGIN
SELECT R.*,
U.DisplayName As FullName,
UR.UserRoleID,
UR.UserID,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM {databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE R.PortalId = @PortalId
AND (R.Rolename = @Rolename or @RoleName is NULL)
AND (U.Username = @Username or @Username is NULL)
END
END
GO

I get the following error :

"Syntax error, permission violation, or other nonspecific error"

Thanks a lot for any advice !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-09 : 00:54:16

Hello fabianus,

Did you try removing the {...} parts like below

GO

IF EXISTS (
SELECT * FROM sysobjects
WHERE
id = object_id(N'GetUserRolesByUsername')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE GetUserRolesByUsername
GO

Or you can even use the below which queries sys.procedures

GO

IF EXISTS (
SELECT * FROM sys.procedures WHERE name = N'GetUserRolesByUsername'
)
DROP PROCEDURE GetUserRolesByUsername
GO

Eralper
http://www.kodyaz.com

-------------
Eralper
http://www.kodyaz.com

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-05-09 : 08:00:52

No I didn't ! Thank you Eralper, this solved my problem !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources