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.
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.
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.
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 seached around for an answer to this question but didn't have much luck. Hopefully someone can help.
I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.
Thanks for your help, Gary
Here is the web code:
Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS")) Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)
============================== CREATE procedure dbo.AddTb2FromTb1 @Tb1No nvarchar(1000) as insert into Tb2 (*) select * from Tb1 where Tb1 IN (@Tb1No) /* How to Passing an Array to a Stored Procedures ??? */ ==============================
dbo.AddTb2FromTb1 'No001' is Work ! dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
I have a stored procedure some thing like this.. When I pass empty strings to both the parameters ..it is returning all the rows from the table.
IF I pass both empty strings to @LLASTNAME_I Char(21), @DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..
CREATE procedure Ceb_Phone_Book @LLASTNAME_I Char(21), @DEPARTMENTCODE_I char(7) AS Select EM010132.DEPARTMENTCODE_I, DEPARTMENTNAME_I, LLASTNAME_I, FFIRSTNAME_I, EM010132.WORKPHONE_I, EM010132.MSTRING_I_5 FROM EM010132 INNER JOIN HR2DEP01 ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I WHERE INACTIVE = 0 AND LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%' ORDER BY LLASTNAME_I,FFIRSTNAME_I
I'm trying to pass parameters to an extended stored procedure, to noavail. I would like to pass two integers to the dll and I have thefollowing three snippets:1. The C++ portion of the dll:....declspec(dllexport) int myAddNumbers(int m, int n)....2. The creation of the extended stored procedure:EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';3. The usage:create function TestFunction()returns integerasbegindeclare @rc integerexec @rc = myAddNumbersreturn (@rc)endHow do any of the above three things need to be modified in order tomake this work?Thanks!!!
There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko
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 want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
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?
Guys I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)
create proc upGetDates as declare @start datetime , @end datetime
select@start = '2001-01-01' , @end = '2007-12-31' go
I want to reuse this proc many times for different reports and using the date values in the calling sp
an example in pseudo code would be
create proc usedates as declare@rstart datetime , @rend datetime
exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc select * from tablename where datecreated >= @rstart and datecreated < @rend go
Not sure how to 'trap' the dates from the called proc
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
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?
