write qury or script [Resolved]

-- sample Insert script

create table student1 ( FirstName VARCHAR(20), LastName VARCHAR(20), studentCode varchar(20))

GO

DECLARE @code VARCHAR(20) ='',  @FirstName varchar(10) = 'Raj1', @LastName varchar(10) = 'Sharma1'

SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudentCode, 3, 10) AS INT)+1), 1001) FROM student1

INSERT INTO student1(FirstName, LastName, StudentCode) VALUES( @FirstName ,  @LastName , LEFT(@Firstname,1)+LEFT(@LastNAme,1)+ @code)



SET @FirstName = 'Bandi'

SET @LastName  = 'Chandu'

SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudentCode, 3, 10) AS INT)+1), 1001) FROM student1

INSERT INTO student1(FirstName, LastName, StudentCode) VALUES( @FirstName ,  @LastName , LEFT(@Firstname,1)+LEFT(@LastNAme,1)+ @code)



SELECT * FROM student1

GO

DROP TABLE student1



/*

FirstName	LastName	studentCode

Raj1	Sharma1	RS1001

Bandi	Chandu	BC1002

*/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

If you want to update the records by using RollNo then you can use the following script

CREATE TABLE StudInfo (StudCode varchar(15), Name varchar(50),Surname varchar(50),RollNo int, Address varchar(50),Prequlification varchar(50),Year INT, PrePercentage varchar(50))

GO

CREATE PROCEDURE dbo.SP_InsertStudInfo 

@StudName varchar(50), 

@StudSurname varchar(50), 

@RollNo int, 

@Address varchar(50), 

@Year varchar(50), 

@PreQulification varchar(50), 

@PrePercentage varchar(50) 

AS 

BEGIN 

DECLARE @code VARCHAR(20) =''

 

	SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudCode, 3, 10) AS INT)+1), 1001) FROM StudInfo

	IF NOT EXISTS (SELECT 1 FROM StudInfo WHERE RollNo= @RollNo)

		Insert into dbo.StudInfo(Name,Surname,RollNo,Address, Year, PreQulification,PrePercentage,StudCode) values(@StudName,@StudSurname,@RollNo,@Address,@Year,@PreQulification,@PrePercentage,LEFT(@StudName,1)+LEFT(@StudSurname,1)+@code) 

	ELSE

		UPDATE StudInfo SET 	

			StudCode = CASE WHEN StudCode IS NULL THEN LEFT(coalesce(@StudName, name),1)+LEFT(coalesce(@StudSurname, Surname),1)+@code ELSE StudCode END,

			Name = coalesce(@StudName, name),

			SurName = coalesce(@StudSurName, SurName),

			Address = coalesce(@Address, Address),

			Year = coalesce(@Year, Year),

			PreQulification = coalesce(@PreQulification, PreQulification),

			PrePercentage = coalesce(@PrePercentage, PrePercentage)

		WHERE RollNo = @RollNo

END 

GO

--1st time

EXEC dbo.SP_InsertStudInfo 'samU ', 'varma ', 1, 'Mumbai', 2013, 'BBA', 66



--2nd time

EXEC dbo.SP_InsertStudInfo 'tina', 'sharma', 2, 'Delhi', 2014, 'BCA', 65

SELECT * FROM StudInfo



/*OUTPUT1:  

StudCode	Name	Surname	RollNo	Address	Prequlification	Year	PrePercentage

sv1001	samU 	varma 	1	Mumbai	BBA	NULL	66

ts1002	tina	sharma	2	Delhi	BCA	NULL	65*/



-- 3rd time  UPDATE firstname & lastname of 2nd roll no 

EXEC dbo.SP_InsertStudInfo 'tinaUpdate', 'sharmaUpdate', 2, 'Delhi', NULL, 'BCA', 65

SELECT * FROM StudInfo



/*OUTPUT2:  

StudCode	Name	Surname	RollNo	Address	Prequlification	Year	PrePercentage

sv1001	samU 	varma 	1	Mumbai	BBA	NULL	66

ts1002	tinaUpdate	sharmaUpdate	2	Delhi	BCA	NULL	65*/



--DROP TABLE StudInfo

--DROP PROCEDURE dbo.SP_InsertStudInfo 

Observe the difference of two outputs

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

hi

try this Code

Select LEFT(Firstname,1)+LEFT(LastNAme,1)+CAST(1000+1 AS varchar) from T20

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

>> e.g. Raj Sharma then student code will be RS1001
I think 1001 is studentID (of type integer) in the Student table

SELECT LEFT(FirstName,1)+LEFT(LastName,1)+CAST( StudentID AS VARCHAR(6)) as StudentCode

FROM Student

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

hi
try this

;with emp as
(
Select ROW_NUMBER()over (ORDER BY ID)sno,*
from t20
)
select *,LEFT(Firstname,1)+LEFT(LastNAme,1)+CAST(1000+sno AS varchar)as Name from emp

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

You can use the below query to achieve this.

SELECT LEFT(FName, 1) + LEFT(LName,1) + CAST(1000+1 AS varchar(8))

FROM YOURTABLE

Happy Coding

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

hi Jaykumar, Thanks.. Can u tell me... I want to do dis while Inserting record means When I am inserting record at runtime it will take values from first name and lastname and assign to student code
means Insert query and ur qury wil execute concurruntly
eg. when i insert like
First Name: Raj
Last Name: Sharma
Student Code:RS1001

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

You can use the below code:-

DECLARE @FName AS VARCHAR(100),
@LName AS VARCHAR(100)

SET @FName = 'Raj'
SET @LName = 'Sharma'


INSERT INTO YOURTABLE VALUES(StudentCode)
SELECT LEFT(@FName, 1) + LEFT(@LName,1) + CAST(1000+1 AS varchar(8)) AS 'VALUE'

Happy Coding.

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

You can use the below code if you want to increase StudenCode.

DECLARE @FName AS VARCHAR(100),
@LName AS VARCHAR(100) ,
@StudenCode AS VARCHAR(100)

SET @FName = 'Raj'
SET @LName = 'Sharma'

SELECT @StudenCode = StudenCode FROM YOURTABLE

INSERT INTO YOURTABLE VALUES(StudentCode)
SELECT LEFT(@FName, 1) + LEFT(@LName,1) + CAST(CAST(RIGHT(@StudenCode, 3) AS INT) +1 AS varchar(8)) AS 'VALUE'

Happy Coding.

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

How you are getting 1001 code here...

DECLARE @FirstName varchar(10) = 'Raj', @LastName varchar(10) = 'Sharma'

INSERT INTO Student(FirstName, LastName, StudentCode) VALUES( @FirstName ,  @LastName , LEFT(Firstname,1)+LEFT(LastNAme,1)+'1001')

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

if you supposed to get the runtime Code value...
-- If there is no records in the table at all

DECLARE @code VARCHAR(20) ='',  @FirstName varchar(10) = 'Raj', @LastName varchar(10) = 'Sharma'

SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudentCode, 3, 10) AS INT)+1), 1001) FROM Student

INSERT INTO Student(FirstName, LastName, StudentCode) VALUES( @FirstName ,  @LastName , LEFT(Firstname,1)+LEFT(LastNAme,1)+ @code)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

thanks Bandi got ans from ur query. thanks all also..

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

can anyone explain me dis query please .. m not getting it ..

SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudentCode, 3, 10) AS INT)+1), 1001) FROM student1
what is 3,10 used for

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

hi

--Substring 3,10
--3 starting string position
--10 Ending the string position
--use this query
SELECT Substring('Jayakumar',3,10)

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

ok Jaykumar then in this query what
Substring(StudentCode,3,10) what output wil get

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

I am not getting this query... how it works.. can anyone explain dis..
SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudentCode, 3, 10) AS INT)+1), 1001) FROM student1

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

>> Substring(StudentCode,3,10) what output wil get
For suppose the table student has rows.

One of the value of StudentCode is BC1001. 

Substring(StudentCode,3,10) --> returns 1001

then I'm casting that to INTEGER and increment by 1 for next code value, After that getting Maximum code from the table

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

ok Bandi thanks got it..
I have a problem in dis query .. when I am inserting record it inserts StudCode in 1st keeping all records blank and then next line all records r inserted
I am attaching screenshot please check... and I am posting my Stored procedure also.. reply if find mistake..

ALTER PROCEDURE dbo.SP_InsertStudInfo

@StudName varchar(50),
@StudSurname varchar(50),
@RollNo int,
@Address varchar(50),
@Year varchar(50),
@PreQulification varchar(50),
@PrePercentage varchar(50)

AS
DECLARE @code VARCHAR(20) =''

BEGIN
SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudCode, 3, 10) AS INT)+1), 1001) FROM StudInfo

Insert into dbo.StudInfo(StudName,StudSurname,RollNo,Address,Year,PreQulification,PrePercentage,StudCode) values(@StudName,@StudSurname,@RollNo,@Address,@Year,@PreQulification,@PrePercentage,LEFT(@StudName,1)+LEFT(@StudSurname,1)+@code)

END

 Download source file

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

hi

what is ur issue your query working good

pass this value

SP_InsertStudInfo 'AA1','BB1',1002,'AFV1','2013','MCA','100%'

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Means You want to update the StudentCode column data with FirstCharinFirstName+FirstCharinLastName+Code...
Can you post the sample data of StudInfo Table.. and final result for the same data.....

I think you need UPDATE statement for existed students and INSERT for new student

UPDATE StudInfo

SET StudCode = LEFT(StudName,1)+LEFT(StudSurname,1)+@code

WHERE StudName = @StudName AND StudSurname= @StudSurname

I will send you the detailed script once you post sample data of studInfo

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Hi , I attached screen shot of my result .. pleaz download that file from my previous comment.

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Hi Pallavi,
I does not have access to download files in my office

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

ok wil post how my table giving result

StudCode Name Surname RollNo Address Prequlification PrePercentage

1 sv1001 sam varma 1 Mumbai BBA 66

2 1002

3 ts1002 tina sharma 2 Delhi BCA 65

when der is no record in table at 1st query it is working fine but on inserting 2nd record it inserts 1st studcode keeping all blank and bellow inserting all records.

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

hi

post your whole sql script i il check and send u

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Hi Check the below script.... There is no problem with this....

-- Table Creation

CREATE TABLE StudInfo (StudCode varchar(15), Name varchar(50),Surname varchar(50),RollNo int, Address varchar(50),Prequlification varchar(50),Year INT, PrePercentage varchar(50))



--Procedure creation

GO

CREATE PROCEDURE dbo.SP_InsertStudInfo 

@StudName varchar(50), 

@StudSurname varchar(50), 

@RollNo int, 

@Address varchar(50), 

@Year varchar(50), 

@PreQulification varchar(50), 

@PrePercentage varchar(50) 

AS 

BEGIN 

DECLARE @code VARCHAR(20) ='' 

	SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudCode, 3, 10) AS INT)+1), 1001) FROM StudInfo

	Insert into dbo.StudInfo(Name,Surname,RollNo,Address, Year, PreQulification,PrePercentage,StudCode) values(@StudName,@StudSurname,@RollNo,@Address,@Year,@PreQulification,@PrePercentage,LEFT(@StudName,1)+LEFT(@StudSurname,1)+@code) 

END 

GO

--Execution of procedure

--1st time

EXEC dbo.SP_InsertStudInfo 'sam ', 'varma ', 1, 'Mumbai', NULL, 'BBA', 66



--2nd time

EXEC dbo.SP_InsertStudInfo 'tina', 'sharma', 2, 'Delhi', NULL, 'BCA', 65



--Check the inserted data

SELECT * FROM StudInfo



--DROP TABLE StudInfo

--DROP PROCEDURE dbo.SP_InsertStudInfo 

Is the RollNo column Primary key in the StudInfo table?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Hi Jaykumar Dis is my Sql Script


USE [DBAssignment]
GO
/****** Object: Table [dbo].[StudInfo] Script Date: 09/04/2013 16:48:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StudInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Studcode] [varchar](max) NOT NULL,
[StudName] [varchar](50) NOT NULL,
[StudSurname] [varchar](50) NOT NULL,
[RollNo] [int] NOT NULL,
[Address] [varchar](50) NOT NULL,
[Year] [varchar](50) NOT NULL,
[PreQulification] [varchar](50) NOT NULL,
[PrePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[SP_InsertStudInfo] Script Date: 09/04/2013 16:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_InsertStudInfo]


@StudName varchar(50),
@StudSurname varchar(50),
@RollNo int,
@Address varchar(50),
@Year varchar(50),
@PreQulification varchar(50),
@PrePercentage varchar(50)




AS

DECLARE @code VARCHAR(20) =''

BEGIN
SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudCode, 3, 10) AS INT)+1), 1001) FROM StudInfo

Insert into dbo.StudInfo(StudName,StudSurname,RollNo,Address,Year,PreQulification,PrePercentage,StudCode) values(@StudName,@StudSurname,@RollNo,@Address,@Year,@PreQulification,@PrePercentage,LEFT(@StudName,1)+LEFT(@StudSurname,1)+@code)



END
GO

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Hi Bandi, Roll no. is not have primary key.
I have used ur Stored procedure
same result is giving
2 rows r inserting same time .. one contain only studcode and other contain all records.


1 st1001 sam tan 8 gtg 3452 efe 66
2 1005 0 0
3 pb1002 pallavi bhosale 1 satara 2013 Bsc 65
4 1005 0 0
5 tj1003 tina jak 3 gao 2012 BBA 77
6 1005 0 0
7 rg1004 reeta gore 2 kho 2013 ba 64
8 1005 0 0
9 yl1005 yan lan 4 sdh 2099 bs 54
10 1006 0 0
11 tp1006 tom pan 5 dfg 2011 BA 77

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

See Pallave.... Your script is also working fine...

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

SET ANSI_PADDING ON 

GO 

CREATE TABLE [dbo].[StudInfo]( 

[Id] [int] IDENTITY(1,1) NOT NULL, 

[Studcode] [varchar](max) NOT NULL, 

[StudName] [varchar](50) NOT NULL, 

[StudSurname] [varchar](50) NOT NULL, 

[RollNo] [int] NOT NULL, 

[Address] [varchar](50) NOT NULL, 

[Year] [varchar](50) NOT NULL, 

[PreQulification] [varchar](50) NOT NULL, 

[PrePercentage] [float] NOT NULL 

) ON [PRIMARY] 

GO 

SET ANSI_PADDING OFF 

GO 

/****** Object: StoredProcedure [dbo].[SP_InsertStudInfo] Script Date: 09/04/2013 16:48:22 ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

CREATE PROCEDURE [dbo].[SP_InsertStudInfo] 

@StudName varchar(50), 

@StudSurname varchar(50), 

@RollNo int, 

@Address varchar(50), 

@Year varchar(50), 

@PreQulification varchar(50), 

@PrePercentage varchar(50) 

AS 

DECLARE @code VARCHAR(20) ='' 

BEGIN 

SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudCode, 3, 10) AS INT)+1), 1001) FROM StudInfo 

Insert into dbo.StudInfo(StudName,StudSurname,RollNo,Address,Year,PreQulification,PrePercentage,StudCode) values(@StudName,@StudSurname,@RollNo,@Address,@Year,@PreQulification,@PrePercentage,LEFT(@StudName,1)+LEFT(@StudSurname,1)+@code) 

END 

GO





--1st time

EXEC dbo.SP_InsertStudInfo 'samU ', 'varma ', 1, 'Mumbai', 2013, 'BBA', 66





--2nd time

EXEC dbo.SP_InsertStudInfo 'samU ', 'varma ', 1, 'Mumbai', 2013, 'BBA', 66



--3rd time

EXEC dbo.SP_InsertStudInfo 'samU ', 'varma ', 1, 'Mumbai', 2013, 'BBA', 66



SELECT * FROM StudInfo



/*OUTPUT:

Id	Studcode	StudName	StudSurname	RollNo	Address	Year	PreQulification	PrePercentage

1	sv1001	samU 	varma 	1	Mumbai	2013	BBA	66

2	sv1002	samU 	varma 	1	Mumbai	2013	BBA	66

3	sv1003	samU 	varma 	1	Mumbai	2013	BBA	66

*/

Posted output of 3 executions..... check once... what is the problem here...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

ok.. Bandi .. dont know wat is exactly error.. I am inserting data from ASP.net web file... Individual query is working swperatly in sql..

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Bandi.. It's working

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Hi Pallavi,
Welcome :)
Why do you want VARCHAR(MAX) for StudentCode column? Is that necessary?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

no it's not neccesarry..ok wil update it.. actually I was thinking it as storing integer value which increase starting from 1000 so...
I am Lerner u may observe so much mistakes... but all r welcum... will update table wid proper data type ... and keys

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

See You have IDENTITY column as INT.... maximum the length of digits is 10 only.... and StudentCode rule is to set FirstChar of First ANem & last Name (in this case length of Letters is 2)
Total Length od Student Code will not exceed 12...
If you wish to use BIGINT for IDENTITY column StudentCode length will not exceed 15 or 20...
So Better to use length 50 or 60 for StudentCode

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

thnaks .. I wil make it... got the point

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator