Red Gate forums: SQL Prompt code snippets

RE: DECLARE CURSOR

datacentricity — Thu, 19 Jul 2012 08:02:49 GMT

An alternative view might be that if one uses cursors so rarely as to need a snippit to remeber all the correct syntax that's a good thing.

RE: Start date of the current month

EdCarden — Thu, 01 Mar 2012 17:49:37 GMT

Brian

If you're finding that you need to get the first of the month often then you could look at incorporating a Lookup table into your coide that consist of rows for each date that falls within a range that would cover the dates you'd expect to use for the next 5 yeras or so. The table would include the Date, the first day of that month, the last day of that month as well as other Date related data that is often obtained using one of the t-sql date functions and or string functions.

I work in a date heavy industry in which our transactional table has hundreads of millions of rows. More then %50 of the queries involved need to get the first day of whatever month/Year the date on the transaction falls within. Instead of using some extensive combination of string and date functions I join to a date lookup table on the date and return from it that First day of the month value. Its very handy to have.

RE: DECLARE CURSOR

EdCarden — Thu, 01 Mar 2012 17:43:34 GMT

DO you use cursors enough that you would benefit from having a Cursor code snippet in SQL Prompt?

Cursors are a necessary evil in SQL that more often then ot are heavily over used by traditional procedural/OOP developers. There are valid scenarios for using cursors but they are far and few. If your using cursors enough that having a predefined code snippet would be of benefit then you should look take some time to look at alternatives to cursor use.

BTW - I mean no offense with the above; just comenting on how you could help yourself improve things by avoiding excess cursor use.

Code to find all Stored Procedures in SQL Server

paulxavierx — Fri, 03 Jun 2011 18:19:07 GMT

Hi,
Here is a simple code to find all ACTIVE Stored Procedures in SQL Server

Code:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id

Create a New SP with Informational Headers & TRY/CATCH Block

AndrewJacksonZA — Wed, 03 Nov 2010 09:59:46 GMT

Snippet: csp
Description: Create a new stored procedure with informational headers and a TRY/CATCH block.
Code:

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: A. Jackson
-- Create date: 2010/<Create Month,,12>/<Create Day,,31>
-- Description: <Description,,>
--
-- Modifications:
-- Author:
-- Date: YYYY/MM/DD
-- Description:
-- :
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- WITH ENCRYPTION, RECOMPILE, EXECUTE AS CALLER|SELF|OWNER| 'user_name'
-- @parameter_name AS scalar_data_type ( = default_value ), ...
AS
BEGIN
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

$CURSOR$

END TRY
BEGIN CATCH
EXEC [dbo].[dba_Error_Handler] @isDynamicSQL = <Is this using Dynamic SQL?,bit,0>
END CATCH
END
GO

Use CTLR+SHIFT+M to bring up a dialog box that let's you enter <these, values> quickly and easily.

Please see my blog entry "A Way to Handle Raised Errors in Both Dynamic and Static SQL Code" for more information about [dba_Error_Handler].

Add A Column To A Table With ExtendedProperty

aultmike — Tue, 13 Jul 2010 20:41:22 GMT

I use this snippet when I want to add a column to a table and want to add a blurb about this columns purpose.

Getting into the habit of using the ExtendedProperty also allows me to use SQLDoc to create documentation. No brainer!

USE <DatabaseName>

BEGIN TRANSACTION
GO
ALTER TABLE dbo.<TableName> ADD <ColumnName> <DataType> NULL
GO
--The statement below allows you to add a description to the field
--you're adding so that you can use SQLDoc to generate documentation
EXEC sp_addextendedproperty N'MS_Description',
N'<Description of the purpose of the column>',
'SCHEMA', N'dbo', 'TABLE', N'<TableName>', 'COLUMN', '<ColumnName>'
GO
COMMIT
GO

FormatDate

Bill_Ross — Mon, 24 May 2010 14:39:05 GMT

Snippet name: fd
Description: Format a date as YYYY-MM-DD
Code:
CONVERT(CHAR(10), <inDate, DateTime, GETDATE()>, 23) AS [Date]

Use Ctrl-Shift-M to populate the parameter.
Defaults to GetDate()

rownum

Bill_Ross — Thu, 13 May 2010 13:16:55 GMT

I love the Over Partition:

row_number() OVER ( Partition BY <foreignkey, varchar> order by <foreignkey,varchar> ) AS [Seq]

Ctrl-Shit-M to populate

RE: My 5 cents tip (you might know it already, but just in case)

AndrewJacksonZA — Wed, 05 May 2010 11:19:41 GMT

Thanks!!!

RE: DECLARE CURSOR

Giggles220 — Mon, 19 Apr 2010 15:43:21 GMT

Thanks, no matter how many times I need to write cursor code, I always cut and paste an example to make sure I get it all. This snippet will be very helpful.

DECLARE CURSOR

aorozco — Mon, 19 Apr 2010 14:54:34 GMT

Hi, I was looking for a DECLARE CURSOR snippet but I couldn't find one, so I decided to create this basic one.

DECLARE $CURSOR$--@Variables

DECLARE /*Cursor name*/ CURSOR
FOR /*Select Statement*/
OPEN /*Cursor Name*/
FETCH NEXT FROM /*Cursor Name*/
INTO --@Variables

WHILE @@FETCH_STATUS = 0
BEGIN

/*SQL Statements*/

FETCH NEXT FROM /*Cursor Name*/
INTO --@Variables
END
CLOSE /*Cursor Name*/
DEALLOCATE /*Cursor Name*/

I hope it helps!

RE: Extended TRY-CATCH block

aultmike — Thu, 25 Feb 2010 18:57:15 GMT

Nice! Thx for sharing!

Extended TRY-CATCH block

ENedelko — Thu, 25 Feb 2010 16:01:12 GMT

Hi,

let me share this SQL block, which seems to be useful for proper error handling in stored procedures...

UPDATED: I found a defect in the original error handler (message was limited to 300 chars). Below is the refined version:

Code:

BEGIN TRY
BEGIN TRAN usp_procXXX
$CURSOR$--
COMMIT TRAN usp_procXXX
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage varchar(max)
, @ErrorSeverity int
, @ErrorState int
SELECT @ErrorMessage =
'Error '
+ CAST( ERROR_NUMBER() as varchar )
+ ' "' + ERROR_MESSAGE() + '" '
+ 'raised in ' + ISNULL( ERROR_PROCEDURE(), 'raw SQL' )
+ ' line ' + CAST( ERROR_LINE() as varchar )
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
IF XACT_STATE() <> 0
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION usp_procXXX;
END
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )
END CATCH

RE: My 5 cents tip (you might know it already, but just in case)

aultmike — Tue, 19 Jan 2010 15:35:27 GMT

Awesome tip!

My 5 cents tip (you might know it already, but just in case)

ivanrdgz — Sat, 11 Jul 2009 04:26:04 GMT

I like to combine the SQL Prompt code snippets with the SQL Server Management Studio templates. I always liked the template (kind of the same idea of snippets) but SSMS lacks the ability of "calling" those templates right from your query window. I mean, you must go to the Template Collection, find the needed template and from there double click on it or drag and drop.

So, now what I do? I just create snippets using the template format. For example:

USE <Database, varchar,>
go

select * from <Table, varchar,>
go

Once I call it, I press CTRL+SHIFT+M and the template parameter window will show up allowing me to enter the defined paramaters of the current template.

Hope this will help someone