how to pass array(query parameter) into stored procudure. with this array i need to retrive data,with another array i have to retrive another set of data.
eg: suppose @param1 contains chapter1,Chapter2,Chapter3,
@param2 contains unit1.1,unit2.1,unit2.2,unit3.1,unit3.6
how can i do in stored procedure
love all
I am trying to find out a way to pass an array of pbaseid's to a function.I have a function defind something like this
Fport(@portfoliobaseid, @reportingcurr, @rowno,@todate)
(
returning a table
)
trying to execute it like this but its giving error related to subquery must return single value
select * from Fport((select pbaseid from dbo.pbaseid),'us',1,'12/31/2013')
I think i need to find a way to pass this pbaseid one by one but i dont know how to do this ...
I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.
View 3 Replies View RelatedIn my front end, I have a procedure in which the operator selects multiple tickets. I have in mind to create a stored procedure which accepts its input parameter and plugs it into an IN() clause in an Update statement, something along these lines:
Update TicketInventory Set SalesID = 12345 Where TicketInventoryID IN( myInputParam );
Can I pass an array of Integers into a stored proc and have it work like this? Alternatively, I could pass a comma-delimited string containing the Integers. Could I plug that into the IN()? Or is there is yet another way to accomplish this?
TIA,
Arthur
I thought that I had read somewhere that SQL 2000 allowed you to pass an array into a stored procedure. I've tried to look up some additional information pertaining to this but I haven't found anything. Did I dream this or can you pass an array to a stored procedure with SQL 2000? If you can please give me some information on this or where I can find some.
Thanks!
Can you pass an array into a stored proc or do we have to use indiviual parameters.
If we can, any code exmaple woudl be great.
Cheers
Chris
Dear All,
I am using sql2000, I want to know whether in stored procedure we can pass
array. Or is there any other solution to pass array of records
Please Guide Me
thanks
Hi I have an if clause in my code to add the final parameter value to send to the database.
If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If
If the user is in the role, the error is triggered saying that @C_ID
is expected by the stored procedure. If i then change the value from 0
to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.
How can I pass a parameter to a stored procedure using Visual Web Developer 2005? I have created a SQLDataSource that calls the SP.
Thanks
--R
How can I pass a parameter to a stored Procedure from another stored procedure in SQL 2005?
Thnak you,
I have the following function that is run when a person
enters a value, "Cell", in an access form. I am trying to
execute the pass through query in SQL Server but i keep
getting the following message:
"The microsoft jet database engine cannot find the input
table or query 'CellDefs'. Make sure it exists and its name
is spelt correctly."
There is nothing wrong with the table name.
I cant understand what the problem is ?????
Can anyone tell me how to execute a query that passes
a parameter from access to Sql server.
Thanks,
Ruaidhri
Public Function GetData(Cell As String)
Dim dbsCurrent As Database
Dim qd1 As QueryDef
Dim SQL As String
Dim rs As Recordset
Dim qdf As QueryDef
Set dbsCurrent = CurrentDb
Set qd1 = dbsCurrent.CreateQueryDef("")
With qd1
.Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS"
SQL = ""
SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber"
SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON "
SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON "
SQL = SQL + "B.Finders_id = C.Finders_id "
SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' "
SQL = SQL + "ORDER BY C.FinderNumber"
.ReturnsRecords = False
.ODBCTimeout = 2400
dbsCurrent.Execute (SQL)
End With
dbsCurrent.Close
End Function
In the snippet below, ExecuteSqlString is a stored procedure that accepts one parameter. SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass. I can't seem to find the correct syntax to get it to work. The way it is show below, the error comes back about incorrect syntax near ')' . Is this doable?
<asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"
DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>"
SelectCommandType=StoredProcedure
SelectCommand="ExecuteSqlString">
<selectparameters>
<asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/>
</selectparameters>
</asp:SqlDataSource>
hi,
i searched a lot to find how to pass an orderBy parameter finally i used a case block in my code and it works now how can i add a second parameter for ascending and descending order(@sortAscOrDesc)
when i use it after the end of case statement i get error
here is my sp:CREATE PROCEDURE [userPhotos]
@userID int,@orderBy varchar(100)
ASSELECT ID,UserID,Photo,ALbumID,Title,views,date_added from userAlbumPic where userID=@userID and albumID=0 order by
case @orderBy
when 'date_added' then date_added
when 'views' then [views]
else date_added
end
GO
Either method is in the “ASPX� file
This is a DataSource for a “DetailsView� which has on top of “DeleteCommand� an “InsertCommand� a “SelectCommand� and an “UpdateCommand�. It is related to a GridView and the “@DonationRecID� comes from this GridView.
Method 1. Using an SQL Query – this works fine <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="DELETE FROM [Donations] WHERE [DonationRecID] = @DonationRecID">
Method 2. – using a stored procedure – this bombs because I have no clue as to how to pass “@DonationRecID� to the stored procedure "Donations_Delete". <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="Donations_Delete" DeleteCommandType="StoredProcedure"> How do I pass “@DonationRecID� to the "Donations_Delete" stored procedure?
Does anyone have an example of how I can do this in the “ASPX.CS� file instead.
sSQL = "spBPT_Fuel_Set_Status_Approved"
cmdDailyPrices.CommandText = sSQL
cmdDailyPrices.Parameters.Add("@user", "Philippe")
cmdDailyPrices.Parameters.Add("@verbose", "0")
cmdDailyPrices.Parameters.Add("@Day_1_add", rowBand1.Cells(DayParameters.AddFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_1_multiply", rowBand1.Cells(DayParameters.MultiplyFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_2_add", "NULL")
cmdDailyPrices.Parameters.Add("@Day_2_multiply", "NULL")
For @Day_2_add and @Day_2_multiply parameters I want to pass the value as NULL not string "NULL"
could you please let me know how to do this?
Thanks
hi,i have created a stored procedure to read xml dataCREATE PROCEDURE InsertXML(@xml varchar(1000)) AS DECLARE @XmlHandle intEXEC sp_xml_preparedocument @XmlHandle output,@xmlinsert into Employee(Name,ID,Sal,Address) (SELECT Name,ID,Sal,AddressFROM OPENXML (@XmlHandle, 'emp:EmployeeDetails/emp:Employee',2) WITH (Name varchar(30) 'Name', ID int 'ID', Sal int 'sal', Address varchar(30) 'Address'))EXECUTE sp_xml_removedocument @XmlHandlebut it is taking only xml text as input.but i want to send the file name as input.how to do it.
View 2 Replies View Relatedthis is the error ...
An error has occured during report processing.
Query execution failed for dataset 'dataset name'
Procedure 'procedure name ' expects parameter '@StartDate', which was not supplied
Hi there,
I am using SQL Reporting Services to generate reports. I am calling the stored procedure from the reporting services. The procedure has parameters which take null. I am stuck with passing null to the parameter from the reporting services. I shows the error and the report is not generated. Could you please suggest the way to pass null to the stored procedure parameter from the SQL Reporting Services.
Kindly reply me with the possible solution ASAP.
Thanks in advance
Hello,
I was wondering if you could help resolve a simple question - namely how to input a type text value as a parameter to a stored procedure, which expects that type of input.
Text type variables are not allowed and casting to varchar in this case will not work as the input will be far longer than 8000 characters.
Thanks!
Hi,
I need to create a batch process which calls a stored procedure.
Here's the scenario.
I have 3 tables
Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId
There is a stored procedure spoc_updateTheater that accepts the state and location id and runs a set of sql statements against the theater table. However i want this to run for all the locations in a state one by one. There are some 700 locations in 45 states. How do i pass the location and state id one by one to the stored proc. Can i call this from a commandline or run it as batch process?
vidkshi
Hello!
I am wondering if some of you T-SQL pros encountered a situation when you have a parameter that can consist of multiple strings. For instance, I have a stored procedure called dbo.usp_CalculateHeadcount that accepts two parameters such as @Term, and @AcadLevel.
It works great when my parameters are two single strings; 'Fall2007', 'UG'. But let say I have one more term such as 'Fall2006' and want to pass it to a stored procedure, the problems start to appear.
So when you execute a stored procedure:
Exec dbo.usp_CalculateHeadcount 'Fall2006','Fall2007','UG'
It doesn't work because I have added an extra string and stored procedure thinks it is another parameter. Is there a way to handle problems like the one above?
Thanks for your feedback.
just getting started with my first db driven web project...
I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.
How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:
<asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>" ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>" SelectCommand="SELECT c.CourseName FROM courses c, enrolments e WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource>
<asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses"> <ItemTemplate> <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br /> </ItemTemplate></asp:DataList> </p>
However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.
Suggestions pleeeeeese!!
Hi, I have created an insert stored procedure which inserts a userid into an sql server 2005 field of datatype uniqueidentifier.
What datatype would the parameter be in my c# code?
do i pass it in as an object datatype?
the code is below....
1 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
2
3 cmd = new SqlCommand("spInsKeyswap_history", conn);
4
5 cmd.CommandType = CommandType.StoredProcedure;
6
7 MembershipUser mu = Membership.GetUser(User.Identity.Name);
8
9 object gdUserID = mu.ProviderUserKey;
10
11
12
13
14
15 cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
16
17 cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
18
19 cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
20
21 cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
22
23 cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
24
25 cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
26
27 cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
28
29 cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
30
31 cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
32
33 cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
34
35 cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
36
37 cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
38
39 cmd.Parameters.Add("@status_code", SqlDbType.Int);
40
41 cmd.Parameters.Add("@support_id", SqlDbType.Int);
42
43 cmd.Parameters.Add("@return_value", SqlDbType.Int);
44
45 cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
46
47 cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
48
49 cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
50
51 cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
52
53 cmd.Parameters["@birthday"].Value = txtBirthday.Text;
54
55 cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
56
57 cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
58
59 cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
60
61 cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
62
63 cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
64
65 cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
66
67 //get logged in users user_id from membership
68
69 cmd.Parameters["@dealer_id"].Value = gdUserID;
70
71 cmd.Parameters["@status_code"].Value = 1;
72
73 cmd.Parameters["@support_id"].Value = 0;
74
75
76
77 cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
78
79 cmd.ExecuteNonQuery();
80
81 int returnValue = (int)cmd.Parameters["@return_value"].Value;
82
83 conn.Open();
84
85
86
How to pass a XML data parameter to an SQL 2005 Stored Procedure
I hope to insert a xml data into an typed xml column in SQL 2005.
1. I can run the Code 1 correctly.
2. I hope that I can pass a XML data parameter to an SQL 2005 Stored Procedure, So create the Code 2. but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: Only non-document nodes can be inserted. Found "xs:string ?".
3. I create the Code 3, but I get the error below:XQuery [cw_bookmark.Bookmark.modify()]: ',' or ')' expected
4. I create the Code 4, but I get the error below:XQuery: SQL type 'xml' is not supported in XQuery.
//--------------------------Code 1-------------------------------------create procedure Hellocw_InsertBookmark40@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert <x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 1-------------------------------------
//--------------------------Code 2-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 2-------------------------------------
//--------------------------Code 3-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml varchar(80)='<x:Bookmark Id="ghdce3ak-456c-4e38-ab2f-5h02d9711b67" Title="cw" Url="kk" Description="Thte" InputDate="2004-08-12" IsPrivate="false"></x:Bookmark>'asupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert cast(sql:variable("@Insertxml") as xml)as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId//--------------------------Code 3-------------------------------------
//--------------------------Code 4-------------------------------------create procedure Hellocw_InsertBookmark41@userId varchar(80)='61809B69-4AD5-40E4-B456-D957C78DD99E',@Id varchar(80)='a6dce8fe-749c-4e38-ab2f-3d03d9711b3d',@Insertxml xmlasupdate cw_bookmark set Bookmark.modify('declare namespace x="http://www.hellocw.com/onlinebookmark";insert sql:variable("@Insertxml")as first into (//x:*[@Id=sql:variable("@Id")])[1]')where userId=@userId
//--------------------------Code 4-------------------------------------
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
In the dataset, I typed
=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")
I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.
Thanks.
I got issue when passing multiple values to a single parameter. Here is my stored procedure as following:
CREATE PROCEDURE [dbo].[School]
@Grade AS varchar(50),
@Class As varchar(50)
AS
BEGIN
SELECT Name, Grade, Class
FROM School
WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class ))
END
In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter.
I have a table with the following structure:
Code:
[tabid] [int] IDENTITY(1,1) NOT NULL,
[tabname] [nvarchar](50) NULL,
[description] [nvarchar](50) NULL,
[url] [nvarchar](50) NULL,
[parent] [int] NULL,
[Code] ....
I wrote a stored procedure that takes a string of values, seperated by semicolon as parameter. The procedure is below;
Code:
ALTER PROCEDURE [dbo].[selectUserTabsByRoles]
@var varchar(max)
AS
BEGIN
SELECT distinct * from tbl_tabs
where ( PATINDEX('%'+left(@var,1)+'%', roles) > 0
or PATINDEX('%'+right(@var,1)+'%', roles) > 0 ) AND parent is null and tabstatus =1
ORDER BY tabposition
END
My problem is, when I pass a parameter like 1; it fetches all rows with roles having 1. But I realised that the last row in the sample data does not have 1 as roles, but rather 11.
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)
I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
I'd like to pass a multi-value parameter to a stored proc to satisfy an integer field lookup.
example -
CREATE PROC SSRSQuery
@InPublicationId VARCHAR(500) = NULL AS
SELECT * from Table where PublicationId IN (@InPublicationId)
where PublicationId is defined as an int
I've seen various posts on how to split up the input string parameter to use in a string-based lookup but nothing useful for an integer-based lookup.
Any tips or tricks for this?
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies View RelatedIs it possible to use a parameter in a ldap query using the ADsDSOObject provider? I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider". Command parameters are unsupported with the current provider.
I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design. Here is my query for my dataset. If I hard code an example it works. Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of. Is this possible? or do I just need to push for a linked server?
="SELECT sAMAccountName, displayName, distinguishedName " +
"FROM 'LDAP://DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' " +
"WHERE objectCategory = 'Person' " +
"AND objectClass = 'user' " +
"AND memberOf = '" + @GlobalGroup + "'"
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
View 3 Replies View Related