satya - Fri Nov 16 2012 15:26:46 GMT-0500 (Eastern Standard Time)

v_taggedFolders

USE [test]
GO

IF  EXISTS (SELECT * FROM sys.views 
      WHERE object_id = OBJECT_ID(N'[dbo].[v_taggedFolders]'))
DROP VIEW [dbo].[v_taggedFolders]
GO

USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[v_taggedFolders]
AS

select  tags.f_relationship_id, 
      f.folder_name, 
      f.folder_id, 
      d.report_short_name as f_tagname,
      d.report_id as f_tagid
from folders f, t_relationships tags, reports d
where 1=1
and tags.f_target_domain = 1
and tags.f_target_id = f.folder_id
and tags.f_source_domain = 2
and tags.f_source_id = d.report_id
and d.report_id in (
   select fi.item_id
   from folders ssf, filed_items fi
   where 1=1
   and ssf.folder_name = 'tags'
   and ssf.folder_id = fi.folder_id );
GO

satya - 5/21/2014 2:10:16 PM

A view in a stored proc

/*
***********************************************************************
* sp_DropCrate_TestSectionsView.sql
* sp_DropCreate_SatTestSectionsView
* view: v_SatTestSectionsView
* Main table: t_SatTestSections

* Goal:
* **************
* dereference the t_SatTestSections
*
* Indexes:
* ***************
* n/a
* 
***********************************************************************
*/
USE [test]
GO

/*
****************************************************
* Drop the proc
****************************************************
*/
if exists (select * from sysobjects 
    where id = object_id(N'[dbo].[sp_DropCreate_SatTestSectionsView]') 
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DropCreate_SatTestSectionsView]
GO

/*
****************************************************
* Create the proc
****************************************************
*/
CREATE PROCEDURE dbo.sp_DropCreate_SatTestSectionsView
as begin

/*
****************************************************
* Drop the view
****************************************************
*/
IF  EXISTS (SELECT * FROM sys.views 
      WHERE object_id = OBJECT_ID(N'[dbo].[v_SatTestSectionsView]'))
DROP VIEW [dbo].[v_SatTestSectionsView]

/*
****************************************************
* Create the proc
****************************************************
*/
exec ('CREATE VIEW [dbo].[v_SatTestSectionsView]
AS
select testSection.*,
    s.f_section_name,
    st.f_sectiontype_name
from t_SatSections s, t_SatSectionTypes st, t_SatTestSections testSection
where 1=1
and testSection.f_section_id = s.f_satsection_id
and testsection.f_sectiontype_id = st.f_satsectiontype_id')


/*
****************************************************
* End of the proc
****************************************************
*/
end
go

satya - 5/21/2014 2:10:28 PM

Notice the dynamic sql through exec

Notice the dynamic sql through exec