Array List In DB
Feb 14, 2005Hi,
Is it possible to have a column as array in SQL Server 2000- similar to Oracle's varray. If yes, how?
rgds,
Kishore
ADVERTISEMENT
Array List In WebService
Jul 13, 2007Hi,
How i can get the FileNames that are stored in Web Server.
Dim strFiles As String = Server.MapPath("~/UploadedFiles/")
Dim dirinfo As New DirectoryInfo(strFiles)
dirinfo.GetFiles("*.doc")
The above Method Iam writing in Web Service. These files will display in a DataGrid in Windows Application.
Sort Array List
Apr 12, 2007I've got the following variables:
CurFile string, FileCount int, FileList Object
I've got a Foreach Loop Container that loops thru a list of files in a directory.
Inside the Foreach Loop Container, I have a Script Task.
In the script, I'd like to assign
FileList(FileCount) = Dts.Variables("CurFile").Value
Thus, For each file in the directory, I want to put all those files into the FileList Array Object. Then I want to sort the Array.
How would I be able to do this? I'd like the easiest way without writing to a database table.
Thanks in advanced.
Dave
For Loop List Of Array In Stored Procedure
Aug 19, 2014I would like to write a store prodecure to return a month:
My output:
Wk1 = July
Wk2 = July
Wk3 = July
Wk4 = July
Wk5 = Aug
and so on..
then i create list of array like below:
The counter for insert the week one by one
DECLARE @TotalWeek INT, @counter INT
DECLARE @WeekNo varchar, @Month varchar
SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53'
--this is weekno,if less than 4, month is july, lf less than 9, month is august and so on
SET @TotalWeek = 53
SET @counter = 1
[Code] ....
Passing A List/array To An SQL Server Stored Procedure 2005
Aug 16, 2007Hi, I m using sql 2005 as a back end in my application...
I am useing Store procedure..for my data in grid..
ALTER PROCEDURE [dbo].[ProductZoneSearct]
(
@Productid char(8),@Proname char(8),@radius int,@mode varchar(5) = 'M',@Zone nvarchar(1000),)
ASSET NOCOUNT ON;Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))
Insert #Product Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment ,Miles, UserID, Daily, PhoneFrom [tblproduct] Where city IN (@Zone)
Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment ,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone From #Product As L Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID Order By DateAvailable
if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH' then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result..
I tried to below syntax also but in no any user Where city IN ('+@Zone+')
In short if i pass value through varibale (@Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?
Please Hel[p me !!!
Thank you !!!
RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.
Jan 25, 2007All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
How Would I Send A String Array As A Integer Array?
Jun 25, 2007I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List
Jun 15, 2015So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Insert Value List Doest Not Match Column List
Apr 18, 2007HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
Items In List A That Don't Appear In List B (was Simple Query...I Think)
Jan 20, 2005Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
Select List Contains More Items Than Insert List
Mar 21, 2008I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
Insert Only New Items From A List But Get ID's For New And Existing In The List.
Feb 12, 2008Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
Array
Jul 17, 2007how can i send array from asp.net to sql?
View 3 Replies View RelatedERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.
May 22, 2006Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
Get Array Value From A Loop?
Jun 19, 2006Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...??
=====================================
DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW'
DECLARE @SEP AS NVARCHAR(5)SET @SEP = ','
WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN
-- do the rest
END
SQL Query In A VB.net Array
Jan 31, 2007I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks!
View 1 Replies View RelatedArray Problem
Apr 4, 2007Hi All,
I have this code below:Dim a As Integer = 0 While a <= myArray.Length() Conn.Open() Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @hp", Conn) UpdateCmd.Parameters.Add("@hp", SqlDbType.VarChar, 50).Value = myArray(a) UpdateCmd.ExecuteNonQuery() a = a + 1 Conn.Close() End While
BUt, When I run it, I receive error "Prepared statement '(@hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @hp, which was not supplied. "
DOes anyone have the solution???
Thanks....
Array In WHERE Clause
Aug 28, 2007Hello, how can I use an array in a WHERE clause? If I had an array saystring[] NamesArray = new string[] {"Tom", "***", "Harry"} How would I do this:string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray" Thanks in advance,Louis
View 3 Replies View RelatedDataRow Array
Jan 16, 2008Hi,
i m pretty new to this forum and c#.net
i m doin a project in c#.net
I have four values in my datarow array
for example
DataRow[] cmb;
cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows
for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}
How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
me.
Parameterized Or Array With This SQL?
Jun 2, 2008I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub
View 7 Replies View RelatedResult Set Into Array
Aug 22, 2004Hi All,
How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.
Gayathri
SqlCommand Array Help
Feb 19, 2006I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks
View 9 Replies View RelatedInline Sql With An Array
Sep 20, 2006I have data which looks like below
actid labname
100 CKS
200 CKS;HDP;LAS
I need the data to be
actid labname
200 CKS
200 HDP
200 LAS
The ; is the seperator
For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views?
select enc_id,labcnt,order_name,date_due
reate FUNCTION fn_GET_ARRAY_VALUE(
@DELIMITER VARCHAR(100),
@STRING VARCHAR(1000),
@ARRAY_POSITION INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @CURRENT_POSITION INT
DECLARE @VALUE VARCHAR(8000)
SET @CURRENT_POSITION = 0
WHILE @CURRENT_POSITION<@ARRAY_POSITION
AND CHARINDEX(@DELIMITER,@STRING,0)>0
BEGIN
SET @STRING =
SUBSTRING(@STRING,
CHARINDEX(@DELIMITER, @STRING, 0)
+LEN(@DELIMITER),
LEN(@STRING)
-CHARINDEX(@DELIMITER, @STRING, 0)
+ LEN(@DELIMITER)
)
SET @CURRENT_POSITION = @CURRENT_POSITION + 1
END
IF CHARINDEX(@DELIMITER,@STRING,0)=0
SET @VALUE = @STRING
ELSE
SET @VALUE = SUBSTRING(@STRING, 0,
CHARINDEX(@DELIMITER, @STRING, 0)
)
RETURN(LTRIM(RTRIM(@VALUE)))
END
Array In SQL Server
Jan 27, 2004How would one implement an array in SQL Server ?
Specically, i'm parsing a field into the array, and doing operations on these elements.
Thanks.
Array In A STOC??
Feb 3, 2004Hi all,
My Issue is the next thing:
I have a table filled with contracts
I have Stoc that checks if contract has expired
If I start the Stoc I must give it the contractnumber with it like:
SP_expired 8
This works fine but now I want that the Stoc checks all the contracts by just starting the Stoc.
I don't really know how to do this, I have thinking about using an array.
But don't have an idea where to start....
Thnx in advance
Dynamic Array
Jan 3, 2007Please excuse me if this is the wrong forum. I hope your combined genius will be able to point me in the right direction.
I have an ASP page that accepts any SQL query. I want to create a table based on the result set so the number of columns can grow/shrink. But I don't know how. Can anyone help me?
My ASP Code shows how I create a table with only 8 fields.
<%
Dim objConn
dim SQLstmt
Dim objRS
Dim SQLqry
Dim UID
Dim RecordArray
Dim I
UID = right(Request.ServerVariables("AUTH_USER"),8)
SQLqry = Request.Form("qry")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Trusted_Connection=yes;driver=SQL Server;server=Server;database=db;"
objConn.Open
If SQLqry = "" then
Response.Write "Please Enter a SQL Query"
Else
Set objRS = objConn.Execute(SQLqry)
Response.Write "<H5 align = ""center"">" & sqlqry & "</H5>"
Response.Write "<Table align = ""center"" border = ""1"">"
For I = 0 To objRS.Fields.Count - 1
Response.Write "<TD><B>" & objRS(I).Name & "</B></TD>"
Next
Do while not objRS.EOF
RecordArray = objRS.GetRows(30)
For I = 0 To UBound(RecordArray,2)
Response.Write "<TR>"
Response.Write "<TD>" & RecordArray(0,I) & "</TD>"
Response.Write "<TD>" & RecordArray(1,I) & "</TD>"
Response.Write "<TD>" & RecordArray(2,I) & "</TD>"
Response.Write "<TD>" & RecordArray(3,I) & "</TD>"
Response.Write "<TD>" & RecordArray(4,I) & "</TD>"
Response.Write "<TD>" & RecordArray(5,I) & "</TD>"
Response.Write "<TD>" & RecordArray(6,I) & "</TD>"
Response.Write "<TD>" & RecordArray(7,I) & "</TD>"
Response.Write "</TR>"
Next
Loop
Response.Write "</TABLE>"
objRS.close
objConn.close
set objRS=nothing
set objConn=nothing
End if
%>
Array As Parameter
Jan 28, 2008hi guys im having some problems with arrays as parameter
what i want to do is pass a array of comma separated values i.e 12,24,36 and use the in(@parameter) but it doesnt work
i've found some solutions, like
ALTER PROCEDURE [dbo].[GetLawyers] ( @LawyerTypeIDs VARCHAR(100)
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE [PARCELAS] IN (' + @LawyerTypeIDs + ')'
EXECUTE (@SQL)
but in my case doesnt work.
here is a part of my code
SELECT * FROM (
SELECT *, ROW_NUMBER()
OVER (PARTITION BY cod_banco ORDER BY coeficiente asc) AS RecID FROM tb_valor_bancos
where
(1 = CASE WHEN @TABELA IS NULL THEN 1
ELSE CASE WHEN TABELA = @TABELA THEN 1
ELSE 0
END
END
)
AND (1 = CASE WHEN @PARCELAS IS NULL THEN 1
ELSE CASE WHEN PARCELAS in(@PARCELAS) THEN 1
ELSE 0
END
END
)
...
i know there is a way to pass the array into a table but it doesnt work too
thanks
Split Array
Feb 21, 2008hi,
how can i split text separated by semicolumn in different cells:
text1;text2;text3;
into
1 - text1
2 - text2
3 - text3
thank you
How To Identify An Array
Nov 20, 2006I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
View 1 Replies View RelatedArray In TSQL
Sep 19, 2007Hi all,
I have data like this :
POHDR;JAKARTA;St.1DTL;1. ;00248337;8996006855701 ;083;041;002; 7,812.50; 312,500.00; 284,091.00; 8,875.00
first row is the PO header and the second is the detail. This data was collected using Export Import Wizard from flatfile and put it in one column. I want to split the header and the detail into different table and map each column separated by ; to a proper field. For this work I should check every column separated by ; with CHARINDEX function but I have to write the TSQL as much as the count of ;. It would be better if we can use array to simplify the code.
Can we use array in Transact SQL ? since I have no clue referring to SQLBOL. Thanks in advance.
Best regards,
Hery
2 Dimensional Array
Jan 24, 2008
Hi. Does anyone know how to pass a 2 dimensional array into a stores procedure? I need to loop through it and insert it into a table.
Thanks!
Create An Array With T-SQL
Oct 10, 2007I'm using SQL Server 2005 and have have a table similar to the following:
Name Week Role
Bob 1 Primary
Bob 2 Backup
Bob 3 Primary
Joe 1 Backup
Joe 2 Backup
Mike 1 Primary
Mike 2 Backup
Is there a t-SQL query that will combine the role and week into an array and return a table like:
Name Role
Bob Primary: 1, 3
Backup: 2
Joe Backup: 1, 2
Mike Primary: 1
Backup: 2