InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:11:34 GMT20http://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxThnx folks for the replies!Wed, 24 Jun 2009 21:53:53 GMTkolangara_anandhttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspx[quote][b]Florian Reischl (6/24/2009)[/b][hr]I knew that any other constant expression should work equal. Just been unsure about the NULL.[/quote]Oh I see.I guess I started using NULL because it somehow emphasises the fact the the 'constant' isn't important or even relevant - it's just required by the syntax, as you know. There's no advantage or disadvantage to using NULL - as far as I know.PaulWed, 24 Jun 2009 15:16:43 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxI knew that any other constant expression should work equal. Just been unsure about the NULL.Thanks!FloWed, 24 Jun 2009 09:30:10 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxHey Flo,Nope - no difference at all, it just depends on my mood :-)ROW_NUMBER() OVER (ORDER BY (SELECT -42.7))...works just as well.PaulWed, 24 Jun 2009 09:08:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxHey PaulA little side question:You work with this row-number function:[code]ROW_NUMBER() OVER (ORDER BY (SELECT NULL))[/code]Is there any difference to this:[code]ROW_NUMBER() OVER (ORDER BY (SELECT 1))[/code]Sure, the results are equal but do you know if they [b]work[/b] equal? Both work with constant expressions but my thought was NULL is always something special.CheersFloWed, 24 Jun 2009 08:38:07 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxDynamic tally:[code="sql"];WITH Numbers (N) AS ( SELECT TOP (25000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master.sys.columns CROSS JOIN master.sys.columns C2 )SELECT 'A' + RIGHT((10000000000 + N), 9)FROM Numbers;[/code]PaulWed, 24 Jun 2009 08:00:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxUsing a a Tally table to create this range of items is just simple. If you don't know what a Tally table is, please search this site. You will find a great article published by Jeff Moden.[code]DECLARE @from INT, @to INTSELECT @from = 1, @to = 100SELECT 'A' + REPLICATE('0', 9 - LEN(CONVERT(VARCHAR(10), N))) + CONVERT(VARCHAR(10), N) FROM Tally WHERE N BETWEEN @from AND @to[/code]Wed, 24 Jun 2009 05:07:27 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic740910-145-1.aspxHi,I have an SP in which I receive a range of stock numbers and I need to insert each number in that range as a individual record in a table. Before inserting into the table I have to format the number store it as a varchar of size 10. The attributes other than this stock number would be same for all those records. I would need to insert upto 5000 such records. For example I receive an input as 1 -100 then the resultant table should be like as below Stock Number Other ParametersA000000001 xyzA000000002 xyzA000000003 xyz………..…...… xyzA000000100 xyz Currently in the SP there is loop running between from and to numbers and inserting 1 record at a time. I believe there should be a better way than that. Any pointers on how I can achieve this would be great.Thnx,AnandWed, 24 Jun 2009 05:00:46 GMTkolangara_anand