sample sql code
satya - Saturday, February 18, 2012 10:55:21 AM
last updated time stamp code
update reports
set last_updated_on=GetDate()
where report_id={reportId}
satya - Fri May 04 2012 17:56:37 GMT-0400 (Eastern Daylight Time)
sample stored proc code
satya - Fri May 04 2012 18:02:33 GMT-0400 (Eastern Daylight Time)
sorting example
order by some_field_alias desc
satya - Tue Nov 06 2012 13:25:09 GMT-0500 (Eastern Standard Time)
How to call sql server stored proc syntax
How to call sql server stored proc syntax
satya - Tue Nov 06 2012 13:26:57 GMT-0500 (Eastern Standard Time)
Here is an example
use testdb;
exec tagFolder 3,5,'satya'
go
satya - 4/22/2014 3:27:33 PM
Here is another set of stored proc examples
satya - 4/22/2014 3:28:37 PM
How to use sp_help
1. Sp_help "system object name" // has to match an exact name
2. sp_helptext "system object name" // details
3. sp_stored_procedures "%objectname%" // takes wild cards
4. sp_columns "%name%" 5. sp_tables "%name%"
satya - 4/22/2014 3:29:34 PM
How can I execute a stored proc?
use the query analyzer tool
//Just type the following and execute
stored_proc_name arg1,arg2,arg3
satya - 4/24/2014 1:47:09 PM
It appears
varchar(10) means upto 10
varchar(256) upto 256
varchar(max) very large crap
satya - 4/24/2014 9:50:17 PM
ok, quick summary
bigint - 64 bits
int - 32 bits
smallint - 16 bits
tinyint - 8 bits
bit
decimal
float
real
date
time
datetime
char
varchar
binary
image
varbinary
satya - 4/25/2014 10:18:06 AM
t-sql single and double quotation marks
t-sql single and double quotation marks
satya - 4/25/2014 10:20:32 AM
In t-sql by default the quoted_identifier is on
this means only single quotes are allowed. you can use
set quoted_identifier off go
Not sure what the implications are
satya - 4/25/2014 11:08:18 AM
tsql: how can i execute sql files?
tsql: how can i execute sql files?
satya - 4/25/2014 11:12:55 AM
A key suggestion is first convert them to stored procs
A key suggestion is first convert them to stored procs
satya - 4/25/2014 11:26:20 AM
tsql Call command syntax
tsql Call command syntax
satya - 4/25/2014 11:37:32 AM
SQL Server Management studio, Running script files
SQL Server Management studio, Running script files
Search for: SQL Server Management studio, Running script files
This seem not possible. command line option may be the only way to run files in tandem one after the other.
satya - 4/26/2014 2:55:03 PM
Understand primary keys, identity, foreign keys here
satya - 5/15/2014 4:34:24 PM
update multi field example
update reports
set last_updated_on=GetDate()
,field2='ddd'
,field3 = 5 -- etc
where report_id={reportId}
satya - 2/5/2020, 7:58:24 PM
tsql cursor sample code
tsql cursor sample code
satya - 2/5/2020, 8:02:55 PM
A reasonable store of sample stored procedures
satya - 2/5/2020, 8:03:28 PM
How can I log from a stored procedure?
How can I log from a stored procedure?
satya - 2/5/2020, 8:08:25 PM
sys.messages sqlserver
sys.messages sqlserver
satya - 2/6/2020, 10:55:20 AM
ctrl-o opens a new file in SSMS
ctrl-o opens a new file in SSMS
satya - 2/6/2020, 11:18:19 AM
Here is an article on how to handle batch job errors in a stored procedure
Here is an article on how to handle batch job errors in a stored procedure
satya - 2/6/2020, 11:34:08 AM
Informational
When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block
satya - 2/6/2020, 11:35:27 AM
A discussion on SOF for riaseerror as a logging mechanism
satya - 2/6/2020, 11:35:49 AM
Informational
RAISERROR ('Some debugging info', 0, 1) WITH NOWAIT
satya - 5/25/2020, 12:05:04 PM
sort is called order by
sort is called order by
satya - 5/25/2020, 12:05:43 PM
double quotes and single quotes in tsql
double quotes and single quotes in tsql
satya - 5/25/2020, 12:08:52 PM
Single quotes
1. Generally good for all strings
2. Including inside a SQL statement
3. double quotes inside a SQL statement seem to throw it off
satya - 5/25/2020, 12:09:18 PM
double quotes
1. Seem to be used to host variables
2. lengthy sql statements
satya - 5/25/2020, 12:11:07 PM
An example
SELECT
--fd.f_forecast_provider_id as provider_id,
provider.f_forecast_provider_name as provider,
--fd.f_plant_id as plant_id,
plant.f_plant_name as plant,
fd.f_date_time
FROM t_vre_da_forecast_data fd,
t_vre_plants plant,
t_vre_forecast_providers provider
where fd.f_forecast_provider_id = provider.f_vre_forecast_provider_id
and fd.f_plant_id = plant.f_vre_plant_id
--Doesn't seem to like double quotes in the following line
and provider.f_forecast_provider_name = 'UL'
and plant.f_plant_name = 'sidrap'
--and fd.f_date_time > '$startString'
--and fd.f_date_time <= '$endString'
order by fd.f_date_time desc