Code Snippets

I am looking for a sample code to write a tool or a 3rd party tool that will enable me to get the same functionality as visual studio 2005 code snippets in SSMS.

We do not expose the necessary interfaces to have 3rd parties plug-in their components into SQL Server Management Studio in SQL Server 2005. We are looking at this for a future release. We do have a Template Explorer, which may be of use in the meantime.

Using SQL Server Management Studio Templates
How to: Use Templates in SQL Server Management Studio

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Maybe you are looking for something like this (with sourcecode)

http://www.codeproject.com/useritems/enisey.asp

|||

Paul I was able to use the templates, its just that I was hoping for similar style of code snippets that Visual Studio has, where I could right click and have some of common tsql scripts available while using a query window. But for now the templates look better than nothing. I am not sure where to put in a suggestions, but that would be my first to have code snippets like visual studio 2005 uses in ssms.

Jurgen thanks for sending the like, but I am looking for more custom snippet storage not neccessarily code generation tools. But Thank you for the suggestion.

Thanks Troutbum!

|||

We have a customer portal where you can file suggestions. It's called Microsoft Connect. You can submit your suggestion here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Code Help Please

I did not write this stored procedure but have been asked to edit the code and display new data. The column name is H.BilletUOM. It only contains only three values. B71,B72, and B73. I was able to select the column in the stored procedure easily so the data is available for the report but the next step is where I am having difficulty. If the value is B71 then I want to display 28ft. B72 display 34ft. B73 display 40ft. Can someone please take a look at this code and try to help me figure this out. I highlighted the H.BilletUOM in red so you can quickly see where I am selecting this column. Any assistance is greatly appreciated. I am new to SQL and have not started classes yet. Thanks again.

CREATE PROCEDURE SP_Melt_HeatReport_HeatList
@.HeatNo varchar(50) = NULL,
@.Date varchar(50) = NULL
AS

UPDATE WebPageData
SET Hits = Hits + 1
WHERE SPName = 'SP_Melt_HeatReport_HeatList'

DECLARE @.Site varchar(100) SET @.Site = dbo.UDF_SystemParameter('Site')
DECLARE @.SiteName varchar(100) SET @.SiteName = dbo.UDF_SystemParameter('SiteName')

DECLARE @.MinHeatNo int SET @.MinHeatNo = CSCMelting.dbo.UDF_SystemParameter('MinClevelandHeat')
DECLARE @.MaxHeatNo int SET @.MaxHeatNo = CSCMelting.dbo.UDF_SystemParameter('MaxClevelandHeat')

DECLARE @.HeatsPerPage int SET @.HeatsPerPage = 40

IF @.Date IS NOT NULL
BEGIN
SELECT @.MinHeatNo = MIN(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT @.MaxHeatNo = MAX(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @.Date AND HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT @.HeatNo = @.MinHeatNo
SELECT @.HeatsPerPage = (@.MaxHeatNo - @.MinHeatNo) / 10 + 1
END

IF PATINDEX('%,%', @.HeatNo) > 0
SET @.HeatNo = SUBSTRING(@.HeatNo ,PATINDEX('%,%', @.HeatNo) + 1, 100)

DECLARE @.ColorRed varchar(7) SET @.ColorRed = '#FF0000'
DECLARE @.ColorRedDark varchar(7) SET @.ColorRedDark = '#AA0000'
DECLARE @.ColorBlue varchar(7) SET @.ColorBlue = '#0000FF'
DECLARE @.ColorGreen varchar(7) SET @.ColorGreen = '#008800'
DECLARE @.ColorYellow varchar(7) SET @.ColorYellow = '#999900'
DECLARE @.ColorWhite varchar(7) SET @.ColorWhite = '#FFFFFF'
DECLARE @.ColorBlack varchar(7) SET @.ColorBlack = '#000000'
DECLARE @.ColorGray varchar(7) SET @.ColorGray = '#eeeeee'

IF @.HeatNo IS NULL OR @.HeatNo = '' OR ISNUMERIC(@.HeatNo) = 0 OR @.HeatNo = '0'
SELECT @.HeatNo = MAX(HeatNo) - (@.HeatsPerPage * 10) + 10
FROM CSCMelting.dbo.MS_HeatLog
WHERE HeatNo BETWEEN @.MinHeatNo AND @.MaxHeatNo

SELECT Tag = 1, Parent = NULL,
[Title!1!Title1] = 'Charter Steel ' + @.SiteName + ' Melting',
[Title!1!Title2] = 'LEVEL II HEAT REPORTS'
FOR XML EXPLICIT

SELECT Tag = 1, Parent = NULL,
[PageVars!1!Site] = @.Site,
[PageVars!1!WebPageID] = 20500,
[PageVars!1!HeatNo] = @.HeatNo,
[PageVars!1!HeatNoNext] = @.HeatNo + (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoPrev] = @.HeatNo - (@.HeatsPerPage * 10) ,
[PageVars!1!HeatNoLast] = 0,
[PageVars!1!HomeLink] = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20500'
FOR XML EXPLICIT

SELECT HeatNo,
Job = JOB_NUMBER,
RowColor = CASE WHEN (Heatno /10) % 2 = 0 THEN @.ColorGray ELSE @.ColorWhite END,
Grade = CONVERT(varchar(20), GRADE + ' ' + CS_CMDESC),
Status = CASE Status WHEN 4 THEN
CASE WHEN ChemCert = 1 THEN 'Certified'
WHEN EXISTS(SELECT * FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo AND Location = '99') THEN 'Inspection'
WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN 'CCM'
ELSE 'Inspected'
END
WHEN 3 THEN 'CCM'
WHEN 2 THEN 'LRF'
WHEN 1 THEN 'EAF'
ELSE ''
END, --+ CASE WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN ' (PB)' ELSE '' END,
H.NonConforming,
PourBackTons = (SELECT MIN(PourBackAmt) / 2000 FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo),
Location = (SELECT MAX(Location) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntCounted = H.BilletsMade1 + H.BilletsMade2 + H.BilletsMade3 + H.BilletsMade4,
ScrapCount = (SELECT ScrapCount FROM CSCMelting.dbo.VW_Prod_ScrapBilletsByHeat Sc WHERE Sc.Heatno = H.HeatNo),
CntInv = (SELECT COUNT(*) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntRolled = (SELECT SUM(Quantity) FROM CSCRolling.dbo._Lot WHERE Heat = H.HeatNo),
CntSched = (SELECT SUM(IC_QUANTITY) FROM CSCRolling.dbo._Lot_Scheduled WHERE IC_LOT_NUMBER = H.HeatNo),

CCM.SEQ_COUNTER,
SeqHeat = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER AND CCM2.REPORT_COUNTER <= R.REPORT_COUNTER), --CCM.SEQ_HEAT_COUNTER,
SeqTotal = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER),

RptDate = CONVERT(varchar(10), RptDate, 120),
EndTapTime = H.EndTapTime,
H.BilletUOM,

ConsumptionsLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20501&HeatNo=' + CONVERT(varchar(20), HeatNo),
FCERepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 400),0),
FCERepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20100&HeatNo=' + CONVERT(varchar(20), HeatNo),
-- VADRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
-- VADRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

LRFRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
LRFRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

VODRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 800),0),
VODRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),

CCMRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 1100),0),
CCMRepCntLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20300&HeatNo=' + CONVERT(varchar(20), HeatNo),
RunoutOrderLink = 'http://mesweb/xml.aspx?Site=' + @.Site + '&WebPageID=20301&HeatNo=' + CONVERT(varchar(20), HeatNo)

INTO #Heats
FROM CSCMelting.dbo.MS_HeatLog H
LEFT JOIN CSCMelting.dbo.REPORTS R ON R.HEAT_ID = H.HeatNo AND R.AREA_ID = 1100
LEFT JOIN CSCMelting.dbo.REP_CCM CCM ON CCM.REPORT_COUNTER = R.REPORT_COUNTER
WHERE HeatNo >= @.HeatNo AND HeatNo < @.HeatNo + (@.HeatsPerPage * 10) AND HeatNo % 10 = 0

SELECT *,
EndTapTimeStr = CONVERT(varchar(25), EndTapTime, 100),
MissChemStr = CASE WHEN NonConforming = 1 THEN 'Y' ELSE '' END,
PourBackTonsStr = CASE WHEN PourBackTons = 0 THEN '' ELSE CONVERT(varchar(10), PourBackTons) + 'tn' END,
CntCountedStr = NULLIF(CntCounted, 0),
ScrapCountStr = NULLIF(ScrapCount, 0),
CntInvStr = NULLIF(CntInv, 0),
CntRolledStr = NULLIF(CntRolled, 0),
CntSchedStr = NULLIF(CntSched, 0),
HeatSeqStr = '' + char(CONVERT(int, SEQ_COUNTER) % 26 + 65) + ' - ' + CASE WHEN SeqTotal <= 1 THEN 'single' ELSE CONVERT(varchar(2), SeqHeat) + ' of ' + CONVERT(varchar(2), SeqTotal) END,
StatusColor = CASE
WHEN PourBackTons > 0 THEN @.ColorBlack
WHEN Status = 'EAF' THEN @.ColorBlue
WHEN Status = 'LRF' THEN @.ColorGreen
WHEN Status = 'CCM' THEN @.ColorYellow
WHEN Status = 'Inspection' THEN @.ColorRedDark
WHEN Status = 'Inspected' THEN @.ColorRed
WHEN Status = 'Certified' THEN @.ColorBlack
ELSE ''
END
FROM #Heats Heats
ORDER BY HeatNo
FOR XML AUTO

print @.HeatNo + (@.HeatsPerPage * 10) + 10

DROP TABLE #Heats
GO

Look at the "status" field above your billetUOM field. It's using a case statement. You'll want to perform similar stuff against your billetUOM field.

http://msdn2.microsoft.com/en-us/library/ms181765.aspx|||

I came up with this code and it still is still giving me syntax errors. Could you please take a look and see what I am doing wrong? Thank you very much for any help.

H.BilletUOM = CASE H.BilletUOM WHEN B71 THEN '28ft.'
WHEN B72 THEN '34ft.'
WHEN B73 THEN '40ft.'
ELSE ''
END,

|||I think you need to put your criteria in quotes as well.

WHEN 'B71' THEN...
WHEN 'B72' THEN...
etc..

Code for 1st day & the Last day of the Previous Month

Hi there,

See if you can help me with the following:

I need to write an SQL code that will return me:

The 1st day & the Last day of the Previous Month in the following format
(smalldatetime):

yyyy-mm-dd hh:mi:ss (24h)

Regards,

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1If you don't have a calendar table (which can be very helpful for
situations like this), you can do it on-the-fly using SQL Server like
so:

SELECT DATEADD(m, -1, FirstOfCurrentMonth) as FirstOfPreviousMonth,
DATEADD(d, -1, FirstOfCurrentMonth) as LastOfPreviousMonth
FROM (SELECT CONVERT(smalldatetime,DATENAME(m, GetDate())
+ ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth) a

Of course, you'll want to do the formatting on the client side.

HTH,
Stu|||On Wed, 20 Jul 2005 16:56:42 GMT, Ray via SQLMonster.com wrote:

> Hi there,
> See if you can help me with the following:
> I need to write an SQL code that will return me:
> The 1st day & the Last day of the Previous Month in the following format
> (smalldatetime):
> yyyy-mm-dd hh:mi:ss (24h)
> Regards,

declare @.d1 smalldatetime
declare @.d2 smalldatetime
declare @.d3 smalldatetime

-- @.d1 is the input date
set @.d1 = CURRENT_TIMESTAMP
-- truncate hours, min, etc.
set @.d1 = convert(smalldatetime, floor(convert(float, @.d1)))

-- @.d2 - last day of previous month

set @.d2 = dateadd(day, - datepart(day, @.d1), @.d1)

-- @.d3 - first day of previous month

set @.d3 = dateadd(day, - datepart(day, @.d2) + 1, @.d2)|||Hi Stu,

Thank you very very much .... it was a great help.

Best regards,

Stu wrote:
>If you don't have a calendar table (which can be very helpful for
>situations like this), you can do it on-the-fly using SQL Server like
>so:
>SELECT DATEADD(m, -1, FirstOfCurrentMonth) as FirstOfPreviousMonth,
>DATEADD(d, -1, FirstOfCurrentMonth) as LastOfPreviousMonth
>FROM (SELECT CONVERT(smalldatetime,DATENAME(m, GetDate())
> + ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth) a
>Of course, you'll want to do the formatting on the client side.
>HTH,
>Stu

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1|||Build a calendar table with all the temporal information you need
instead of trying to compute it on the fly. Next, the display of
temporal data has nothing to do how it is stored.|||Ross,

Thank you so much ...

Ross Presser wrote:
>> Hi there,
>>
>[quoted text clipped - 8 lines]
>>
>> Regards,
>declare @.d1 smalldatetime
>declare @.d2 smalldatetime
>declare @.d3 smalldatetime
>-- @.d1 is the input date
>set @.d1 = CURRENT_TIMESTAMP
>-- truncate hours, min, etc.
>set @.d1 = convert(smalldatetime, floor(convert(float, @.d1)))
>-- @.d2 - last day of previous month
>set @.d2 = dateadd(day, - datepart(day, @.d1), @.d1)
>-- @.d3 - first day of previous month
>set @.d3 = dateadd(day, - datepart(day, @.d2) + 1, @.d2)

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1