Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating array in sql server Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 23, 2013 4:17 AM
Points: 1, Visits: 13
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it
Post #1443172
Posted Wednesday, April 17, 2013 7:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
rama.king127 (4/17/2013)
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it


Think outside the box, the sql way like this...

 set nocount on;
declare @sqlArray table (id int identity(1,1), item int)
insert into @sqlArray(item)
select 3 union all
select 4;


declare @x int=1, @arrayLength int, @curItem int
select @arrayLength=MAX(id) from @sqlArray;
--now loop thru your array =)
while @x <= @arrayLength begin
select @curItem = item from @sqlArray where id=@x;
print @curItem;
set @x= @x+1;
end


Post #1443260
Posted Wednesday, April 17, 2013 7:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 2,422, Visits: 7,441
You may want to have a read about custom types.


Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1443265
Posted Wednesday, April 17, 2013 4:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
haiao2000 (4/17/2013)
rama.king127 (4/17/2013)
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it


Think outside the box, the sql way like this...

 set nocount on;
declare @sqlArray table (id int identity(1,1), item int)
insert into @sqlArray(item)
select 3 union all
select 4;


declare @x int=1, @arrayLength int, @curItem int
select @arrayLength=MAX(id) from @sqlArray;
--now loop thru your array =)
while @x <= @arrayLength begin
select @curItem = item from @sqlArray where id=@x;
print @curItem;
set @x= @x+1;
end




Gosh, no. Think outside the box. Don't use RBAR for this.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443537
Posted Wednesday, April 17, 2013 4:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
rama.king127 (4/17/2013)
A string contains 3,4 suppose I want to store 3 in one parameter and 4 in another parameter similar in c# like arr[0] and arr[1]? how can i achieve it


Will you always be passing just 2 parameters like this?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443538
Posted Wednesday, April 17, 2013 5:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
Also consider Table Value Parameters or some other kind of list shredding method.
Post #1443551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse