sample sql code


update reports    
set last_updated_on=GetDate()    
where report_id={reportId}

sample stored proc code


order by some_field_alias desc

How to call sql server stored proc syntax

Search for: How to call sql server stored proc syntax


use testdb;
exec tagFolder 3,5,'satya'
go

Here is another set of stored proc examples


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%"

use the query analyzer tool

//Just type the following and execute

stored_proc_name arg1,arg2,arg3

tsql data types

Search for: tsql data types

Here is an msdn link


varchar(10) means upto 10
varchar(256) upto 256
varchar(max) very large crap

bigint - 64 bits
int - 32 bits
smallint - 16 bits
tinyint - 8 bits
bit
decimal 
float
real
date
time
datetime
char
varchar
binary
image
varbinary

t-sql single and double quotation marks

Search for: t-sql single and double quotation marks

this means only single quotes are allowed. you can use

set quoted_identifier off
go

Not sure what the implications are

Read about quoted_identifer

tsql: how can i execute sql files?

Search for: tsql: how can i execute sql files?

A key suggestion is first convert them to stored procs

tsql Call command syntax

Search for: tsql Call command syntax

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.

Understand primary keys, identity, foreign keys here


update reports    
set last_updated_on=GetDate()
,field2='ddd'
,field3 = 5 -- etc
where report_id={reportId}

Control flow ref

Cursors documented here

tsql cursor sample code

Search for: tsql cursor sample code

cursor sample code

A reasonable store of sample stored procedures

How can I log from a stored procedure?

Search for: How can I log from a stored procedure?

Understanding raiseerror

sys.messages sqlserver

Search for: sys.messages sqlserver

sys.messages is documented here

You got to throw at some point

ctrl-o opens a new file in SSMS

Here is an article on how to handle batch job errors in a stored procedure

raiseerror docs

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block

A discussion on SOF for riaseerror as a logging mechanism


RAISERROR ('Some debugging info', 0, 1) WITH NOWAIT

Set command

sort is called order by

double quotes and single quotes in tsql

Search for: double quotes and single quotes in tsql

1. Generally good for all strings

2. Including inside a SQL statement

3. double quotes inside a SQL statement seem to throw it off

1. Seem to be used to host variables

2. lengthy sql statements


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