Insert list of values in sql server

November 11, 2008 1 comment

We can insert list of values in sql server database in single column. This list is comma separated. Here is store procedure doing this.

Create PROCEDURE  States_InsertStateList
@Siteid as int,
@StrSateID as varchar(5000),
@strTaxAmout as varchar(5000)
AS
BEGIN
delete from Taxes where Site_ID=@Siteid
Declare @Delimiter char(1)
DECLARE @StateID Varchar(5000)
DECLARE @TaxAmout Varchar(5000)
set @Delimiter = ‘,’
declare @List TABLE (Item varchar(8000),Amount varchar(8000))
WHILE CHARINDEX(@Delimiter,@StrSateID,0) <> 0
BEGIN
SELECT @StateID=RTRIM(LTRIM(SUBSTRING(@StrSateID,1,CHARINDEX(@Delimiter,@StrSateID,0)-1))),

@StrSateID=RTRIM(LTRIM(SUBSTRING(@StrSateID,CHARINDEX(@Delimiter,@StrSateID,0)+1,LEN(@StrSateID))))
SELECT @TaxAmout=RTRIM(LTRIM(SUBSTRING(@strTaxAmout,1,CHARINDEX(@Delimiter,@strTaxAmout,0)-1))),

@strTaxAmout=RTRIM(LTRIM(SUBSTRING(@strTaxAmout,CHARINDEX(@Delimiter,@strTaxAmout,0)+1,LEN(@strTaxAmout))))
IF LEN(@StateID) > 0
begin
INSERT INTO @List SELECT @StateID,@TaxAmout
end
END
IF LEN(@StrSateID) > 0
INSERT INTO @List SELECT @StrSateID,@strTaxAmout

insert into Taxes (State_ID,Amount,Site_ID)select Item,Amount,@Siteid from @list
END

ModalPopupExtender

February 13, 2013 Leave a comment

If you have two ModalPopupExtender with same BehaviorID then the second ModalPopupExtender will be visible. If this happen then change

BehaviorID of second, will work.

Categories: Uncategorized

Validate Checkbox list

January 5, 2010 1 comment

To validate checkbox list in asp.net is very simple. I found the following code searching google.

To validate checkbox list use the CustomValidator  control and set the ClientValidationFunction to the
javascript function. Following is the javascript code.
function ValidateCheckboxList(source, args)
{
var chkcbl_repeat_future= document.getElementById (‘Id of the checkbox list’);
var chkList= chkcbl_repeat_future.getElementsByTagName(“input”);
for(var i=0;i
{
if(chkList[i].checked)
{
args.IsValid = true;
return;
}
}
args.IsValid = false;
}
Here you can download example code

Reset validation controls in asp.net

October 30, 2009 Leave a comment

When we use validation controls e.g required fieldvalidater in asp.net form and on the same form we are using reset button. Reset button reset the field but the validation controls are not reset.

I found the following code is good for doing or resting the validation controls. Now our reset button will be

server control not html input type control.

 

Dim val As IValidator

For Each val In Validators

val.IsValid = True

Next

This code will reset the all validation controls in the form. Now we will reset our other fields too.

 

Here is code.

 

Protected Sub btn_Reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Reset.Click

Dim val As IValidator

For Each val In Validators

val.IsValid = True

Next

ResetFormControlValues(Me)

End Sub

Private Sub ResetFormControlValues(ByVal parent As Control)

Me.txt_ConfirmTag.Text = “”

Me.txt_Description.Text = “”

Me.txt_RewardAmount.Text = “”

‘For Each c As Control In parent.Controls

If c.Controls.Count &gt; 0 Then

ResetFormControlValues(c)

Else

Select Case (c.GetType().ToString())

Case “System.Web.UI.WebControls.TextBox”

CType(c, TextBox).Text = “”

Case “System.Web.UI.WebControls.CheckBox”

CType(c, CheckBox).Checked = False

Case “System.Web.UI.WebControls.RadioButton”

CType(c, RadioButton).Checked = False

Case “System.Web.UI.WebControls.DropDownList”

CType(c, DropDownList).SelectedIndex = 0

End Select

End If

Next c

End Sub

 

Generate Insert or Update Statements from table / SQL Server Row Script Creator

October 20, 2009 Leave a comment

After googling I found some interesting code to generate insert and update statement for the specified table in sql server.

This script will generate script to insert/update from a source table in one database to an  identical destination table in another database or server.  It can be run for inserts or updates,  and can be run for a single row in insert and update mode, or all rows in a table for insert mode.

declare @tab varchar(50)

,@pk1Val varChar(10)

,@pk1Name varChar(50)

,@qt char(1)

,@StatementType varChar(10)

set nocount on

/*

Instructions:

1) open script and connect to the source database

2) Change the  variable values to change the output options for the script below (@tab, @statementtype etc)

3) execute the script (best to use text output)

4) copy the script output into a script window, and run on the destination table.

@Tab = the name of the source table

@pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row

@pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key

@StatementType = either ‘INSERT’ to create an insert statement or ‘UPDATE’ for an Update statement

*/

select @tab = ‘mytable’, @pk1Val = ”, @pk1Name = ”, @StatementType = ‘UPDATE’

declare @tabName varchar(50)

, @colName varchar(50)

, @colType varchar(50)

, @collength varChar(50)

, @colOrder int

, @IsIdent char(1)

create table #output (Line varChar(4000), LineOrder int, rowNumber int)

create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))

declare @out varchar(4000)

,@lineCounter int

,@ColValue varchar(4000)

,@sortCol varchar(50)

/* get the ordering column */

select @sortCol = sc.Name

from sysobjects so

inner join syscolumns sc

on so.id=  sc.id

inner join systypes st

on sc.xtype = st.xusertype

where so.Name = @tab

and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))

/* put in the repeating values based on the columns*/

declare objCurs CURSOR FOR

select so.name, sc.name, st.name, sc.length, Case when sc.status = 0x80 then ‘Y’ else ‘N’ END as IsIdent, ColOrder

from sysobjects so

inner join syscolumns sc

on so.id=  sc.id

inner join systypes st

on sc.xtype = st.xusertype

where so.Name = @tab

DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int

select @numCols = count(sc.id)

from sysobjects so

inner join syscolumns sc

on so.id=  sc.id

where so.Name = @tab

–select @numCols  –debug code

open objCurs

Fetch from objCurs

into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder

while @@fetch_status = 0

begin

SET @counter = 0

/* get the value from the table */

if @IsIdent = ‘N’

BEGIN

–select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder  –debug code

/* increase better type handling by inserting more case statments, handling different data types */

if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0

begin

/* getting all rows in the table */

exec (‘insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)

select  ”’ + @colName + ”’, ‘ + @ColOrder + ‘, Convert(nvarchar(4000),’ + @colName + ‘) , ”’ + @colType + ”’ from ‘ + @tabName + ‘ order by ‘ + @SortCol + ‘ ‘ +

‘ declare @counter int set @counter = 0 ‘ +

‘ update #ColumnValues Set @Counter = RowNumber = @Counter + (‘ + @numCols + ‘ * 10) where ColName = ”’ + @colName + ”” )

end

else

begin

/* filtering by a pk val */

exec (‘insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)

select 0, ”’ + @colName + ”’, ‘ + @ColOrder + ‘, Convert(nvarchar(4000),’ + @colName + ‘) , ”’ + @colType + ”’ from ‘ + @tabName +

‘ where ‘ + @pk1Name + ‘ = ‘ + @pk1Val)

end

end /* if @isIdent = ‘n’ */

Fetch Next from objCurs

into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder

end

–select * from #ColumnValues –debug code

select @MaxRowNumber = Max(rowNumber) from #columnValues –keep highest row number so we know when we are finished

select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber

/* next cursor for outputting the results from the retval table into the output table */

declare ColVal_Curs  cursor for

select ColName , ColOrder , RowNumber , ColValue , colType

from #ColumnValues

order by RowNumber, ColOrder

open ColVal_Curs

–set the last row number to the first in the table, so post loop checking works

select @lastRowNumber = min(rowNumber) from #ColumnValues

set @lineCounter = @LastRowNumber –initialise at the first row

fetch from ColVal_Curs into

@colName, @ColOrder, @RowNumber, @colValue, @ColType

while @@Fetch_status = 0

BEGIN /* cursor loop */

/* get the quote type to enclose the value from the column type */

select @qt = case @colType

when ‘nvarchar’ then ””

when ‘nchar’ then ””

when ‘DateTime’ then ””

when ‘ntext’ then ””

else ”

end

–select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType

if not @ColValue is null

if @rowNumber = @lineCounter

select @out = case @statementType

when  ‘UPDATE’ THEN ‘Update ‘ + @tab + ‘ SET ‘

when  ‘INSERT’ then ‘INSERT INTO ‘ + @tab + ‘ (‘

end

begin

if @StatementType = ‘UPDATE’

BEGIN

select @Out = @out + @colName + ‘ = ‘ + @qt + COALESCE(@ColValue, ‘NULL’) + @qt + ‘,’ — + @ColType

insert into #output (Line, LineOrder)

values (@out, @lineCounter)

end

if @statementType = ‘INSERT’

BEGIN

/* put comma in */

if @lineCounter &gt; @RowNumber –not first line in set of values for row

select @out = @out + ‘,’

/*put in the name of the column */

insert into #output (Line, LineOrder)

values (@out + @colName

, @lineCounter)

if @lineCounter &gt; @RowNumber –not first line in set of values for row

select @out = ‘,’

else

select @out = ”

/* put in the value of the column */

insert into #output (Line, LineOrder)

values (@out + @qt + COALESCE(@ColValue, ‘NULL’) + @qt

, @lineCounter + 10 + @numCols)

END

end  /*not @ColValue is null */

select @lineCounter = @lineCounter + 1

set @out = ”

/* get the next record*/

fetch from ColVal_Curs into

@colName, @ColOrder, @RowNumber, @colValue, @ColType

–select @ColOrder, @MaxColOrder, @@Fetch_Status  –debug code

if (@rowNumber &gt; @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)

BEGIN

/* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/

/* ie we are building a record to insert, and the PK changes because we are at the next record */

/* remove the last comma from the last line */

declare @lastLine int

if @statementType = ‘UPDATE’

begin

/*remove last comma*/

update #output

set Line = left(Line,datalength(Line)-1)

where lineOrder = @LineCounter

/* insert a ‘where’ clause */

insert into #output (line, LineOrder)

select ‘ WHERE ‘ + @pk1Name + ‘ = ‘ + @pk1Val, Max(LineOrder) + 1 from #output

end

if @statementType = ‘INSERT’

BEGIN

/* put in a ‘values’ statement between the column names and the column values */

insert into #output (Line, LineOrder)

values (‘) VALUES (‘, @LastRowNumber + @numCols + 5)

/* close off the lot */

insert into #output (line, lineorder)

select ‘)’, Max(LineOrder) + 1 from #output

END

set @lastRowNumber = @RowNumber

set @lineCounter = @RowNumber  /* reset linecounter for next set */

update #output

set RowNumber = @rowNumber

where RowNumber is null

End /* if rownumber &gt; last row number */

end /* cursor loop */

close objCurs

deallocate objCurs

close ColVal_Curs

deallocate ColVal_Curs

/* the following is an extra loop over the original code to output the code as one statement per row */

create table #combineOutput (rowNumber int, line varchar(4000))

/* get the statements out from the list*/

declare @output varchar(8000), @codeLine varchar(4000), @thisRowNum int, @lastRowNum int

select @output = ”

declare line_curs cursor for

select line, RowNumber  from #output order by RowNumber, lineorder

open line_curs

fetch from line_curs into @codeLine, @thisRowNum

select @lastRowNum = @thisRowNum

while @@fetch_status = 0

begin

if @thisROwNum &gt; @lastRowNum

BEGIN

/* insert a row into the aggregate table if a new row number */

insert into #combineOutput (rowNumber, line) values (@rowNumber, @output)

set @output = ”

END

select @output = @output + @codeLine + ‘ ‘

select @lastRowNum = @thisRowNum

fetch from line_curs into @codeLine, @thisRowNum

end

/* the last row needs to be inserted */

insert into #combineOutput (rowNumber, line) values (@rowNumber, @output)

close line_curs

deallocate line_curs

select line as [Copy and paste code from below] from #combineOutput order by rowNumber

/*  bug tracking code – uncomment to diagnose problems

select distinct RowNumber from #ColumnValues order by 1

select * from #ColumnValues

order by RowNumber, ColOrder, ColName, ColValue

*/

drop table #output

drop table #combineOutput

drop table #ColumnValues

set nocount off

/*

Bruce Chapman Aug 2006

*/

Categories: Sql Server Tags:

SQL SERVER – 2008 – Interview Questions and Answers

April 6, 2009 Leave a comment

SQL SERVER – 2008 – Interview Questions and Answers

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

SQL SERVER – 2008 – Interview Questions and Answers

Categories: Uncategorized

Drop Store Procedure in Sql Server

November 3, 2008 1 comment

To drop sql server store procedure use the following .

USE [name of database]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[name of store procedure]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[name of store procedure]