Stored Procedure Not Getting A Parameter
I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure.
When I run the update from the app the error is that the procedure is not getting the @Phrase parameter.
Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]
@paymentID int,
@PaymentDate datetime,
@TenderTypeID int,
@PaymentAmount money,
@CreditCardType int,
@ExpirationDate datetime,
@ccNumber nvarchar(50),
@Phrase nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
SET @Phrase = (SELECT Phrase FROM tblSpecialStuff)
UPDATE tblReceipts SET
PaymentDate=@PaymentDate,
TenderTypeID=@TenderTypeID,
AmountPaid=@PaymentAmount,
CreditCardType=@CreditCardType,
ExpirationDate=@ExpirationDate,
ccNumber = (CASE
WHEN @CreditCardType > 0 THEN
EncryptByPassPhrase(@Phrase,@ccNumber)
ELSE @ccNumber
END)
END
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
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 !
View Related
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 !
View Related
RS 2005: Stored Procedure With Parameter It Runs In The &&"Data&&" Tab But The Report Parameter Is Not Passed To It
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure. I have to display the result of a parameterized query and I created a SP that based in the parameter does something: SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= '' AS BEGIN declare @slqSelectQuery nvarchar(MAX); SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed. Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!! What is wrong? What I forgot?? Thankx for any help! Marina B.
View Replies !
View Related
Parameter For Stored Procedure
HiI'm trying to alter my stored procedure to take a parameter for theDatabase Name, but as usual the syntax is killing me.Thanks for any helpDennis'--------------------------------------------------------------------------*--------------------------------Before - This Works without a paramater'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE [MyClientDatabase] ON PRIMARY( NAME = N'MyClientDatabase',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'MyClientDatabase_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--------------------------------------------------------------------------*--------------------------------After - This Doesn't work with a parameter'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE @ClientDBName ON PRIMARY( NAME = N@ClientDBName,FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'@ClientDBName' + '_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASMsg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4Incorrect syntax near '@ClientDBName'.
View Replies !
View Related
Stored Procedure Parameter
In the Create portion of a stored procedure, does the line in bold mean that if the parameter @RegoDate is not provided, then use the current datetime? CREATE PROCEDURE spGetRegoDetail @Owner nvarchar(20), @RegoDate datetime = getdate AS ... Thanks in advance, G11DB
View Replies !
View Related
Stored Procedure Parameter
Hi, I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ). I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...) I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones ) I'm really not sure as to how to accomplish this... Any ideas? thanks!
View Replies !
View Related
Parameter To Stored Procedure
Hello! I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I don´t know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... ) How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work) CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar) AS BEGIN SELECT Customer FROM Customer WHERE CustomerId IN (@sCustomerId) END /Fredrik
View Replies !
View Related
Using A Parameter In A Stored Procedure
hi, i need to run a stored procedure from an asp page that will import data into a temporary table. i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'. I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>' this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter. can anyone help please? TIA
View Replies !
View Related
Stored Procedure With A Parameter
Hi all, I'm trying to create a SP with a parameter for server. CREATE PROCEDURE dbo.sp_Testing @server ??????? AS SELECT * from @server GO Does anyone know how to do what i'm trying here..??? I don't know the type of the parameter...is it just simply server? or is there another specific name for it??? thanks,
View Replies !
View Related
Stored Procedure && Output Parameter
I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have: IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL DROP PROCEDURE dbEvent GO CREATE PROCEDURE @Name varchar(200) ,@Location varchar(200) AS INSERT INTO Event ( NAME , LOCATION ) VALUES ( NAME , @LOCATION ) SELECT SCOPE_IDENTITY() And my code behind: public Int64 Insert(SqlConnection Conn) { try { using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output; Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName; Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location; if (Conn.State != ConnectionState.Open) Conn.Open(); Command.ExecuteNonQuery(); Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString()); return _requestId; } I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value. Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
View Replies !
View Related
Stored Procedure Output Parameter
I have two stored procedures one generates an output parameter that I then use in the second stored procedure. 1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4 5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14 15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut" 17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20 21 22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " " 30 myCommand.Parameters("@IDFieldOut").Value = 031 32 myCommand.ExecuteNonQuery()33 34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35 If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
View Replies !
View Related
Temp Parameter Value In A Stored Procedure
How do I assign a temp value to a parameter in a stored procedure? I am trying like so: Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar = "CQ2"AS SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO What is the correct way to assign a temp value for testing purposes in SQL Server 2000 Query Analyzer? When I try: Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar AS Set @partNumber = "10-AF40-N04B-JZ"SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO I get error: Invalid column name '10-AF40-N04B-JZ'.
View Replies !
View Related
Return Stored Procedure Parameter
I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e) { /*variables need for the update of the old staff record and the creation of the new staff record. */ BMSUser bmsUser = (BMSUser)Session["bmsUser"]; int staffid = Convert.ToInt32(bmsUser.CmsStaffID); int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]); string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text); int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value); SqlConnection conn = new SqlConnection(connect); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_BMS_MoveStaffMember"; cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int); cmd.Parameters["@OldStaffProfileID"].Precision = 9; cmd.Parameters["@OldStaffProfileID"].Scale = 0; cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID; cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int); cmd.Parameters["@NewManagerProfileID"].Precision = 9; cmd.Parameters["@NewManagerProfileID"].Scale = 0; cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID; cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int); cmd.Parameters["@LastModifiedBy"].Precision = 9; cmd.Parameters["@LastModifiedBy"].Scale = 0; cmd.Parameters["@LastModifiedBy"].Value = staffid; SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int); sp.Direction = ParameterDirection.Output; cmd.Parameters.Add(sp); lbl_ERROR.Text = Convert.ToString(sp); conn.Open(); SqlTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans; cmd.ExecuteNonQuery(); conn.Close(); My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null. Here is the parts of my stored proc returning the value... DECLARE @OLDManagerReturn numeric(9) SELECT @OLDManagerReturn = ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear RETURN @OLDManagerReturn
View Replies !
View Related
Stored Procedure Input Parameter (asp.net 2.0)
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great: Code: Dim cmd As New SqlDataSource cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure cmd.InsertParameters.Add("@firstName", txtFirstName.Text) cmd.InsertParameters.Add("@lastName", txtLastName.Text) cmd.InsertParameters.Add("@address1", txtAddress1.Text) cmd.InsertParameters.Add("@address2", txtaddress2.Text) cmd.InsertParameters.Add("@city", txtCity.Text) cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value) cmd.InsertParameters.Add("@zipCode", txtZipCode.Text) cmd.InsertParameters.Add("@telephone", txtTelephone.Text) cmd.InsertParameters.Add("@email", txtEmail.Text) cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value) cmd.InsertParameters.Add("@birthday", txtBirthday.Text) cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value) cmd.InsertParameters.Add("@question", txtquestion.Text) cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" cmd.InsertCommand = "sp_insertCustomer" cmd.Insert() Stored Procedure: CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000) AS INSERT tblCustomer (firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question) Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question) ERROR: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied. Source Error: Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
View Replies !
View Related
Ignoring A Parameter Value In A Stored Procedure
If you have a table called Person and has ID, Age, Gender and Name. And you have a stored procedure: CREATE PROCEDURE [dbo].[GetName]@Age TINYINT,@Gender TINYINTASBEGIN SELECT Name FROM Person WHERE Age = @Age AND Gender = @Gender END You want some times to send @Age as null to get all ages (or in other words, you are not concerned with the age) or @Gender as null to get all genders. A way to do that is to build your query string dynamically and execute it with EXEC command. However, this is a bad solution because you are losing most of the SP benefits and writting longer and more complicated procedures, specially that you have a lot of parameters! Another solution that I thought of is having your select like this: SELECT Name FROM Person WHERE Age = ISNULL(@Age,Age) AND Gender = ISNULL(@Gender, Gender) However, I noticed that when the value of the column is NULL, you will get false results, obviously because you are using = operator in comparing a value with null while you should be using IS operator, so this method did not work. I am wondering if any one has a good solution for this. Adam Tibi
View Replies !
View Related
Stored Procedure With A Sort Parameter
I have a SQL Server 2005 Stored Procedure that takes a parameter to sort on: ----------------------------------------------------------------- ALTER PROCEDURE [dbo].[GetAllApplications_Sorted] @sortType varchar(25) AS SET NOCOUNT ON; SELECT aev.ApplicationID, aev.ApplicationName, aev.EventMessage, aev.MachineID, aev.MachineName, aev.EventLevel, ellu.EventImg, ellu.EventLevelID, dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID) AS maxEventLevel FROM dbo.ApplicationEventView aev, dbo.EventLevelLookUp ellu WHERE ellu.EventLevelID=dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID) AND aev.EventLevel=ellu.EventLevel ORDER BY ellu.EventLevelID + ' ' + @sortType ----------------------------------------------------------------- Problem is I keep getting this error: Conversion failed when converting the varchar value 'desc' to data type int. And I can't figure out why... Thanks, Doug
View Replies !
View Related
Bit Type Parameter For Stored Procedure
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI The syntax I am trying to use is: MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit)) MyStoredProcedure.Parameters("@pdi").value = -1 When I do my ExecuteNonQuery I get error 8114 What am I doing wrong?
View Replies !
View Related
Using Cursor As OUT Parameter In Stored Procedure
Hi guys, I have a serious problem. I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures : create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes; create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val; How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike
View Replies !
View Related
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 !
View Related
Parameter Passing To A Stored Procedure
Hey huys, I got this stored procedure. First of all: could this work? --start :) CREATE PROCEDURE USP_MactchUser @domainUserID NVARCHAR(50) , @EmployeeID NVARCHAR(50) , @loginType bit = '0' AS INSERT INTO T_Login (employeeID, loginType, domainUserID) Values (@EmployeeID, @loginType, @domainUserID) GO --end :) then I got this VB.Net code in my ASP.net page.... ---begin :) Private Sub matchUser() Dim insertMatchedUser As SqlClient.SqlCommand Dim daMatchedUser As SqlClient.SqlDataAdapter SqlConnection1.Open() 'conn openned daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1) daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text) daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output SqlConnection1.Close() 'conn closed End Sub --- If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View Replies !
View Related
Stored Procedure Parameter And IN Clause
This works: WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005')) This doesn't: WHERE WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears)) @strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?
View Replies !
View Related
Using NEWID() As A Parameter To A Stored Procedure
Is it possible to use NEWID() as a parameter to a stored procedure inSQL Server 2000. I keep getting a "Line 1: Incorrect syntax near ')'"error.ALTER PROCEDURE dbo.StoredProcedure1(@x uniqueidentifier)AS/* SET NOCOUNT ON */RETURNStoredProcedure1 NEWID()go"Line 1: Incorrect syntax near ')'"Thanks in advance
View Replies !
View Related
Possible To Specifying A List As A Parameter To A Stored Procedure ?
I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like.... SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7)) The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure? Any help much appreciated!
View Replies !
View Related
Parameter Direction Of A Stored Procedure
I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[ProcRetDbl] @Threshold real, @Result real OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @Result = Channel1 FROM DataTable WHERE Channel2 < @Threshold END But then when I look at the properties of the @Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening. I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help. Kamen
View Replies !
View Related
Surrogate Key As Parameter In Stored Procedure?
I have two tables: countries(country_id integer, country_name string) authors(auth_id integer, country_id integer, auth_name string) ...Where "country_id" in the authors table refers to the same country_id in the countries table. I want a stored procedure to handle the insertion of new rows in the authors table. There are two methods of doing it: 1) CREATE PROCEDURE addAuthor( authorName, countryId ) And 2) CREATE PROCEDURE addAuthor( authorName, countryName ) Now, I like #1 because the implementation is simple -- the calling code simply passes an author name, and a country id and an INSERT INTO statement is called with those parameters INSERT INTO authors( @authorName, @countryId ) I like #1, because it hides the surrogate "id" key from the application calling code. But on the downside, it has more overhead work, because you have to first a) verify a country with that name exists, and b) select that id into a variable. DECLARE id INT; IF EXISTS (select * from countries where country_id = @countryId ) THEN SELECT country_id INTO id FROM countries WHERE country_name = @countryName; END IF; (Sorry I may have the SQL syntax wrong up there, but I was just trying to demonstrate the extra overhead involved). Which approach do you guys think is better?
View Replies !
View Related
Stored Procedure Parameter Filtering
consider a stored procedure with a parameter @OrderID, i want to perform the following query :select * from Orders where OrderID = @OrderIDi want the condition to be true when parameter @OrderId is null so what is the syntax for that? i think there is an IF CONDITION that can be embedded with where clause.
View Replies !
View Related
Output Parameter Of A Stored Procedure
I have a stored procedure (named Insert_SRegister) that calls another stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one. The program goes like this: The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored procedure. The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission; The code of the first procedur is : Create Procedure dbo.Insert_SRegister @YGroup VarChar (3), @YDate VarChar (4), @YTerm Char(1), @SName VarChar(30), @SSecondName VarChar(30), @SFirstName VarChar(30), @SGender TinyInt, @SBDate DateTime, @SBPlace VarChar(50), @SOCountry VarChar(50), @SOPassCount VarChar(50), @PassNo VarChar(50), @SOLang VarChar(50), @MOLang VarChar(50), @MName VarChar(50), @MHAdd VarChar(50), @MHT1 VarChar(20), @MHT2 VarChar(20), @MHT3 VarChar(20), @MHMob VarChar(20), @MWAdd VarChar(50), @MWT1 VarChar(20), @MWT2 VarChar(20), @MExt VarChar(10), @MWMob VarChar(20), @FName VarChar(50), @FHAdd VarChar(50), @FHT1 VarChar(20), @FHT2 VarChar(20), @FHT3 VarChar(20), @FHMob VarChar(20), @FWAdd VarChar(50), @FWT1 VarChar(20), @FWT2 VarChar(20), @FExt VarChar(10), @FWMob VarChar(20), @EAdd VarChar(50), @ETele VarChar(20), @SchName VarChar(50), @SAdd VarChar(50), @FBNo Smallint, @FSNo Smallint, @FONo VarChar(50), @EMedical VarChar(1000), @FDetails VarChar(1000), @FRDetails VarChar(1000), @PName VarChar(50), @StudentStatus VarChar(50) OUTPUT, @ID VarChar (50) OUTPUT As Begin Declare @Exists Int, -- Return Value @StudentCode VarChar(50) ----------------------------------------- -- here I call the second proc and assgin it output to variable --@StudentCode ----------------------------------------------------------------------------------- exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT ----Do work and insert info in db End Code of the second stored procedure is: ALTER Procedure dbo.Generate_Student_Code @YGroup VarChar (3), @YDate VarChar (4), @newID VarChar (50) OUTPUT As Begin set nocount on Declare @Exists int, -- Return Value @Mv varchar(5), @IdLen int If Exists(Select SID From SRegisteration) Begin select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration Set @IdLen = DATALENGTH(@Mv) Set @Mv = case when @IdLen = 1 then '0000' + @Mv when @IdLen = 2 then '000' + @Mv when @IdLen = 3 then '00' + @Mv when @IdLen = 4 then '0' + @Mv else @Mv end Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv ---------Here I return the ID value select @newID -------------------------------------- Select @Exists = 1 End Else Begin Select @Exists = 0 Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001' ---------Here I return the ID value select @newID ------------------ Return @Exists End End
View Replies !
View Related
Default Parameter In Stored Procedure
Here is my Dilemma: I am writing a stored procedure (in SQL 6.5) with a Default Parameter and will call this SP through Power Builder sometimes with a value for the default parameter and sometimes without a value. I want the SP to update the appropriate column with the value when I call it with value and update the column with 'Null' when I call it without a value. The SP goes like this Create Proc dbo.sp_Emp @ID int ,@AttorneyTrackID int = NULL as Declare @UserName char(8) ,@Now datetime ,@Status int ..... .... ..... Will this work or is there a better way to do this. I hope I have posted enough info and expressed the problem clearly. I will appreciate any comment or feedback. I need help urgently, thanks.
View Replies !
View Related
Output Parameter In Stored Procedure
can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter. ie insert record and get its id column value in output parameter. any one knows the way?
View Replies !
View Related
OUTPUT Parameter From Stored Procedure
Hi I should validate a USER and if the user is not a valid user it should return a message to the front end application for this I have a procedure like this . Basically I need to pass an output parameter to the front end application returned by this procedure. Create procedure test @userid VARCHAR(20) AS Declare c1 cursor AS Select userid from USERS_TBL Open Cursor C1 FETCH NEXT from C1 INTO @Temp While @fetch_status = 0 BEGIN If @Temp <> @Userid -- IF the USER is not a valid USER ...It should not execute the Futher code(statement) it should exit the procedure by returning the error statement. --- --- --- FETCH NEXT from C1 INTO @Temp END Thanks VENU
View Replies !
View Related
Using The IN Keyword With A Parameter In A Stored Procedure
Here is the beginning of a stored procedure that I am trying to use as the source of data for a Reporting Services report. Code Snippet CREATE PROCEDURE udsp_mrkt_anal @source varchar(8000), @weeks int AS DECLARE @campaigns table (glcid int, campdate datetime) DECLARE @entitycamp table (enid int, glcid int) DECLARE @campcount table (glcid int, opps decimal (18,0), revenue money) DECLARE @camps table (opid int, begdate datetime) INSERT into @campaigns SELECT glcid, [date] FROM gl_campaigns WHERE source IN (@source) So, what I am trying to do is allow users to pick multiple source ids to be returned in the report. However, SQL doesn't see them as that in the stored procedure and I am assuming is just converting them to a string for example: '1, 2, 3, 4' My question is, how can I make this work with a stored procedure and having the user pass multiple ids through, in this case, a report parameter? The same solution would probably work for just executing the sp straigt out as well.
View Replies !
View Related
Stored Procedure And VarChar Parameter
I'm having the following (abbreviated) stored procedure: Code Snippet CREATE PROCEDURE proc_SomeSmartName @SomeVariable VARCHAR AS BEGIN SELECT COUNT(ID) AS SomeLabel, SomeField FROM SomeTable GROUP BY SomeField HAVING SomeField = @SomeVariable END Now my problem: It doesn't seem to work if I give the @SomeParameter a string to work with, neither via SqlCommandObject nor directly in the Management Studio. The following returns zero rows: Code Snippet DECLARE @return_value int EXEC @return_value = [dbo].[proc_SomeSmartName] @SomeVariable = 'MyText' SELECT 'Return Value' = @return_value Funny enough, when I have the following query, it works perfectly: Code Snippet SELECT COUNT(ID) AS SomeLabel, SomeField FROM SomeTable GROUP BY SomeField HAVING SomeField = 'MyText' Returning one row as it should. SomeField is an NVarChar field, but I tried casting it to VarChar without any benefit, and I also supplied the parameter as NVarChar to test, both without further success. And 'MyText' does exist in the database, in both cases when I run the stored procedure and when I run the SQL statement directly. What am I doing wrong?
View Replies !
View Related
Help For Stored Procedure Input Parameter
Hi All, I have a project which will be a tools to edit different tables. Now I need a stored_procedure to select data from different table. For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns. It has the following records. "1","EmployeeID","Varchar" "1","FirstName","varchar" "1","LastName","varchar" "1", "EmployedDate","date" It has the following records. "2","AddressID","int" "2","ApartNo","varchar" "2", "Address","varchar" Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data, "001","Susan","Daka","1999-09-09", "002","Lisa","Marzs","1999-08-08", "003","David","Smith","2000-01-01", I also have address table has columns "AddressID","ApartNo","Address" and has the following data "1","1101","1208 Mornelle Crt, Toronto", "2","1209","1940 Garden Drive, Toronto" I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields." So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables. I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string. Second, I need to create a stored procedure to insert or update data into these dynamically table. Can anyone help me? Thanks in advance.
View Replies !
View Related
Syntax To Use Stored Procedure Parameter
How do I pass my stored procedure value into my query? My first example works, in the second example my parameters are contained in the sql string. How do I expose parameter values to the inner sql string? Thanks for any help. Kevin ============================================== This works: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectTest] @StartDate datetime, @EndDate datetime AS BEGIN SET NOCOUNT ON; SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE DownloadDate BETWEEN @StartDate AND @EndDate END This does not: INSERT INTO ReportTable (ItemDate, ProjectNo, ProjectName, Category, Type, Amount, RevExp) EXECUTE ('SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE [Project Number] > 0 AND DownloadDate BETWEEN @StartDate AND @EndDate ');
View Replies !
View Related
ADO Stored Procedure Parameter Error With DB2
Hey all, I ran into a little snag when writing a VBScript app for work that parses a text file and places that data into a DB2 V8.1 database via OLE DB ADO calls and a stored procedure with parameters. I have found that I can call a stored procedure w/o parameters but as soon as I one that takes some I get the following error. "arguments are of the wrong type, are out of acceptable range" To try to troubleshoot it, I tried applying each parameter property individually. I noticed that when I didn't provide a direction, I received a different error (Don't have the exact one but it had something to do with an IBM OLE DB property error). Because the VBScript error was on the line (mycmd.commandtype = adCmdStoredProc) I replaced the global constant with a literal (IE: 4). No fix. I'm going to try to replace all of the global constants with their literal values but I also found this on the IBM site... 1: SQLProcedureColumns is used by many applications to determine the direction of stored procedure parameters. The ODBC driver is returning an ordinal of 1 for all the parameters. This causes problems with applications that use this information. For example a stored procedure call using Microsoft ActiveX Data Objects (ADO) and the adCmdStoredProc syntax will return one of the following error messages: 3265 Item cannot be found in the collection corresponding to the requested name or ordinal. 3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. SQLProcedureColumns is also returning a "BUFFER_LENGTH" value for numeric data types that is 1 byte too large. The default C data type is character so this value should be display size -without an extra byte for null. I'm guessing this is the solution to my issue but I figured I'd put this out there just in case I was missing something. If you have any other suggestions, please chime in.
View Replies !
View Related
Help! !SQL Stored Procedure Output Parameter.
I am calling a procedure with two input parameters and one output parameter (third) in the following format. I'm receiving a database error on the return parameter. *result. What is the correct syntax for an output parameter. Code: CSTRING strSQL; strSQL.Format("BEGIN EQUIPMENT_UTILITIES.MOVE_EQUIPMENT('%s', '%s', '%d'); END;",(LPCSTR)lpEQNum,(LPCSTR)lpMoveTo, result); <-Failing at '%d' result. Thanks.
View Replies !
View Related
Help With XML Input Parameter For Stored Procedure
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record. Here is an example of the XML being passed: <object> <property @propID="14" @propType="4" @propValue="Blah blah text" /> <property @propID="217" @propType="2" @propValue="Some other text" /> </object> I have a table like this in my database: CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT) and I want a stored procedure that will accept XML and update this table. Here is what I am trying: CREATE PROCEDURE sp_UpdateObject @inValues XML AS BEGIN --create a temporary table DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT) --And then insert the values from the @inValues XML argument into the temporary table --I am sure the SELECT statement is VERY wrong INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE) SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT) FROM @inValues.nodes('/object/property') --...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID AND @props.PROPTYPE != 6 END I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL. I apologize for the long post. Thanks in advance for any help you can provide.
View Replies !
View Related
Help With Parameter Values In Stored Procedure
Hi, I am using a stored Procedure where I am passing some parameter values.Following is my Code. CREATE proc Usp_Rpt_GetDetails @Fromdt varchar(12), @ToDt varchar(12), @ApscId numeric, @StatusCode varchar(1), @val numeric as Begin if @StatusCode = "C" then @ vall(1,2) End select '' as unuseid, substring(ltrim(rtrim(s.Spares_Code)),1,12) as Code, oh.WO_Number AS Claim_Id, ltrim(rtrim(sc.section_code)) AS section_code, ltrim(rtrim(dc.defect_code)) AS defect_code, ltrim(rtrim(at.Action_Taken_Code)) AS Repair_Code, cs.Call_status_code from [32_Trans_Work_Order_Spares_Detail] ws inner join [32_Trans_Work_Order_Header] oh on oh.WO_Number = ws.WO_Number inner join [11_Master_Spares]s on s.Spares_ID = ws.Spares_ID inner join [31_Master_Section_Code] sc on sc.Section_ID = ws.Section_Code_ID inner join [31_Master_Defect_Code] dc on dc.Defect_ID = ws.Defect_Code_ID inner join [10_Master_Equipment_Status] e on e.Equipment_Status_ID = oh.Equipment_Status_ID inner join [00_Master_Country] c on c.Country_ID = mp.Country_ID where e.Equipment_Status_ID in (1,2) and cs.Call_Status_ID in (1,2) and oh.WO_Record_Date between @Fromdt and @ToDt and oh.WO_Status='C' My Problem is How to pass values to parameters Status Code Consists of values C, V, R which i am passing from the Front End along with Call_Status_ID which can be 1,2. Thanks ...
View Replies !
View Related
|
TRACKER
