Contribute your favourite snippets here.

• 
  sph
  sp_help
  
  spt
  sp_helptext
  
  w2
  sp_who2+^{LEFT}{F5}
  
  Mark

  myuabov December 11, 2006 14:56

  - 0 +
• 
  I use an @Error variable I set after almost every statement.
  
  The following snippets keep me sane:
  
  sete:
  SET @Error;
  
  and
  ife:
  IF(@Error = 0)
  BEGIN
  
  END;
  
  some others I use frequently:
  be:
  BEGIN
  
  END;
  
  st4:
  SELECT TOP 450 *
  FROM dbo
  
  I use this is for when I want to test a query, without retrieving the thousands of rows it would return normally.
  
  Hope this is helpful to anyone.

  Turambar December 12, 2006 03:44

  - 0 +
• 
  Dear development team,
  
  First code snippet:
  I don't know if it is possible in SQL Prompt, but I use CodeSmith to generate a fetch loop for a table by selecting a table from a database.
  With tablename: Station, the format is:
  
  --
  -- Buffer Station
  --
  declare @ST_Column1 int
  declare @ST_Column2 varchar(5)
  declare @ST_Column3 varchar(50)
  
  declare curs_Station cursor for
  select
  Column1,
  Column2,
  Column3
  from Station
  
  open curs_Station
  
  fetch next from curs_Station into
  @ST_Column1,
  @ST_Column2,
  @ST_Column3
  
  @fetch_status = 0)
  begin
  --
  -- Processing of row starts here
  --
  
  fetch next from curs_Station into
  @ST_Column1,
  @ST_Column2,
  @ST_Column3
  end
  close curs_Station
  deallocate curs_Station
  
  It would be nice to generate the above code from SQL Prompt.
  
  Second codesnippet:
  The second thing I would like to be able to do from SQL Prompt, is beeing able to generate a column list for a selected Table, to work with in my coding.
  
  Looking forward to the final release,
  Hans

  hansmank December 13, 2006 02:58

  - 0 +
• 
  These are the snippets that i can´t live (or at least code SQL without):
  
  They work so that you type something and then use the snippet when standing at the end of the line in Query Analyzer or Management Studio.
  
  e: (execute)
  {BS}+{HOME}{F5}
  example: select * from Customers e[tab]
  
  ed: (execute and delete statement)
  {BS}+{HOME}{F5}{DEL}
  example select * from Customers ed[tab]
  
  i: (info sp_help)
  sp_help {HOME}+{END}{F5}{DEL}
  example: Customers i[tab]
  
  v: (view table)
  {BS 2}{HOME}select * from {HOME}+{END}{F5}{DEL}
  example: Customers v[tab]
  
  vt: (view top of table)
  {BS 2}{HOME}select top 5 * from {HOME}+{END}{F5}{DEL}
  example: Customers vt[tab]

  Traco December 13, 2006 03:58

  - 0 +
• 
  @ERROR are the most use commands

  dineshasanka December 13, 2006 06:12

  - 0 +
• 
  The code I find myself using a lot of the time is one to strip the time from a date-time field:
  
  CONVERT (DATETIME, CONVERT (VARCHAR(10), [FIELDNAME], 101))
  
  or to do the same for the current date
  
  CONVERT (DATETIME, CONVERT (VARCHAR(10), GETDATE(), 101))
  
  I also do a lot of report creation that looks for data in certain time spans (last full week/month, week/month to date) that would be nice to be able to do in SQL Prompt instead of having to cut and paste from a text file.
  
  WTD: BETWEEN DATEADD([day], (DATEPART(dw, GETDATE())-1) * - 1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))) AND CONVERT (DATETIME, CONVERT (VARCHAR(10), GETDATE(), 101))
  
  MTD: BETWEEN CONVERT (DATETIME, CONVERT (VARCHAR(2), MONTH(GETDATE())) + '/1/' + CONVERT (VARCHAR(4), YEAR(GETDATE()))) AND CONVERT (DATETIME, CONVERT (VARCHAR(10), GETDATE(), 101))
  
  LFM: BETWEEN DATEADD([MONTH], - 1, CONVERT (DATETIME, CONVERT (VARCHAR(2), MONTH(GETDATE())) + '/1/' + CONVERT (VARCHAR(4), YEAR(GETDATE())))) AND DATEADD([DAY], - 1, CONVERT (DATETIME, CONVERT (VARCHAR(2), MONTH(GETDATE())) + '/1/' + CONVERT (VARCHAR(4), YEAR(GETDATE()))))
  
  LFW: DATEADD(week, - 1, DATEADD([day], (DATEPART(dw, GETDATE()) - 1) * - 1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101)))) AND DATEADD([day], (DATEPART(dw, GETDATE())) * - 1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101)))

  jmoldover December 13, 2006 09:05

  - 0 +
• 
  Here's a snippet which creates some boilerplate cursor code:
  
  name: cur
  snippet:
  
  DECLARE /* VARIABLES */
  
  DECLARE <CURSOR> CURSOR FAST_FORWARD READ_ONLY FOR
  /* SELECT STATEMENT */
  
  OPEN <CURSOR>
  
  FETCH NEXT FROM <CURSOR> INTO /* VARIABLES */
  
  @FETCH_STATUS = 0
  BEGIN
  
  /* CURSOR LOGIC */
  
  FETCH NEXT FROM <CURSOR> INTO /* VARIABLES */
  
  END
  
  CLOSE <CURSOR>
  DEALLOCATE <CURSOR>
  
  In a perfect world, the Replace dialog would display with <CURSOR> as the text to replace.
  
  Regards

  muellerbill December 13, 2006 11:07

  - 0 +
• 
  A few very simple ones for commands I tend to use a fair bit...
  
  scf - select count(*) from
  
  sphf - exec sp_helpfile
  
  xpfd - exec master..xp_fixeddrives

  swordfish December 13, 2006 12:36

  - 0 +
• 
  SELECT INTO...

  /*
  ** SELECT ... INTO
  ** Creates a new table in DestDB and populates it
  ** with data from SrcDB..SrcTable
  */
  SELECT *
  INTO DestDB..NewTable
  FROM SrcDB..SrcTable
  

  INSERT SELECT

  /*
  ** INSERT ... SELECT
  ** Inserts records into an existing table.
  */
  INSERT INTO DestinationTable
  SELECT Field, Field2
  FROM OtherTable
  WHERE clause
  

  bdill December 14, 2006 02:36

  - 0 +
• 
  @Error block and the BEGIN/END TRAN's

  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS OFF
  GO
  IF EXISTS(SELECT * FROM sysobjects WHERE name = <PROC>)
  	DROP PROCEDURE <PROC>
  GO
  
  /*
  ** Desc: <DESC>
  ** Auth: <AUTH>
  ** Date: <DATE>
  */
  CREATE PROCEDURE <PROC>
  
  	 @UserID INT
  
  AS
  
  BEGIN TRAN
  
  SELECT 1
  
  	IF @@ERROR <> 0
  	BEGIN
  		ROLLBACK TRAN
  		RAISERROR('An error occurred while trying to <DO SOMETHING> in stored procedure <PROC>.', 16, 1)
  		RETURN
  	END
  
  COMMIT TRAN
  
  GO
  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS ON
  GO
  

  bdill December 14, 2006 02:46

  - 0 +
• 

  -- create a new database
  use master ;
  create database [] on (name = '', filename = 'd:\sql\.mdf') log on (name = '_log', filename = 'd:\sql\.ldf') ;
  go
  use [] ;
  exec sp_addlogin @loginame = '', @passwd = '', @defdb = '' ;
  go
  exec sp_changedbowner @loginame = '' ;
  go
  

  pbeazley December 15, 2006 08:20

  - 0 +
• 
  snippet name: neo
  description: find the most recent objects in the database
  defn:
  select top 21 name, type, crdate from sysobjects order by crdate desc

  jjderoo December 15, 2006 13:38

  - 0 +
• 
  This would be my most used snippet:
  
  SELECT TOP 100 * FROM
  
  With kind regards,
  
  Emmanuel
  
  PS: I also use some snippets whose utility would fall because of SQL Prompt, like
  
  SELECT * FROM MyTable WHERE 1 = 0

  nanchen December 16, 2006 18:32

  - 0 +
• 
  If it would by handy for me if it could insert this code block.
  
  begin transaction
  
  --commit transaction
  rollback transaction

  bhold December 18, 2006 18:20

  - 0 +
• 
  I frequenty have to run a command against all of the databases on a given server so I created the following snippet "alldb"
  
  DECLARE @DB sysname
  DECLARE @... NVARCHAR(4000)
  SET @DB=''
  
  WHILE @DB IS NOT NULL
  BEGIN
  SELECT @DB=MIN(NAME) FROM master..sysdatabases
  WHERE NAME NOT IN ('master','model','tempdb','msdb','northwind','pubs')
  AND NAME > @DB
  
  SELECT @SQL=N' ' + @DB + ' '
  PRINT @...
  EXEC sp_executesql @...
  END
  
  I then just edit the SELECT clause to do what I need it to.

  JTShyman January 03, 2007 13:46

  - 0 +
• 
  Thanks to all of you who've posted snippets. I'm going to close this thread now as we're aiming for code lock-off tomorrow so I'll need to be getting some of these into the default snippets.
  
  
  Thanks,
  Bart

  Bart Read January 04, 2007 05:36

  - 0 +

Add comment

Please sign in to leave a comment.