CRM 4: user-side call jscript event handler for CRM-entity

// CRM 4.0 SDK Example
// Define the soapBody for the WhoAmI request.
// Difference from Microsoft CRM 3.0 is requirement to call method GenerateAuthenticationHeader() in SOAP Envelope

var soapBody = "";
soapBody += "";
soapBody += "";
soapBody += "";
//Wrap the Soap Body in a soap:Envelope.
var soapXml = "soapXml += "xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" ";
soapXml += "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" ";
soapXml += "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">";
soapXml += GenerateAuthenticationHeader();
soapXml += soapBody;
soapXml += "";
var SERVER_URL = "http://crm4.company.ru:5555";
// Create the XMLHTTP object for the execute method.
var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
xmlhttp.open("POST", SERVER_URL + "/mscrmservices/2007/crmservice.asmx", false);
xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
//Send the XMLHTTP object.
xmlhttp.send(soapXml);
// Create an XML object to parse the results.
xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async = false;
xmlDoc.loadXML(xmlhttp.responseXML.xml);
// Get the user's ID.
var userid = xmlDoc.getElementsByTagName("UserId")[0].childNodes[0].nodeValue;
alert(userid);

T-SQL example: "UPSERT" model (Insert + Update) by T-SQL with changeable number of input parameters

ALTER PROCEDURE [dbo].[spSave]
@InputItems char(2048)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @FilterID INT;
SET @FilterID = -1;

BEGIN TRANSACTION;

DECLARE @TempString varchar(2048)
DECLARE @Temp varchar(2048)
DECLARE @ChPos int

DELETE FROM WorkTable WHERE ID = @FilterID

IF @InputItems <> ''
BEGIN
SET @TempString = ''
SET @Temp = ''
SET @ChPos = -1

SET @TempString = @InputItems

SET @ChPos = CHARINDEX(',', @TempString)

WHILE @ChPos > 0
BEGIN
SET @Temp = SUBSTRING(@TempString, 1, (@ChPos - 1))
INSERT INTO WorkTable
(ID, YourValue)
VALUES
(@FilterID, CAST(@Temp AS int))

IF @@ERROR != 0 GOTO ErrorHandler

SET @TempString = SUBSTRING(
@TempString,
(@ChPos + 1),
(LEN(@TempString) - @ChPos))
SET @ChPos = CHARINDEX(',', @TempString)
END

INSERT INTO WorkTable
(ID, YourValue)
VALUES
(@FilterID, CAST(@TempString AS int))
IF @@ERROR != 0 GOTO ErrorHandler
END
COMMIT TRANSACTION

SELECT @FilterID AS RetVal
RETURN 0
ErrorHandler:
SELECT @FilterID AS RetVal
IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
RETURN @@ERROR
END

T-SQL example: dynamically generate columns by table

DECLARE @Seria INT
SET @Seria = 15

DECLARE @Kpcount INT
SELECT @Kpcount = Pairs * Sections FROM S_SeriaTPS WHERE ID = @Seria

DECLARE @Cnt INT
SET @Cnt=1

-- Colums list string
DECLARE @sf_1 VARCHAR(MAX)
SET @sf_1 = ''

-- Join section string
DECLARE @sj_1 VARCHAR(MAX)
SET @sj_1 = ''

DECLARE @ChkVal INT
SET @ChkVal = 0

WHILE (@Cnt <= @Kpcount)
BEGIN
SET @sf_1 = @sf_1 +
', t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.RepDate AS [Date ' + RTRIM(CAST(@Cnt AS CHAR(2))) + '], ' +
't0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.DiamAv AS [Diameter avg ' + RTRIM(CAST(@Cnt AS CHAR(2))) + '] '

-- Generate join section
SET @sj_1 = @sj_1 +
'LEFT JOIN (
SELECT
tk.IDTps,
cm.RepDate,
tk.Position,
DiamAv =
ABS(
COALESCE((bm.DiamL - (bm.TolshBandL - cm.TolshBandL) * 2), bm.DiamL) +
COALESCE((bm.DiamR - (bm.TolshBandR - cm.TolshBandR) * 2), bm.DiamR)) / 2
FROM TpsKp tk
LEFT JOIN BasicMeasurements bm ON bm.IDKp=tk.IDKp
LEFT JOIN Measurements cm ON
cm.ID=(
SELECT TOP 1 cm1.ID
FROM Measurements cm1
WHERE cm1.IDKp=tk.IDKp
ORDER BY cm1.RepDate DESC)
WHERE tk.Position=' + RTRIM(CAST(@Cnt AS CHAR(2))) +
') t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + ' ON t0' + RTRIM(CAST(@Cnt AS CHAR(2))) + '.IDTps=t.ID '

-- Column counter increment
SET @Cnt = @Cnt + 1
CONTINUE
END

DECLARE @sql VARCHAR(MAX)
SET @sql =
'SELECT RTrim(t.Nomer) AS [Name] {0} FROM Tps t {1} WHERE t.IDSeria=' +
RTRIM(CAST(@Seria AS CHAR(3))) + ' ' +
'ORDER BY Nomer'

IF @sf_1 = ''
BEGIN
SET
@sf_1 = ', ''no data'' AS [Info]'
END

SET @sql = REPLACE(@sql, '{0}', @sf_1)
SET @sql = REPLACE(@sql, '{1}', @sj_1)

EXEC (@sql)

-----------------------
Result seem's like this
Name Date 1 Diameter avg 1 Date 2 Diameter avg 2
316 2007-12-28 13:11:23.000 1254 2007-12-28 13:12:16.000 1255
319 2008-01-19 14:55:00.000 1250 2008-01-19 14:56:30.000 1255
321 2008-02-15 13:15:21.000 1212 2008-02-15 13:15:37.000 1213