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
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
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
satya - 5/14/2014 12:48:39 PM
tsql throw or raise exceptions sample code
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
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?
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