Using A Function To Pass A Parameter To A Stored Procedure
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>
View Complete Forum Thread with Replies
Related Forum Messages:
Can't Pass 0 In Stored Procedure Parameter
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.
View Replies !
Pass A Parameter To A Stored Procedure In Asp:SqlDataSource
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.
View Replies !
Pass Sort Parameter In Stored Procedure
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
View Replies !
How To Pass Xml File Name As Parameter To Stored Procedure
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 Replies !
HOw To Pass The Null Value To The Parameter Of The Stored Procedure
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
View Replies !
How To Pass Text Parameter To Stored Procedure?
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!
View Replies !
Pass Null To The Parameter In The Stored Procedure
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
View Replies !
How To Pass A Parameter Which Includes More Than One Value To A Stored Procedure?
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.
View Replies !
Calling A Stored Procedure And Pass Parameter One By One
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
View Replies !
How To Pass A XML Data Parameter To An SQL 2005 Stored Procedure
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-------------------------------------
View Replies !
Pass Parameter Values To Stored Procedure In Dataset
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.
View Replies !
Call SSIS Package From Stored Procedure And Pass Parameter
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.
View Replies !
Pass Multivalue Parameter To A Stored Procedure For Integer Lookup
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?
View Replies !
How To Pass A Null Parameter To A Stored Procedure Executed Within A SSIS Package?
I have a SSIS package that I am trying to execute a stored procedure (the stored procedure references a linked server) within a execute sql object. The stored procedure has two parameters. I am passing the one parameter to the SSIS package upon execution. The second parameter is an output paramter. When I execute the SP normally through SSMS, I just pass a null value for the second parameter. I can't figure out how to get this to work with the execute sql task. I created a parameter in the parameter mapping tab. Not knowing exactly how to do it, I created a new string variable type & left the value empty. I then set it to be an output direction. I set the datatype to be varchar & just called it param2. When I execute the sp, I use the following syntax: exec sproc ?,?. I get the following errors: OLE DB Provider "SQLNCLI" for linked server returned message "No transcation is active.". Executing the query exec sproc ?,? failed "the operation could not be performed because OLE DB provider "SQLNCLI" for linked server was unable to begin a distributed transation. Possible failure reasons: problems with the query, resultset property not set correctly, parameters not set correctly. I have the linked server setup correctly & I am able to execute the SP through SSMS. Any help would be appreciated! Let me know if you need more clarification. I can also forward my SSIS package for you to look at if you would be willing. Thanks! John
View Replies !
Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; } As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help! Thanks Nickdel68
View Replies !
How To Pass Parameter In Max Aggregate Function
i have on table temp and it has column name fhID, now that column name and table name comes as parameter in stored procedure from c#. now stored procedure has code somthing like below @column nvarchar(50) @tbname nvarchar(50) -- both are parameter comes from c# decalre @maxid select @maxid=max(@column) from @tbname rather than prints the maximum values of "fhid" it prints column name itself. now i how can i achive actual working of max function using parameter. is there any other way to pass parameter in aggregate function or it is not allowed to pass.??? plz help me, thanks vishal parekh 'fhid' now how can
View Replies !
Pass Table As A Parameter To A Function
Hi Friends,Is it possible to pass a table as a parameter to a funtion.whos function declaration would look some thing like this....ALTER FUNCTION TempFunction (@TempTable TABLE, @nPId INT)my problem is: i have to access a temporary table created in an SP ina functionALTER PROCEDURE MySPBEGIN....DECLARE @TmpTable TABLE(...)....TempFunction(@TmpTable)....ENDThanksArunDhaJ
View Replies !
Pass File Name As A Parameter In Procedure
Hi Everyone, I tried to pass file name as a parameter in procedure, but it did work. Here are the codes create procedure spImport @filename varchar(100) as bulk insert mytable from @filename end I received following error message Msg 102, Level 15, State 1, Procedure spImport, Line 4 Incorrect syntax near '@filename' Could anybody help me to correct it. Thanks in advance. Kevin
View Replies !
Pass Sorting Parameter To Stores Procedure
I used Datagrid to show "Title", "Location" and "Date", It works very well. I want to sort DataGrid data, that is when user click the "Title", "Location" or "Date", my asp.net code will through class and send "Sort" parameter to stores procedure to get the new data and bind to DataGrid. Here is my stores procedure: CREATE Procedure JobSearch ( @Search varchar(150), @Sort varchar(50) ) AS SELECT JobTitle, JobLocationCity, JobLocationState, PostDate FROM Job WHERE JobTitle LIKE '%' + @Search + '%' OR JobKeywords LIKE '%' + @Search + '%' IF @Sort = "Title" ORDER BY JobTitle IF @Sort = "Location" ORDER BY JobLocationState, JobLocationCity IF @Sort = "PostDate" ORDER BY PostDate DESC When I test stores procedure in SQL Server, I got the error about "Error 156: Incorrect syntax near the keyword 'ORDER' ". Who has experience about stores procedure, please help me to correct this error.
View Replies !
How To Pass Array (query Parameter) Into Stored Pr
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
View Replies !
How To Pass Profile Userid Into Stored Proc As A Parameter
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
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
How To Pass A Variable To The Stored Procedure?
Hi, i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3) My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens: comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details Maybe is my stored procedure wrong? Thanks T. Here is it:---------- ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN SET @iLoopNumber = 1 SET @amountr While (@iLoopNumber <= @amount) BEGIN INSERT INTO table(field1,...) VALUES (....)) SET @iLoopNumber = @iLoopNumber +1 End COMMIT TRAN
View Replies !
How To Pass Xml File To A Stored Procedure
hi,i am passing a xml file name to the stored procedure. the SP parses the file. but it is giving the error' INVALID AT THE TOP LEVEL OF THE DOCUMENT 'I expect this because of + and - in the xml file bafore the parent tags.how can i do the parser to eliminate these.
View Replies !
How Do I Use Pass Data To/from Stored Procedure
Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob
View Replies !
Pass A Csv List To Stored Procedure
I would like to pass a list of ids to my stored proc for use in a statment like: SELECT pr.name where pr.id IN ('23,25,27') FROM profiles pr so that the list following the IN statement is a replaceable parameter. How do I declare the parameter in my stored procedure?
View Replies !
How Array Will Pass To Stored Procedure
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 Replies !
How To Pass DateTime To A Stored Procedure
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName". Code Snippet public static string GetWithDate(string date) { string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); XmlDocument xmlDoc = new XmlDocument(); SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++); cmd.Parameters.Add(new SqlParameter("@date", date)); try { cmd.ExecuteReader(); } catch (Exception ex) { Console.WriteLine(ex.Message); } SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs if (conn != null) conn.Close(); return xmlDoc.InnerXml; } I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View Replies !
VB.NET Stored Procedure, Can't Pass Param
Hello, I have a VB.NET stored procedure as below: Code Snippet Partial Public Class StoredProcedures Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer) 'here I perform update statement using "paramInOut" passed form calling code ....... 'then I return value to the calling code paramInOut = 555 End Sub End Class Calling code specifies a parameter like this: Code Snippet Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int) param.Direction = ParameterDirection.InputOutput param.Value = 999 cmd.Parameters.Add(param) When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ? Any help would be appreciated. Thanks a lot,Fly.
View Replies !
Pass XML To Stored Procedure In SQL Server2000
Hi, I want to pass an xml file to stored procedure in SQL Server 2000 as a parameter. Can we pass the xml file path as a parameter? if not then how can it be done. I want to serialize data to XML and then pass it to a procedure to insert data. Kindly help ASAP. Thanks
View Replies !
Stored Procedure With User!UserID As Parameter, As Report Parameter?
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure. Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
View Replies !
How To Pass Pass The Parameter In SQL Command In SSIS Package
Hi We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment? Query 1: (source View Query) SELECT V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID FROM V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE WHERE REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' ) Query 2: (look up ) SELECT GDR_PRODUCT_ID, TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') , TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS') FROM V_RDP_GOLD_PRICE where GDR_PRODUCT_ID = :1 and report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS') please anyone give the sample control flow and how to pass the parameter? Thanks & regards Jeyakumar.M
View Replies !
How To Pass Values For The In Clause To The Stored Procedure?
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
View Replies !
Pass Multiple Parameters To Stored Procedure
Hi, I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample, CREATE PROCEDURE [dbo].[display_customer] @agentID varchar(20), @customer1 varchar(20), @customer2 varchar(20), ..... -- Here I do know how many customers for each agent AS SELECT name, city, state, zip FROM rep_customer WHERE agent = @agentID and (name = @customer1 or name = @customer2) Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure? Thanks a lot!
View Replies !
How To Pass Parameters To Stored Procedure That Use SQLDataSpurce
I have stored procedure that expects 2 input parameters (@UserID uniqidentifier and @TeamID int). My datasource is SQLDataSource. So i need to pass parameters to SP..When i add parameters ans execute i got an error "....stored procedure expects @TeamId parameter, which was not supplied" But i pass them. How should i pass parameters? Maybe the reason is some mismatching of parametrs. 1 Parameter [] param = new Parameter[2]; 2 3 param[0] = new Parameter(); 4 param[0].Name = "@TeamID"; 5 param[0].Type = TypeCode.Int32; 6 param[0].Direction = ParameterDirection.Input; 7 param[0].DefaultValue = "1"; 8 9 param[1] = new Parameter(); 10 param[1].Name = "@UserID"; 11 param[1].Direction = ParameterDirection.Input; 12 param[1].DefaultValue = "edf26fd8-d7cd-4b32-a18a-fc888cac63ef"; 13 param[1].Type = TypeCode.String; 14 15 dataSource = new SqlDataSource(); 16 dataSource.ID = "Source"; 17 dataSource.ConnectionString = settings.ToString(); 18 19 dataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; 20 dataSource.DataSourceMode = SqlDataSourceMode.DataReader; 21 dataSource.SelectCommand = "dbo.GetAprfAssessmentTeamData"; 22 23 24 dataSource.SelectParameters.Add(param[0]); 25 dataSource.SelectParameters.Add(param[1]); 26
View Replies !
How To Pass XML File To Stored Procedure (Urgent)
i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
View Replies !
Can't Pass Search Text Into Stored Procedure
I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @VisitorKeywords. PROCEDURE dbo.KWsearchAS SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( @visitorKeywords ) AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name I am wanting the resulting WHERE portion to be: WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1) Thank you
View Replies !
How To Pass Table Names To A Stored Procedure
Hi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi
View Replies !
How To Pass Values To A Calling Stored Procedure
Currently i am working in a project of report generation in MS ACCESS. The tables are in sql server 2000. I have to write stored proc in ms access. Illustration: I am having a stored proc as follows name: myproc ------------------- Create procedure my_proc @f1 char(1), @f2 char(5) As select * from table1 where field1=@f1 and field2=@f2 ________________________________________________ and calling proc name: call_myproc execute my_proc 'A','2004' If i am getting the vales of field1/@f1 and field2/@f2 from forms in ms access. I have to get the values from forms in ms access. I have to write the calling proc as follows my_proc [forms]![form_a].[Combo4],[forms]![form_a].[text12] But ms access throws syntax error. How is it possible to pass values from ms access FORMS to a calling stored procedure. I have followed the way of creating and executing the stored procedure as given in the article as follows. http://www.databasejournal.com/features/msaccess/article.php/10895_3363511_1 As per the given link. They did not give values dynamically. could you please help me to fix this problem ? regards, Krishna
View Replies !
|
TRACKER
