Using Visual Studio 2005 (or 2008) will allow you to build and deploy the assembly containing the CLR SP, it will also create the T-SQL wrapper for it. There's little...

Here's a C# version

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetItemName(SqlInt32 cItemID)

{

...

Jeff Moden (2/26/2009)

---

I follow the following casing standard...

All SQL Keywords in UPPER CASE.

All database names, table names, procedure names, column names, column aliases, variable names (basically, my stuff) etc, in...

SELECT DealNo,'Comment1' AS CommentType,Comment1 AS Comment

FROM #TEST

WHERE Comment1 IS NOT NULL

UNION ALL

SELECT DealNo,'Comment2' AS CommentType,Comment2 AS Comment

FROM #TEST

WHERE Comment2 IS NOT NULL

UNION ALL

SELECT DealNo,'Comment3' AS CommentType,Comment3 AS Comment

FROM #TEST

WHERE Comment3...

Maybe this?

select t1.*, t2.TranID

from dbo.TranTable t1

inner join dbo.TranTable t2

on t1.CustomerID = t2.CustomerID

and t2.Type =...

select PK

from mytable

where Event='Sold'

group by PK

having datediff(minute,min(Eventime),max(Eventime))>10

Try using COALESCE

SELECT

t1.blah1,

t2.blah2,

(t1.field1 * t1.field2)/COALESCE(t2.field3,t1.field4)

FROM

table1 t1

left join table2 t2

Maybe this?

create function dbo.grpsum(@GroupId int)

returns int

as

begin

return (select sum(ANumber) from dbo.TestTable where GroupId=@GroupId)

end

GO

alter table dbo.TestTable add constraint chkgrp check (dbo.grpsum(GroupId) <= 100)

Can you post your XML (as an attachment if possible)

Schemabinding guarantees that the dependancies of the function (e.g. column types) cannot be changed without first altering the function. Check BOL for more information.

The function needs to be schemabound

CREATE FUNCTION

[dbo].[Calculation] ( @p1 INT, @p2 TINYINT, @p3 TINYINT)

RETURNS INT

WITH SCHEMABINDING

AS

BEGIN

DECLARE @ReturnVal INT

SET @ReturnVal = @p1 + @p1*@p2/100 - @p1*@p3/100

RETURN @Returnval

END

DECLARE @PerDay INT

SET @PerDay = 2

SELECT

ORDER_DT = DATEADD(d,t1.N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),

ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1, --<== Stole from Jeff

...

SELECT

ORDER_DT = DATEADD(d,N-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)),

ORDER_QTY = ABS(CHECKSUM(NEWID()))%1000+1, --<== Stole from Jeff

PRODUCT_ID = ABS(CHECKSUM(NEWID()))%5+1...

Untested, but try this

WITH CTE(CategoryID,ParentCategoryID,CategoryName,fullpath) AS (

SELECT CategoryID,ParentCategoryID,CategoryName,CAST(CategoryName AS VARCHAR(MAX))

FROM mytable

WHERE ParentCategoryID IS NULL

UNION ALL

SELECT p.CategoryID , p.ParentCategoryID ,p.CategoryName, c.fullpath+'/'+CAST(p.CategoryName AS VARCHAR(MAX))

FROM CTE c

INNER JOIN mytable p ON p.ParentCategoryID=c.CategoryID)

SELECT CategoryID,CategoryName,ParentCategoryID

FROM...

This should give you all of the blocks

with cte as (select Emp_Id , Date, Code,

row_number() over(partition by Emp_Id order by Date)-

...