SQLSERVER by VAGALLA SURESH REDDY

Create table xx(id int identity primary key, name varchar(10),gender bit)
CREATE TYPE [dbo].xx_type AS TABLE(
id int , name varchar(10),gender bit,isModified bit
)GO　insert into xx select 'c',1
　declare @my_type xx_type

insert into @my_type
select *,0 as isModified from xx
update @my_type set name='cc',ismodified=1 where id=3
insert into @my_type select 0,'d',1,0 -- is modified should be zero for newly inserted records
insert into @my_type select 0,'e',1,0 -- is modified should be zero for newly inserted records
　
　exec USP_XX_Modify @my_type
Create PRoc USP_XX_Modify(@my_type xx_type readonly) AS
BEGIN -- updating modified recordsupdate At set name=t.name , gender=t.gender
from @my_type t inner join xx AT on t.id=at.id
where isModified=1
-- inserting newly passed recordsInsert into xx
select name,gender from @my_type where id=0
END　select * from xx

Sunday, 18 August 2013

concatenate EVERY 3rd CHARACTER using FUNCTION and PROCEDURE

concatenate EVERY 3rd CHARACTER using FUNCTION and PROCEDURE----------------

---------------------------USING PROCEDURE---------------------------------
CREATE PROC name_store(@stringname VARCHAR(300))
AS
BEGIN
DECLARE @char_concat varchar(50)=''
DECLARE @i int=3
WHILE(@i<LEN(@stringname))
BEGIN
select @char_concat=@char_concat+substring(@stringname,@i,1)
SET @i=@i+3
END
PRINT @char_concat
END
EXEC name_store 'JFSOAIRFGASLKEHLRUWAH...UDSFJGNZDSFH'

--------------------USING FUNCTION------------------------------------------------

ALTER FUNCTION get_concat_char(@FULLNAME VARCHAR(50))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @I INT=3
DECLARE @NAME VARCHAR(100)=''
WHILE(@I<LEN(@FULLNAME))
BEGIN
SET @NAME=@NAME+SUBSTRING(@FULLNAME,@I,1)
SET @I=@I+3
END
RETURN @NAME;
END
SELECT DBO.get_concat_char('JZSDFJJBVSUDFSMAZBXFCVH')

Print DATABASES and NO OF TABLES in each DATABASE--using CURSOR

------------CREATE PROC for Print DATABASES and NO OF TABLES in each DATABASE---------

ALTER PROC concatination
AS
BEGIN
DECLARE @qry varchar(100)
DECLARE @NAME VARCHAR(MAX)=''
DECLARE c1 CURSOR FOR SELECT name FROM sys.databases
OPEN C1
FETCH NEXT FROM C1 INTO @NAME
WHILE(@@FETCH_STATUS<>-1)
BEGIN
set @qry= 'select '+char(39)+@name+char(39)+',count(*) from '+@name+'.sys.tables'
--EXEC(@qry)
fetch next from c1 into @name
PRINT @qry
END
CLOSE C1
DEALLOCATE C1
END
EXEC concatination

Saturday, 17 August 2013

SCRIPT by PROCEDURE

---------------------------PRODUCE SCRIPT through PROCEDURE------------------------

alter PROC usp_get_inserts(@table_name varchar(50)) as
BEGIN
--usp_get_inserts '[TABLE NAME]'
declare @cols varchar(1000) =''
declare @ins varchar(1000)=''
declare @cols_type varchar(max)=''
select @cols=@cols+','+c.name ,@cols_type= @cols_type
+ '+'',''+' +case when t.name in('int','money') then 'cast('+ c.name + ' as varchar(10))' else 'char(39) +'+c.name + '+ CHAR(39)' end
from sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
where object_id=object_id(@table_name)
set @cols = right(@cols,len(@cols)-1)
set @cols_type = right(@cols_type,len(@cols_type)-5)
set @ins= 'INSERT INTO '+@table_name+'(' + @cols + ') VALUES('
exec('select '''+ @ins + '''+'+ @cols_type + '+'')'' from ' + @table_name)
END

SPLIT FUNCTION

----------------------SPLIT names using STRING FUNCTIONS--------------------------

/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 08/06/2012 16:16:27 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

-- select distinct mls_mail_to from gpc_mails where MLS_MESSAGE_FROM='NC' and mls_mail_status is null

create function [dbo].[Split] (@String varchar(8000), @Delimiter char(1))

Returns

@Results Table (Items varchar(8000))

As

Begin

Declare @Index int

Declare @Slice varchar(8000)

Select @Index = 1

If @String Is NULL Return

While @Index != 0

Begin

Select @Index = CharIndex(@Delimiter, @String)

If (@Index != 0)

Select @Slice = left(@String, @Index - 1)

else

Select @Slice = @String

Insert into @Results(Items) Values (@Slice)

Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break

End

Return

End

--select * from dbo.Split('1,vagalla,SURESH,reddy',',,')