I want to pass my stored proc a list, and either loop through it as a list or (better) turn it into an array and loop through it that way to insert it. Psuedocode would be...
Loop from 0 to ListLength
begin
INSERT Transaction_Data
end
I am glad to announce that there is now a version of my article "Arrays andLists in SQL Server" for SQL 2005 available on my web site.THe URL for the article ishttp://www.sommarskog.se/arrays-in-sql-2005.html. If you are curious aboutthe performance numbers they are in an appendix athttp://www.sommarskog.se/arrays-in-sql-perftest.html.The old version of the aritlce remains, as the new article covers SQL 2005only. The old version is now athttp://www.sommarskog.se/arrays-in-sql-2000.html.The old URL, http://www.sommarskog.se/arrays-in-sql.html leads to a pagethat links to both articles.And the reason that there are two articles is simply that SQL 2005 addsso many new features: nvarchar(MAX), the CLR, the xml data type, CTE thatall can be used in the realm of arrays and lists.--Erland Sommarskog, SQL Server MVP, Join Bytes!Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/pr...oads/books.mspxBooks Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodin...ions/books.mspx
Hello, I am a beginner of sorts to SQL 2000/5, and wanted to know if anyone could point me in the right direction in getting a book that explains, and instructs you on writing queries? (I would also want the book to have some examples and practices).
I am trying to debug a t-SQL function. I am supposed to have both Enterprise Manager for SQL 2000, and Management Studio for SQL 2005, on my computer. In the books-online for SQL 2000 there is a reference to a T-SQL debugger which does all the things that you'd expect: checkpoints, ablility to stop and examine data, etc. I don't seem to have books-online for SQL 2005 - I merely get "help on Help" which is rather useless.Anyway, how do I get this debugger to work? I can't find anything in either Enterprise Manager or Management Studip.Thanks, Robert Barnes
This procedure will work if @Tablename is replaced by a literal. It all works except the last line when the variable @TableName is passed How can I pass a variable @Tablename and get the last line to work?
CREATE PROCEDURE spDropTable @TableName char(50) as if exists( select * from sysobjects where id = object_id(@TableName)) select "Table Exists "= @TableName drop table @TableName
Can some one point me in the right direction? I need to execute a DTS package after a set of T-SQL statements run in a stored procedure. The starting of the DTS package needs to be the last thing executed/called from the stored procedure.
hi, i'm passing a string to a Stored procedure(sp).this is string is delimited, say, mon:tue:wed..is there a way in a sp to parse this string out an store it in an array and then use a for i=i to..whatever.. loop to access each element?
I have data I am retrieving using a datareader...and SQLSERVER It could return 1 row of information or perhaps 3 rows of information I need to know how to use an array here I would guess so I can access each element in this row or rows.
HOw might I use reader.read and get it into the array
I am creating an application for booking events. Each event has several dates and each date has a fixed amount of available seats.
Currently in the events table I have a field for the number of available seats and a field for the string of dates that are later parsed into a listbox control.
The problem with my current setup is each date is sharing the same number of available seats so if date Event A is decemented 10 so will Event B.
I need some way to associate each date with it's own number of available seats. What is the best way to do this? An array? If so how do I store an array in the DB?
Hi;I have been writing a lot of short tsql scripts to fix a lot of tinydatabase issues.I was wondering if a could make an array of strings in tsql that Icould process in a loop, something likearray arrayListOfTablesToProcess = { "orders", "phone","complaints"}for( int i = 0; i < arrayListOfTablesToProcess.length; i++ )delete from arrayListOfTablesToProcess[i]Can you do something like this in tsql?I should probably stop asking all of these questions.Is there a good book on TSQL alone ( I'm not interested in wizards,just scripting )...that is short?Steve
Hi everyone My stored procedure accepts 2 parameters: names, number of names like this: (name1, name2, name3,3) Problem is: the number of names changes each time i want to run it. Some time it is: Name1,1 and other time it is: name1, name2,2. So every time i run it i need to change code. I thought "arrays" might solve that problem but i never met arrays in sql2005. Does it exist ? Case answer is "no": What is it to replace arrays and solve the above problem? Thanks
I'm have a stored procedure that iterates through a list of numbers and adds an item for each number (user id) some of these ids are duplicates which is fine even necessary for the first part of my query but for the last I need to ensure that no duplicates id's are passed to the stored procedure, in this case called 'spInsertForBackupNote'. My thoughts here was to do something like this:
SET @Note_Buffer = @UserID -- @Note_Buffer being some kind of array?
IF @Note_Buffer = @UserID -- If its been added to the buffer we dont execute sp BEGIN Do Nothing here END
ELSE
BEGIN EXECUTE spInsertForBackupNote @FK_UserID, @FK_NoteID END
I know this would never work because it would always be false since I just added the same userid to the buffer that I want to add. But I think you see my problem. I know it should be an easy one but my TSQL is limited. I've posted the whole sp. Hope someone can help.
Can any one help me with a sample code, which can take an array of elements as one of it's parameters and get the value inserted into a table in a stored procedure.
I'm newer to MS SQL, but have a programming background, so I'm going to try to describe what I'm doing in that sense.
What I want to do is pull an entire column of data from one table, and insert it into a column in another table. Typically, I would do this with an array and while loop or something similar. I've figured out how to do a while loop in SQL, but the array situation has me stumped. I tried reading http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx that article, however it referred to a procedure, and I have no background or experience with procedures.
Any sort of idea or document on a technique to do this would be most appreciated, thank you!
Hi, I'm using Visual Studio and Visual Basic to save data about members in a database. I use SQL-database as it's included. However, I'm new to this saving in the datbase and wonder how I can at the best save data about a member without having to index each value in the arrays: The array has 2 index and is declared dim Results(6, 50) as string Of course, I can do results11, results12,... results150, results21, ...results250.... But it seems rather awkward to do it this way. I'm sure that there is a better way. Thanks, any assistance is appriciated! Best wishes, Per
SelectCommand="SELECT * FROM Table1 WHERE Field1 IN @target"
And my parameter looks like this:
<asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" /> This code gives me a syntax error near @target. Someone got a solution?
i want to know how i can pass multiple values in the form of arrays to a stored procedures.
the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:
Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader() Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand() Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection() Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand() ' 'cmdInsSlabHmst ' Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST" Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1 Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50)) Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1)) Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar)) Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar)) Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar)) Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer)) ' 'OleDbConnection1 ' Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)
'Passing multiple values to the procedure with the help of ~ sign
Dim strCode As String Dim i As Integer 'Dim dblSlabRate As Decimal 'Dim dblSlabFrom As Decimal Dim strSlabRate As String Dim strSlabFrom As String Dim strSlabRateP As String Dim strSlabFromP As String Dim intCntr As Integer 'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text 'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value 'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text 'OleDbConnection1.Open() 'strCode = cmdInsSlabHmst.ExecuteScalar 'OleDbConnection1.Close() For i = 0 To dgSlabDtl.Items.Count - 1 If i = dgSlabDtl.Items.Count - 1 Then 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text Else 'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text 'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text End If strSlabRateP += strSlabRate & "~" strSlabFromP += strSlabFrom & "~" intCntr += 1 'If dblSlabRate <> "" And dblSlabFrom <> "" Then 'InsDtl(strCode, dblSlabFrom, dblSlabRate) 'End If Next If strSlabRateP <> "" And strSlabFrom <> "" Then With cmdInsSlabHmst .Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text) .Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value .Parameters("iREMARKS").Value = txtRemarks.Text .Parameters("iSLABFROM").Value = strSlabFromP .Parameters("iSLABRATE").Value = strSlabRateP .Parameters("iNOOFRECORDS").Value = intCntr End With OleDbConnection1.Open() cmdInsSlabHmst.ExecuteNonQuery() OleDbConnection1.Close() End If
to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.
i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.
Someone recently tried to tell me that it is possible to pass an array to a stored procedure.
I have tried creating procedures with the 'table' datatype for the parameters but the attempts fail with a syntax error.
We currently workaround this limitation by passing the equivalent of an array to temporary tables and selecting from those tables as needed within our stored procedure but if we can circumvent this by passing an array, we'd definetly like to try that instead.
My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...
I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.
In the [Audit] table I can find all the field changes for the record in question using this SQL...
select * from audit where rowid = 1309606 order by auditID asc
My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...
So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.
What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?
Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.
Hope that makes sense and any help would be great!
How do you all recommend storing ordered pairs in SQL Server 2005? I plan to add one record for every data point but this will generate many records and requires an extra field to relate the points together. Are there any better ways to do this? Can the data still be searchable or does it have to be unpacked first?
I'm getting ready to install SQL Server 2005 Enterprise for the first time and I have a question about the directory location of the log files and the data files. I have 3 RAID arrays on my server, 1 for the OS, 1 for SQL Logs, and 1 for SQL Data.
Here's my issue. I want to install the logs on the Log array and the SQL data on the SQL data array, however, during the installation I can't find anything that allows me to select certain directories!
I have a bunch of SPs that all rely on a UDF that parses a commadelimitted list of numbers into a table. Everything was working fine,but now my application is growing and I'm starting to approach the 8000character limit of the varChar variable used to store the list.I would like to change the UDF only and avoid having to dig through allof my stored procedures. I was hoping to use the text datatype toallow for much larger lists, but I am unable to perform anymanipulations necessary to parse the list into a table. I have triedPATINDEX, but it alone is not enough without the text maniuplations andI don't think the sp_xml_preparedocument can be used in a UDF.Anyone with any thoughts on managing large arrays in t-sql?thanks,Matt Weiner
I know that SQL Server itself does not support passing arrays to its procedures. But I need an alternative that will allow me to "duplicate" the same functionality.
I have the following information stored in a Class:
UserInfo: Where I keep the variables 1-12 PageInfo: Where I keep variable 13 (the list of pages)
I need to store this information in my SQL Server Database. So far I found three possible methods but I'm not sure which one has the better performance:
First Method: the easy one 1. Call the SaveUserInfo stored procedure 2. Loop through the pages array and call the SavePageInfo stored procedure for each page item in the array
Second Method: Passing a delimited string to the stored procedure 1. Call the SaveUserInfo stored procedure 2. Pass a delimeted string to the SavePageInfo stored procedure. The stored procedure will split the string and save the pages into the database. The string would look like this:
Third Method: Passing and XML File to the stored procedure The stored procedure will read the XML file and store the information into the database.
What method is the best for performance?? As you may see this is for tracking the user navigation through the website. For the first method I worry about the number of call to the database; for the Second and Third method I worry about the lenght of the string or XML file to pass to the stored procedure.
I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".
Hi AllWe are running SQL Server & Outlook with an exchange server. We arelooking for a way to import the address book & email details into adatabase table.We have managed to do this with MS Access by using the import optionexchange(). Is there a similar way this can be done in SQLServer 2000
I'm new to SQL. I don't understand yet how to add if statements and other scripting techniques. But this is what I am trying to do...
I have a list of IDs (1, 2, 3, 4, 5, 6, 7, 8). I want to query a table and then display which of those IDs are NOT in that table. It seems like it should be easy, but I don't know how to do it.