June 18, 2012 at 8:07 am
Hello All,
I am newbie to sql server programming. I have a script that will add the extended properties and its values for stored procedure/function.I need to twaek this to get results as if the extended properties already exists for a certain stored procedure/function and if the user executes the below script it should add only the values for the extended properties as the properties are already defined.If they are not present for certain object then it should add the properties and the values.
Here is my script
ALTER PROCEDURE [dbo].[TestAddExtendedProperties]
(
@ObjType varchar(25), @ObjName varchar(75),@ObjOwner varchar(35) = NULL, @AppgrpValue varchar(35),@AuthorValue varchar(20),
@DescriptionValue varchar(70),@ReturnsValue varchar(30)
)
AS
BEGIN TRY
BEGIN TRAN
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)@level1name=@ObjName --SP/FN Name
EXEC sys.sp_addextendedproperty
@name='Author', -- Name of the new property
@value=@AuthorValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
EXEC sys.sp_addextendedproperty
@name='Description', -- Name of the new property
@value=@DescriptionValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
EXEC sys.sp_addextendedproperty
@name='Returns', -- Name of the new property
@value=@ReturnsValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
COMMIT TRAN
SELECT 'Extended properties added successfully to [' + @ObjOwner + '].' + @ObjNameas [Message]
END TRY
BEGIN CATCH
ROLLBACK TRAN
EXEC site.dbo.GetErrorInfo
END CATCH
I have tried this
IF NOT EXISTS(SELECT 1
FROM fn_listextendedproperty ('ApplicationGroup',
@AppgrpValue,
'SCHEMA',
@ObjOwner,
@ObjType,
NULL,
NULL) )
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
This script will help me when the object doesn't have the properties.But it will skip if the properties exists but no values.I want something if the properties exists, this procedure should help users to add the values.
The bottom line is the extended properties stored procedure first should check whether the object has extended properties with values or not.If they dont have the values defined but there are properties then the users should be able to add the values with this extended properties stored procedure.If the object (SP/Fn) doesn't have both extended properties and the values then the users should be able to add these both with above mentioned extended properties stored procedure.
Please help me with any suggestions.
Thank You
June 18, 2012 at 8:21 am
i thought you had this solved.
from your previous thread, you said the values were blank and the ned user is oing to update them if desired.
in that case, it's going to be a simple if not exists insert else update, right?
IF NOT EXISTS(SELECT 1
FROM fn_listextendedproperty ...,)
BEGIN
sp_addextendedproperty...
END
ELSE
BEGIN
sp_updateextendedproperty ...
END
Lowell
June 18, 2012 at 8:46 am
I have modified like this
IF NOT EXISTS(SELECT 1
FROM fn_listextendedproperty ('ApplicationGroup',
@AppgrpValue,
'SCHEMA',
@ObjOwner,
@ObjType,
NULL,
NULL) )
BEGIN
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
END
ELSE
BEGIN
EXEC sys.sp_updateextendedproperty
@name='ApplicationGroup',
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
end
I have tested this script on one of the stored procedure which has all the 4 properties but no values. But it is not working. It is saying property cannot be added because 'Application group' already exists. I dont know why it is not going to else part if the property exists.
I have tested like this
EXEC TestAddExtendedProperties 'Procedure', 'RefreshData','dbo','Database API','AGubba','Procedure used for Nightly refresh','None'
June 18, 2012 at 9:08 am
the EXISTS command you are using now is checking for the specific values as well, , i think what you want to do is to simply check if the Extended property 'ApplicationGroup' exists, right? or is it Application Group schema/ object?
SELECT *
FROM fn_listextendedproperty ('ApplicationGroup' , NULL, NULL, NULL, NULL, NULL, NULL);
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);
Lowell
June 18, 2012 at 9:21 am
No. The 'Application Group' is an extended property. I am checking first if the extended property 'Application group' exists or not. The value for this property is blank for some objects (stored proc/function). Whenever user wants to update this value, he/she can just execute this extended properties stored procedure by passing the parameters.
The object that I am testing right now is 'RefreshData' stored procedure. It has the extended property 'Application Group' and the value of this property is blank. I want to update this property's value to 'Database API' using this script
ALTER PROCEDURE [dbo].[TestAddExtendedProperties]
(
@ObjType varchar(25), @ObjName varchar(75),@ObjOwner varchar(35) = NULL, @AppgrpValue varchar(35)
)
AS
IF NOT EXISTS(SELECT 1
FROM fn_listextendedproperty ('ApplicationGroup',
@AppgrpValue,
'SCHEMA',
@ObjOwner,
@ObjType,
NULL,
NULL) )
BEGIN
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
END
ELSE
BEGIN
EXEC sys.sp_updateextendedproperty
@name='ApplicationGroup',
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
end
I have executed like this
EXEC TestAddExtendedProperties 'Procedure', 'RefreshData','dbo','Database API'
When I execute this I am getting error as the Property cannot be added. The property 'Application group' already exists.
Hope I didn't confuse you.
I am struggling with this since 2 days.
Thanks for your reply.
June 18, 2012 at 10:00 am
like i said, your IF EXISTS query was not quite right.
this works:
--testing on an object that exists in my database:
EXEC TestAddExtendedProperties 'Procedure', 'sp_find','dbo','Database API'
EXEC TestAddExtendedProperties 'Procedure', 'sp_find','dbo','Apples'
ALTER PROCEDURE [dbo].[TESTADDEXTENDEDPROPERTIES] (
@ObjType VARCHAR(25),@ObjName VARCHAR(75),@ObjOwner VARCHAR(35) = NULL,@AppgrpValue VARCHAR(35))
AS
IF NOT EXISTS(SELECT
*
FROM FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', @ObjOwner, @ObjType, @ObjName, NULL, NULL)X
WHERE name = 'ApplicationGroup')
BEGIN
EXEC sys.SP_ADDEXTENDEDPROPERTY
@name='ApplicationGroup',-- Name of the new property
@value=@AppgrpValue,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner,--Schema Name
@level1type=@ObjType,-- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
END
ELSE
BEGIN
EXEC sys.SP_UPDATEEXTENDEDPROPERTY
@name='ApplicationGroup',
@value=@AppgrpValue,-- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner,--Schema Name
@level1type=@ObjType,-- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
END
Lowell
June 18, 2012 at 12:35 pm
Thanks a ton :)...I really appreciate your help. I have been struggling with this since 2 days.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply