6-Feb-20 (Created: 6-Feb-20) | More in 'CS-SQLServer'

SampleStoredProc1


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

/* Create the object */
CREATE PROCEDURE dbo.SampleProc1
	@inArg1 varchar(20)
as 
/**
 * Created by: Satya Komatineni
 * Created on: Sep 26th, 2003
 * Last updated on: Sep 26th, 2003
 * Last updated by: Satya Komatineni
 * Read only (Query only does not update): YES
 *
 * Module: (Module Name)
 * 
 * SampleProc1
 *	Description of proc1
 *
 * Logic
 *	1. (point1)
 *	2. (point2)
 * 
 * A partial list of where this proc is used
 * *********************************************
 * 1. (application 1)
 * 2. (application 2)
 *
 * Sample data
 * **************
 *
 * This proc demonstrates
 * **************************
 * 1. left Outer joins in SQLServer
 * 2. left join by default is a left outer join
 * 3. You can use a sub-select as a column value
 * 4. You can use top 1 to picke the top row for case 3
 * 5. You can use a function to get the value for a column
 * 6. For 4 and 5, you can use the values of other columns as inputs
 * 7. Use of inner selects for outer joining multiple tables
 * 8. Demonstrates how to pass input arguments and use them
 */
begin
select  
-- Some set of fields, these are used in order by
	t1.some-col		as colAlias1,
	t1.some-col2 		as colAlias2,
--Data Set
	t2.some-col as colAlias3,
--Using selects as part of a value
	(select top 1 t7.column1 from table7 t7 where t7.prmiary_key = t1.primary_key) 
		as colAlias4
--Using a function to get the values							
	dbo.fn_some_function_name(t2.some_col_name
			,t3.some_col_name)				
		as colAlias5

--Unknown fields as NULLs
	NULL				as colAlias6,
	NULL				as colAlias7,
--Allows to add fields and delete fields, for testing purposes
	'last' as last
from 
	table1 t1
	--The dependent table table12 can optionally exist
	left outer join table12 t12 on t12.col_type_id=1
		and t12.primary_key_id = t1.primary_key_id

	--Another example of optional entries
	--left join is same as left outer join
	left join table13 t13 on t13.col_type_id=1
		and t13.primary_key_id = t1.primary_key_id

	--tabble14 entries have to exist
	join table14 t14 on t14.col_type_id=1
		and t14.primary_key_id = t1.primary_key_id

	--when you have to outer join with more than one table
	--Join the tables first into a compound table 
	left outer join (
		select 	t15.some-key as primary_key_id,
			t151.some-column,
		from table15 		t15
			,table151 	t151
		where 1=1
			and t15.join-key-id = t151.join-key-id
			--i2status check
			and t15.col1=(some-criteria)
			--yes setting
			and t151.col1=(some-criteria)
	) t16 on t16.primary_key_id=t1.primary_key_id

where 1=1
	--sent-to-i2status = no, Not sent to i2
	and t1.some-col=23
	and t12.some-col='22'
	and t1.some-col3 like @inArg1
	and t16.some-col > 20

order by colAlias1,colAlias2


end
GO
/**
 * Complete the process
 */
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO