RcBuilder@walla.com
call centre: 054-5614020

SQL Categories

Posted by: RcBuilder  /   Category: CTE / DBA / SQL / SQL Function / Stored Procedure / System Function   /   No Comments
27
Nov
2014

SQL Categories - OPTION 1 - declare @tbl table(Id int,Name nvarchar(50),Parent int,SubCategories nvarchar(500)) insert into @tbl select 1,'111',0,'' union all select 2,'222',1,',2,5,6,7,13,14,' union all select 3,'333',1,'' union all select 4,'444',1,'' union all select 5,'555',2,'' union all select 6,'666',2,'' union all select 7,'777',2,',7,13,14,' union all select 8,'888',3,'' union all select 9,'999',3,'' union all select 10,'aaa',3,'' union all select 11,'bbb',3,'' union all select 12,'ccc',3,'' union all select 13,'ddd',7,'' union all select 14,'eee',7,'' ------------------------------------ declare @Id int set.

Read more

SQL COLLAPSE example

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
20
Oct
2014

SQL COLLAPSE - multiple rows to single value we can use the COLLAPSE sql function to concatenation values into a single string data declare @tbl2 table(Name nvarchar(10), Quantity int, CreatedDate smalldatetime) insert into @tbl2 SELECT 'Roby', 100, '2009-12-05' UNION ALL SELECT 'Avi', 40, '2011-11-05' UNION ALL SELECT 'Avi', 60, '2010-05-03' UNION ALL SELECT 'Avi', 15, '2009-06-11' UNION ALL SELECT 'Ron', 50, '2010-04-11' UNION ALL SELECT 'Roby', 77,.

Read more

SQL PIVOT

Posted by: RcBuilder  /   Category: DATES / DBA / SQL / SQL Data Types / SQL Function / Stored Procedure   /   No Comments
07
Oct
2014

SQL PIVOT creates an horizontal group by. MUST define the groups names. structure pivot(<aggregation function> FOR <group by column> IN (<group by column values>)) USING example 1: declare @tbl1 table(Name nvarchar(10), Quantity int) insert into @tbl1 SELECT 'Roby', 100 UNION ALL SELECT 'Avi', 40 UNION ALL SELECT 'Avi', 60 UNION ALL SELECT 'Avi', 15 UNION ALL SELECT 'Ron', 50 UNION ALL SELECT 'Roby', 77 UNION ALL SELECT 'Roby', 12 UNION.

Read more

Split SQL FUNCTION

Posted by: RcBuilder  /   Category: SQL / SQL Data Types / SQL Function / Stored Procedure / System Function   /   No Comments
23
Sep
2014

Split SQL FUNCTION  create FUNCTION Split ( @String nvarchar (MAX), @Delimiter nvarchar (5) = ',' -- use the keyword 'DEFAULT' in order to use the default parameter ) RETURNS @ValueTable table ( nvarchar(50)) AS BEGIN if(RTRIM(LTRIM(@String)) = '') return declare @NextString nvarchar(50) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1) --Initialize set @NextString = '' set @CommaCheck = right(@String,1) --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck <> @Delimiter ) set @String =.

Read more

SQL Rows to Columns

Posted by: RcBuilder  /   Category: DBA / SQL / SQL Function / Stored Procedure   /   2 Comments
28
May
2014

SQL Rows2Columns  ALTER PROCEDURE Rows2Columns @tableName varchar(50), @columnName varchar(50) AS BEGIN create table #temp(Id int) insert into #temp(Id) values(1) declare @DATA table(RowId int identity(1,1), value nvarchar(50)) insert into @DATA exec('select cast(' + @columnName + ' as varchar(50)) from ' + @tableName) declare _cursor CURSOR FOR(select RowId, value from @DATA) declare @index int, @value nvarchar(50) OPEN _cursor FETCH NEXT FROM _cursor into @index, @value WHILE @@FETCH_STATUS = 0 BEGIN exec('ALTER TABLE #temp ADD.

Read more

SQL value from key Function

Posted by: RcBuilder  /   Category: SQL / SQL Function / Stored Procedure   /   No Comments
19
May
2014

SQL scalar function - key value collection ALTER FUNCTION fn_GetCRMAccountStatusName(@Id int) RETURNS nvarchar(35) AS BEGIN return case @Id when 1 then 'Real Registered' when 2 then 'Demo Registered' when 3 then 'Real Verified' when 4 then 'Lead' when 5 then 'Black Listed' when 6 then 'Blocked' when 7 then 'Real Deposit' else '' end END GO USING select.

Read more

SQL delete duplicates

Posted by: RcBuilder  /   Category: SQL / SQL Function / Stored Procedure / System Function   /   No Comments
19
May
2014

SQL delete duplicates declare @temp table(RowId int identity(1,1), id int, Name varchar(20)) insert into @temp select 123,'ROBY' union all select 344,'AVI' union all select 310,'ITAY' union all select 155,'SHLOMI' union all select 123,'SHIRLY' union all select 310,'TAMIR' union all select 167,'RAMI' delete from @temp where RowId in(select MAX(RowId) from @temp group by id having COUNT(*) > 1) select * from.

Read more

send email via SQL

Posted by: RcBuilder  /   Category: Master / SQL / SQL Function   /   No Comments
11
May
2014

send email via SQL steps: * turn on the 'Database Mail XPs' option (need to turn on 'show advanced' option) * add mail profile: msdb.dbo.sysmail_add_profile_sp @profile_name='', @description=''; * add mail account : msdb.dbo.sysmail_add_account_sp @account_name = '', @email_address = '', @display_name = '', @replyto_address = '', @description = '', @mailserver_name = '', @mailserver_type = 'SMTP', @port = , @username = '', @password = '', @enable_ssl = ; * set profile level sysmail_add_principalprofile_sp @profile_name='', @is_default=0, @principal_name='public'; * add the.

Read more

sql delete from join

Posted by: RcBuilder  /   Category: SQL / SQL Function / Stored Procedure   /   1 Comment
11
May
2014

sql delete from join declare @tbl1 table(Id int, Name nvarchar(30)) insert into @tbl1 values(1, 'ROBY') insert into @tbl1 values(2, 'AVI') insert into @tbl1 values(3, 'RON') insert into @tbl1 values(4, 'KOBI') insert into @tbl1 values(5, 'YOSSI') insert into @tbl1 values(6, 'GALIT') insert into @tbl1 values(7, 'RON') insert into @tbl1 values(8, 'RON') insert into @tbl1 values(9, 'ROBY') declare @tbl2 table(Id int, Name nvarchar(30)) insert into @tbl2 values(1, 'ROBY') insert into.

Read more

SQL COALESCE – FIRST NON NULL VALUE

Posted by: RcBuilder  /   Category: SQL / System Function   /   No Comments
04
May
2014

USE COALESCE TO RECEIVE THE FIRST NOT NULL VALUE FROM COLUMNS LIST declare @users table(Name varchar(20), Phone1 varchar(15), Phone2 varchar(15), Phone3 varchar(15)) insert into @users select 'Roby', null, null, null UNION ALL select 'Avi', '054-1111111', null, null UNION ALL select 'Itay', null, null, '054-3333333' UNION ALL select 'Roby', null, '054-2222221', '054-3333333' UNION ALL select 'Roby', '054-1111111', null, '054-3333333' UNION ALL select 'Tal', null,.

Read more
12
  • פיתוח מערכות
  • פתרונות טכנולוגים
  • קידום אתרים
  • בניית אתרים