T-SQL templates

CREATE VIEW.sql

CREATE VIEW authorizer.ElevatedUserSiteRoles
AS
/*
DESCRIPTION: Used to display all elevated users with their specific site roles.
	CALLED BY: SSMS.
*/
SELECT u.Id AS UserId, u.NetId, u.Added, u.SiteId, s.WiscWebId, s.Name AS SiteName, s.Url, s.DefaultRole, s.RequireWsb, u.RoleId, r.Name AS RoleName, r.[Description]
FROM authorizer.ElevatedUser u
	LEFT JOIN authorizer.WiscWebSite s ON u.SiteId = s.Id
	LEFT JOIN authorizer.AccessRole r ON u.RoleId = r.Id

Templates.sql

/* insert template */

CREATE PROCEDURE [dbo].[sbwsp_SurveyRespondentInsert]
	(@SurveyId int, @iMISMemberId varchar(10), @DateTime datetime, @Ip varchar(15), @ProductCode varchar(50))
AS
/*
DESCRIPTION: Used for marketplace product reviews, implemented on WisBar.
	Associates a question with a survey.
	Called by: ?

MODIFICATION HISTORY:
  07/08/2010	J.Skemp		Created stored procedure.
*/
INSERT INTO sbw_SurveyRespondent (
	SurveyId, iMISMemberId, [DateTime], Ip, [ProductCode]
)
SELECT @SurveyId, @iMISMemberId, @DateTime, @Ip, @ProductCode

SELECT scope_identity() Id


/* update template */

CREATE PROCEDURE [dbo].[sbwsp_SurveyRespondentUpdate]
	(@Id int, @SurveyId int, @iMISMemberId varchar(10), @DateTime datetime, @Ip varchar(15), @ProductCode varchar(50))
AS
/*
DESCRIPTION: Used for marketplace product reviews, implemented on WisBar.
	Updates an answer to a question.
	Called by: ?

MODIFICATION HISTORY:
  07/08/2010	J.Skemp		Created stored procedure.
*/
IF Exists(SELECT Id FROM sbw_SurveyRespondent WHERE Id = @Id)
	BEGIN
		UPDATE sbw_SurveyRespondent
		SET SurveyId = @SurveyId
			, iMISMemberId = @iMISMemberId
			, [DateTime] = @DateTime
			, Ip = @Ip
			, ProductCode = @ProductCode
		WHERE Id = @Id

		SELECT 1 [Status]
	END
ELSE
	BEGIN
		SELECT 0 [Status]
	END


/* delete template */

CREATE PROCEDURE [dbo].[sbwsp_SurveyDelete]
	(@Id int)
AS
/*
DESCRIPTION: Used for marketplace product reviews, implemented on WisBar.
	Deletes a survey and anything associated with it.
	Called by: ?

MODIFICATION HISTORY:
  07/08/2010	J.Skemp		Created stored procedure.
*/
IF Exists(SELECT Id FROM sbw_Survey WHERE Id = @Id)
	BEGIN
		-- Delete any answers to the survey.
		DELETE FROM sbw_SurveyAnswers
		WHERE SurveyRespondentId IN (
			SELECT Id
			FROM sbw_SurveyRespondent
			WHERE SurveyId = @Id
		)

		-- Delete the respondents who answered the survey.
		DELETE FROM sbw_SurveyRespondent
		WHERE SurveyId = @Id

		-- Delete the questions associated with the survey.
		DELETE FROM sbw_SurveyQuestion
		WHERE SurveyId = @Id

		-- Delete the products associated with the survey.
		DELETE FROM sbw_SurveyProduct
		WHERE SurveyId = @Id

		-- Delete the survey.
		DELETE FROM sbw_Survey
		WHERE Id = @Id

		SELECT 1 [Status]
	END
ELSE
	BEGIN
		SELECT 0 [Status]
	END