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

satya - Tue Nov 06 2012 13:02:44 GMT-0500 (Eastern Standard Time)

Here is another simpler example


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].[tagFolder]') 
   and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[tagFolder]
GO

/* Create the object */
CREATE PROCEDURE dbo.tagFolder
   @inFolderId int,
   @inDocumentId int,
   @ownerUserId varchar(50)
   
as begin


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

satya - Tue Nov 06 2012 19:05:15 GMT-0500 (Eastern Standard Time)

t-sql declaring variables

t-sql declaring variables

Search for: t-sql declaring variables

satya - Tue Nov 06 2012 19:07:32 GMT-0500 (Eastern Standard Time)

an example


USE AdventureWorks2012;
GO
DECLARE @find varchar(30); 
/* Also allowed: 
DECLARE @find varchar(30) = 'Man%'; 
*/
SET @find = 'Man%'; 
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p 
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

satya - Tue Nov 06 2012 19:22:50 GMT-0500 (Eastern Standard Time)

Important: column types should match with the local variable declarations when you compare them

Important: column types should match with the local variable declarations when you compare them

satya - 4/29/2014 8:58:45 PM

tsql how can I select into a variable

tsql how can I select into a variable

Search for: tsql how can I select into a variable

satya - 4/29/2014 8:58:53 PM

Here is an example


USE AdventureWorks2012 ; 
GO 
DECLARE @var1 nvarchar(30) 
SELECT @var1 = 'Generic Name' 
SELECT @var1 = (SELECT Name 
FROM Sales.Store 
WHERE CustomerID = 1000) 
SELECT @var1 AS 'Company Name' ;

satya - 4/30/2014 10:06:20 AM

Here is update syntax


update t_SatAnswers 
set f_option_number = @option_number
where f_SatAnswer_id = @previousAnswerId;

satya - 4/30/2014 10:07:34 AM

How you select into a variable


declare @previousAnswerId int;

select @previousAnswerId = (select f_SatAnswer_id
from t_SatAnswers
where f_user_id = @user_id
and f_question_id = @question_id
and f_ownerUserId = @ownerUserId )

satya - 4/30/2014 10:08:11 AM

Here is how you apply if/else


if (@previousAnswerId = Null)
begin
  print 'something'
end
else
begin
  print 'els'
end

satya - 4/30/2014 10:09:33 AM

A nested if/else


DECLARE @Number int;
SET @Number = 50;
IF @Number > 100
   PRINT 'The number is large.';
ELSE 
   BEGIN
      IF @Number < 10
      PRINT 'The number is small.';
   ELSE
      PRINT 'The number is medium.';
   END ;
GO

satya - 4/30/2014 10:31:41 AM

Sorry, use this NULL condition instead


if (@previousAnswerId iS NULL)
begin
  --something
end
else
begin
  --else
end

satya - 5/6/2014 1:28:16 PM

tsql stored procedure arguments specifying not null

tsql stored procedure arguments specifying not null

Search for: tsql stored procedure arguments specifying not null

satya - 5/6/2014 1:29:35 PM

Here is one suggestion


CREATE   proc dbo.CheckForNull @i int 
as
begin
  if @i is null 
    raiserror('The value for @i should not be null', 15, 1) -- with log 

end
GO

satya - 5/6/2014 1:30:51 PM

Cursory browsing seem to indicate you have to do it in the proc

Cursory browsing seem to indicate you have to do it in the proc

satya - 5/6/2014 1:45:17 PM

tsql example of raiserror

tsql example of raiserror

Search for: tsql example of raiserror

satya - 5/14/2014 12:48:39 PM

tsql throw or raise exceptions sample code

tsql throw or raise exceptions sample code

Search for: tsql throw or raise exceptions sample code

satya - 5/14/2014 12:56:05 PM

does tsql raise error cause jdbc exception

does tsql raise error cause jdbc exception

Search for: does tsql raise error cause jdbc exception

satya - 5/14/2014 12:59:50 PM

A nice example of tsql code

A nice example of tsql code

satya - 5/14/2014 1:05:49 PM

Here is an example


if exists (select user_id
    from users
    where user_id = @user_id)
begin
    raiseerror('DUPLICATE_USER: duplicate user',15,1);
end

satya - 5/14/2014 1:21:12 PM

raiserrore


message: any string message
severity: 0 through 18
state: 0 through 255

Ex: raiseerror('blah bla' 10, 1);

satya - 5/30/2017, 11:42:57 AM

What is SET QUOTED IDENTIFIER in SQL Server?

What is SET QUOTED IDENTIFIER in SQL Server?

Search for: What is SET QUOTED IDENTIFIER in SQL Server?

satya - 5/30/2017, 3:18:14 PM

How to run a stored proc in SQL Server


exec sp_named_proc
or
exec schema.sp_named_proc

satya - 2/6/2020, 10:56:17 AM

A simple template


/*
****************************************************
* sp_test_read
****************************************************
*/
use [db-name]
go


/*
****************************************************
* Drop the proc
****************************************************
*/
if object_id('dbo.sp_test_read') is not null 
     drop proc dbo.sp_test_read
go

CREATE PROCEDURE dbo.sp_test_read
    @name nvarchar = "satya",
    @param2 int = 0

AS
    SELECT * from some-table
GO