User Tools

Site Tools


notes:sql:samplesprocs

Examples of Stored Procedures

A sproc with a cursor:

CREATE PROC ReplacePlaceholders
    @EntityId UNIQUEIDENTIFIER,
    @Text NVARCHAR(2000) OUTPUT
AS
BEGIN
    SET NOCOUNT ON
 
	DECLARE @EntityName VARCHAR(50), @AttrName VARCHAR(50), @FriendlyName VARCHAR(50)
        DECLARE @DataType VARCHAR(20), @IsPlaceholderFound INT
        DECLARE @SQL VARCHAR(500), @AttrValue NVARCHAR(2000)
 
	CREATE TABLE #value (AttrValue NVARCHAR(2000))
 
        -- Traverse the list of all available placeholders
	DECLARE placeholders CURSOR FOR
		SELECT FriendlyName, EntityName, AttrName, DataType FROM Placeholders
	OPEN placeholders
	FETCH NEXT FROM placeholders INTO @FriendlyName, @EntityName, @AttrName, @DataType
 
	WHILE @@FETCH_STATUS=0
	BEGIN
                -- Try to find a given placeholder in the template
		SET @IsPlaceholderFound = CHARINDEX(@FriendlyName, @TextCondition)
 
		-- If the placeholder found ...
		IF @IsPlaceholderFound>0
		BEGIN
                        -- ... get the value using entity name and attr name
			-- We are using a naming scheme EntityNameId for primary keys.
			SET @SQL = 'INSERT INTO #value SELECT ' + @AttrName + 
                                   ' FROM ' + @EntityName + 
                                   ' WHERE ' + @EntityName + 'Id=''' + @EntityId + ''''
			EXEC(@SQL)
 
			-- Format the money
			IF @DataType='money'
                            SET @AttrValue = '$'+CONVERT(VARCHAR, CAST(@AttrValue AS MONEY), 1)
 
			-- Replace the placeholder in the template
			SET @Text = REPLACE(@Text, @FriendlyName, @AttrValue)
		END
 
		FETCH NEXT FROM placeholders INTO @FriendlyName, @EntityName, @AttrName, @DataType
	END
	CLOSE placeholders
	DEALLOCATE placeholders
 
	DROP TABLE #value
 
    SET NOCOUNT OFF
END
GO
notes/sql/samplesprocs.txt · Last modified: 2015/06/24 by admin