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.
In the SqlDbType enumeration there is no value for the new (max) types, only varchar. If Im passing a large string, it will get cut off at 8K. So how do I specify my varchar parameter as being of the max type ?
Hi Guys I am wondering if you could spare some time and help me out with this puzzle. I am new to this stuff so please take it easy on me.
I’m trying to create procedure which will take 2 input parameters and give me 1 back. Originally there will be more outputs but for this training exercise 1 should do. There are 2 tables as per diagram below and what I’m trying to do is Verify username & password and pull out user group_name.
For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.
'==================================================== ALTER PROCEDURE dbo.try01 ( @UserName varchar(50), @Password varchar(50), @Group varchar Output ) AS SET NOCOUNT ON; SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME, TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID FROM TBL_USERS INNER JOIN TBL_USER_GROUPS ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID WHERE (TBL_USERS.USERNAME = @UserName) AND (TBL_USERS.PASSWORD = @Password) '====================================================
and this is what i'm getting in VS.Net while trying to save.
'==================================================== ADO error: A select statement that assigns a value to variable must not be combined with data-retrieval operation. '====================================================
I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
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!!
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
Hi All, I have a Stored procedure as below.. create procedure sp_sample @name varchar(12) as begin set @name = (select name from mytable where id = 1) select @name end
how can i return the varchar value from the above Stored procedure? I want to capture it in SQLFetch ODBC call.
I have a stored procedure which returns a count of products and a limited number of rows from a query.
I am using SQL Server 2005 and calling the procedure in asp.net
The procedure is as follows
Code Snippet
GO ALTER PROCEDURE [dbo].[GetProductsByCategoryId] @Category VARCHAR(255), @Range INT, @PageIndex INT, @NumRows INT, @CategoryName nvarchar(255) OUTPUT, @CategoryProductCount INT OUTPUT AS
BEGIN
/* Get product count */ SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)
/* get full list of products */ With ProductEntries as ( SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2 FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar WHERE Range=@Range AND Category = @Category )
/*get only needed rows */ SELECT field1, field2 FROM ProductEntries WHERE Row Between @startRowIndex and @startRowIndex+@NumRows-1
END
The problem seems to be with the line
AND Category = @ Category in the query to make the ProductEntries
If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes. If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.
The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.
Also if I run the query on just the Products table removing the left join it will return results with no problems.
Thanks to anyone who can help!
And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.
in the statement, where @sql is defined as DECLARE @sql varchar(Max). the problem is that this statement produces results that are in excess of 8000 characters and the results are truncated. Is there anyway to avoid this? I know that it's not possible to user ntext/text as a local variable, and if i try to return the result as an ouput paramater, only the first result is returned.
my code is based off of this article http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables Thanks for any suggestions.
Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :( Syntax error converting the varchar value 'ABC' to a column of data type int. SQL file ====================== DECLARE @S AS VARCHAR(1000) EXEC @S = PDT.DBO.SP_RPT 'ABC' SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S ====================== Stored Procedure ====================== CREATE procedure sp_RPT( @array varchar(1000) ) AS DECLARE @content AS VARCHAR(2000) SET @content = 'RPT: ' + @array + ' loaded successfully ' SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array) GO
So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it. Here is the code:#############################################ALTER PROCEDURE dbo.GetUserIdForUser @username NVARCHARASBEGINDECLARE @postedbyid UNIQUEIDENTIFIERSET @postedbyid = (SELECT UserIdFROM aspnet_UsersWHERE (UserName = @username))END###Which returns this### Running [dbo].[GetUserIdForUser] ( @username = jason ).No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser].############################################# This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build. If anyone can tell me what I am doing wrong I would appreciate it. I have tried a few things that have resulted in different failures. If I remove any references to variables (delete SET @postedbyid = and replace @username with 'jason') I can get it to return a result. If I put @username in though it doesn't work. Here are the examples of those:ALTER PROCEDURE dbo.GetUserIdForUser @username NVARCHARASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = @username)END###Which returns this###Running [dbo].[GetUserIdForUser] ( @username = jason ).UserId -------------------------------------- No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Here is a sanity check to show that the data is in fact in the database:ALTER PROCEDURE dbo.GetUserIdForUserASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = 'jason')END Running [dbo].[GetUserIdForUser].UserId -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Anyone have any ideas on what I am doing wrong?
In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria; I get: Failed to convert parameter value from a String to a Guid. Ugh! It's a string, dummy!!! I have even tried: objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString(); and
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B"); but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?
I have a problem I can't seem to find the solution with this aweful limitations on VARCHAR fields of 255.
Within a stored procedure called Store_Check, I need to dynamically build a string (@string) using VARCHAR(255) since the text datatype can't be used in a stored procedure.
So,this string is built according to whether the Store ID is NOT NULL. So if the StoreID is not null, I start building this string 'Exec Update_Store_Address @StoreID1, @address2'. There are 20 StoreID's passed into Store_Check. IF all 20 StoreID's are not NULL, the executed String greatly exceeds 255 because the string winds up looking like this
I am not executing this string within the StoredCheck procedure. It needs to be passed as ONE string to a VB program and it gets executed by the VB program. Even if I create 4 local variables and concatenate them, it stops at the 255th character.
Also, a local varialbe of type TEXT cannot be declared within stored procedure.
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!
how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.
Code: USE [byrndb] GO /****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code: USE [byrndb] GO DECLARE@return_value int EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber] @number = N'23913' SELECT'Return Value' = @return_value GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
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]
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.
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
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?
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?
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'.
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,
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);
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
Hi,This works:CREATE PROCEDURE MyProc @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this?
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'.
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
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?
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?
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?
