Order By with VARCHAR

Hi everyone,

In order by clause of varchar(50) column for numeric values,the result is different from order by with integer column..

E.g. select * from classroom order by classroom (where classroom is varchar(max) column)
the result is:
       1
       10
       11
       12
        2
        3
      ........
now suppose i need it order by like 1 2 3 4 5 ... i need to convert the column to integer , instead of changing the design of table you could write your query as this...

select * from classroom order by cast(classroom as integer)

would give you result as 1 2 3 4 5........

Happy Coding,
Regards,
Krunal Panchal

How to get Id of Just Inserted Value in SQL

Hi everyone,
This is a Simple example of Getting Unique Id (Auto Id) for the inserted value after insertion of values in SQL table

Declare @subjectid INT =NULL out
-----Insertion Process-----------

Insert into Subject(SyllabusID,ClassroomID,Subject)
values(@syllabusid ,@classroomid ,@strsubject )

------Get Id into @subjectid----
set @subjectid =SCOPE_IDENTITY ();

Happy Coding,
Regards,
Krunal Panchal

Stored Procedure For Inserting Values in Table In Multiple Column using CSV and getting their AutoId

Hi everyone,
I got indulged into one situation where i have to insert values in SQL table using CSV (Comma Seperated Values) , now the situation became more critical where i need to insert values at a particular ID for a particular table.
i.e. i am having 2 tables namely:classroom, subject, syllabus
now i have data like 4,5,6 to be checked if present and if they are not present then they should be inserted for particular syllabus-id, now by getting the classroom-id for particular syllabus-id i have to insert subject eg. English, Maths  for that particular classroom-id and syllabus-id .
I made the Stored Procedure for simple Insertion.......
But the problem was that after inserting the subject the parameter of subject became null OR '' so i stored it in one variable before inserting , and set the subject parameter again to the stored one.......


This whole process also could be done in asp.net with several checks and for loops , but i thought that if it would be done in asp.net then there would be much load to the server. Then i thought of making Stored Procedure for this whole process and here it is........

Stored Procedure......

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Krunal N. Panchal
-- Create date: Monday,November ,22,2010
-- Description: This Stored Procedure is for School Merge [For Insertion of Classroom And Subject By CSV]
-- =============================================
alter PROCEDURE [dbo].[Insert_Classroom_Subject]
--Parameters of Stored Procedure--
@classroom varchar(max),
@subject varchar(max),
@syllabusid varchar(max),
@classroomid varchar(max) =null out,
 @subjectid varchar(max)=NULL out
AS
DECLARE @countclassroom int
    DECLARE @strclassroom VARCHAR(max)
    DECLARE @spotclassroom SMALLINT
     DECLARE @storedsubject varchar(max)
SET @storedsubject =@subject

--------------for classroom insertion--------------
WHILE @classroom <> ''

    BEGIN
SET @subject =@storedsubject
        SET @spotclassroom = CHARINDEX(',', @classroom)
        IF @spotclassroom>0
            BEGIN
                SET @strclassroom = CAST(LEFT(@classroom, @spotclassroom-1) AS varchar(max))
                SET @classroom = RIGHT(@classroom, LEN(@classroom)-@spotclassroom)
            END
        ELSE
            BEGIN
                SET @strclassroom = CAST(@classroom AS varchar(max))
                SET @classroom = ''
            END
          
            --Check Classroom if Present---          
            SELECT @countclassroom=count(1) FROM classroom
WHERE Classroom =@strclassroom
and SyllabusID =@syllabusid
----if Classroom not present then insert it----
IF @countclassroom =0
BEGIN
Insert into Classroom(Classroom ,SyllabusID )
values (@strclassroom ,@syllabusid )
set @classroomid=SCOPE_IDENTITY ();
END
Else
-----else get the id of the classroom for selected subject----
Begin
set @classroomid=(select classroomid from Classroom WHERE Classroom =@strclassroom
and SyllabusID =@syllabusid )
End

-------------------End of classroom insertion---------------------

-------------------For Subject Insertion-----------------------------

DECLARE @countsubject int
    DECLARE @strsubject VARCHAR(max)
 
    DECLARE @spotsubject SMALLINT


----------------For Storing the subject-------------
--SET @storedsubject =@subject

WHILE @subject <> ''
BEGIN
SET @spotsubject = CHARINDEX(',', @subject)
IF @spotsubject>0
BEGIN
SET @strsubject = CAST(LEFT(@subject, @spotsubject-1) AS varchar(max))
SET @subject = RIGHT(@subject, LEN(@subject)-@spotsubject)
END
ELSE
BEGIN
SET @strsubject = CAST(@subject AS varchar(max))
SET @subject = ''
END

-----------check if subject is present for particular classroom and syllabus
SELECT @countsubject=count(1) FROM subject
WHERE ClassroomID  =@classroomid
and SyllabusID =@syllabusid
and Subject =@strsubject

-----count is 0 then insert the subject------
IF @countsubject =0
Begin
Insert into Subject(SyllabusID,ClassroomID,Subject)
values(@syllabusid ,@classroomid ,@strsubject )
set @subjectid =SCOPE_IDENTITY ();
End

End
---------------------Setting value of subject-----------


     End
  


GO

Happy Coding.

Regards,

Krunal Panchal