dewaMediashort . informative
Avoiding Cursor in TSQL
Challenge: People is asking me how to avoid the cursor. Solution: There are couple ways to avoid this: 1) Always use a natural power of SQL Language (join, sub query). 2) Use Common Table Expression (CTE) – http://msdn.microsoft.com/en-us/library/ms175972(SQL.90).aspx. 3) Utilise Merge statment (SQL 2K8) or http://sqlserver-tips.blogspot.com/2006/09/mimicking-merge-statement-in-sql.html
TSQL: Executing Stored Procedure/Function with Synonym
Challenge: We have an issue on executing the stored procedure/function with synonym (Error: Procedure or function expects parameter which was not supplied). We’ve created the synonym using GUI. Solution: Not sure what is causing on the GUI but if you run through a TSQL command, it works. USE EUCTelcoDB_DEV_2 CREATE SYNONYM syn_aspnet_UsersInRoles_GetRolesForUser FOR EUCAccountService_DEV.dbo.aspnet_UsersInRoles_GetRolesForUser;
Pivoting using STUFF and XML Path in SQL
Challenge: I have these 3 simple tables 1) Users: UserID INT, Username VARCHAR(32) 2) UserInRoles: UserID INT, RoleID INT 3) Roles: RoleID INT, RoleName VARCHAR(32) So if I joinining these 3 tables I might end up with for instance: UserID, Username; RoleName: 1; ‘test’; ‘Administrators’ 1; ‘test’; ‘Mobile Users’ 2; ‘test1′; ‘Administrators’ As you can [...]
Usefull Query
UPDATE EUCTelcoDB_DEV_2.dbo.prf_BatchItems SET CostNetExGST = CostNetExGST * -1 WHERE Code1 = ‘2S__T’ AND BatchID = 37 AND APartyNo IN (SELECT AA.APartyNo FROM (SELECT APartyNo, SUM(CostNetExGST) AS CostNetExGST from EUCTelcoDB_DEV_2.dbo.prf_BatchItems where Code1 LIKE ‘2S__V’ AND BatchID = 37 GROUP BY APartyNo) AA INNER JOIN (select APartyNo, SUM(CostNetExGST) AS CostNetExGST from EUCTelcoDB_DEV_2.dbo.prf_BatchItems where Code1 = ‘2S__T’ AND [...]
