Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
vamsidhar.tangutoori
Starting Member
19 Posts |
Posted - 2012-11-20 : 14:22:09
|
Hi,I am trying to insert into a table that looks like ID (identity) Range_From(int) Range_to(int)I am writing a stored procedure which has to insert into the range columns values from 0000 to 9999my question is 1)how do i insert 0000 into the columns?2)If i insert only a part of the range then it has to automatically insert another row with the remaining part of the range for ex:if i am trying to insert only 0000 to 1000, then it has to insert another row with 1001 to 9999orif i insert 9000 to 9999, then it has to insert another row with 0000 to 8999.Please help me understand how to do this!!!!!!!!!!!!!!!! |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-20 : 14:29:53
|
Would this work for you?DECLARE @startReqd INT, @endReqd INT, @startActual INT, @endActual INT; SET @startReqd = 0; SET @endReqd = 9999;INSERT INTO tbl (Range_From,Range_to) VALUES (@startActual,@endActual);IF @startActual > @startReqdBEGIN INSERT INTO tbl (Range_From,Range_to) VALUES (@startReqd,@startActual-1);ENDIF @endActual < @endReqdBEGIN INSERT INTO tbl (Range_From,Range_to) VALUES (@endActual+1,@endReqd);END |
|
|
vamsidhar.tangutoori
Starting Member
19 Posts |
Posted - 2012-11-20 : 14:41:57
|
Hi Sunitabeck,It works great thanks for the help.one more question when i am retreving the results i need it in 0000 or 0001 or 0012 format how do i achieve that?quote: Originally posted by sunitabeck Would this work for you?DECLARE @startReqd INT, @endReqd INT, @startActual INT, @endActual INT; SET @startReqd = 0; SET @endReqd = 9999;INSERT INTO tbl (Range_From,Range_to) VALUES (@startActual,@endActual);IF @startActual > @startReqdBEGIN INSERT INTO tbl (Range_From,Range_to) VALUES (@startReqd,@startActual-1);ENDIF @endActual < @endReqdBEGIN INSERT INTO tbl (Range_From,Range_to) VALUES (@endActual+1,@endReqd);END
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-20 : 14:51:45
|
You'll have to convert then numebrs to varchar and then add the zeroes like...VALUES( RIGHT('0000'+convert(varchar(4),@startReqd+1,4) )JimEveryday I learn something that somebody else already knew |
|
|
vamsidhar.tangutoori
Starting Member
19 Posts |
Posted - 2012-11-20 : 14:57:44
|
Thanks Jim,it really helped me!!!!!quote: Originally posted by jimf You'll have to convert then numebrs to varchar and then add the zeroes like...VALUES( RIGHT('0000'+convert(varchar(4),@startReqd+1,4) )JimEveryday I learn something that somebody else already knew
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-20 : 15:23:05
|
You're welcome!JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|