I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks. ALTER procedure [dbo].[USP_Account_Search_Mod]
@ClientCode VARCHAR(7) = ''
,@DebtorName VARCHAR(25) = '',@DebtorNumber INT = 0
if @Status = 'ALL' OR @Status = 'ALA' OR @Status = 'ALI' --All Period
BEGIN
if(@Status = 'ALL') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'
END
if(@Status = 'ALA') --All Active
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'
END
if(@Status = 'ALI') --All Inactive
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'
SELECT @SQLTier2Select = 'SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria + ' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'
ENDEND
ELSE IF @Status = 'PAID'
BEGIN
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'END
ELSE
BEGINSELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria
SELECT @SQLTier2Select = 'SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))'+ @Criteria END
SELECT @SQL = CASE @Tier
WHEN 0 THEN @SQLTier1Select + ' UNION ' + @SQLTier2Select + 'ORDER BY NAME ASC' WHEN 1 THEN @SQLTier1Select + 'ORDER BY NAME ASC'
WHEN 2 THEN @SQLTier2Select + 'ORDER BY NAME ASC 'END
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
I am building a stored procedure that changes based on the data that is available to the query. See below. The query fails on line 24, I have the line highlighted like this. Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------ This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Hi, somebody can tell me how to create a correct Stored procedure (with commit and rollback) that return errors to my code for save it in a log file .... I would like to know the right method for a SP with parameters and return error value Thanks
hi, i want to take the first n values from my Categorii table, here is my stored procedure: SELECT ROW_NUMBER() OVER (ORDER BY CategoryID) AS RowNumber, CategoryID, Name, Description FROM Categorii WHERE DepartamentID = @DepartamentID AND RowNumber <= 5 i get the error: invalid column name RowNumber why? what should i do? if i execute the procedure without the AND RowNumber <= 5 i get the RowNumber values 1 to 9 (that means it works)...but what should i do to retrive only the first n? thank you
When I'm trying to execute my stored procedure I'm getting the following code Line 35: Incorrect syntax near '@SQL'. Here is my procedure. Could someone tell me what mistake I'm doing.Alter procedure [dbo].[USP_SearchUsersCustomers_New] @UserID INT ,@RepName VARCHAR(50) ,@dlStatus VARCHAR(5) = '' as Declare @Criteria VARCHAR(500) ,@SQL VARCHAR(8000)
SELECT @Criteria = ''SET NOCOUNT ON if (@dlStatus <>'ALL' AND (LEN(@dlStatus)>1)) BEGIN if(@dlStatus='ALA') SET @Criteria='AND dbo.tbl_Security_Users.IsActive=1' else SET @Criteria='AND dbo.tbl_Security_Users.IsActive=0' END --If the user is an Admin, select from all users. if(dbo.UDF_GetUsersRole(@UserID) = 1) BEGIN@SQL = 'SELECT U.UserID --,U.RoleID ,ISNULL((Select TOP 1 R.RoleName From dbo.tbl_Security_UserRoles UR INNER JOIN dbo.tbl_Security_Roles R ON R.RoleID = UR.RoleIDWhere UR.UserID = U.UserID), 'Unassigned') as 'RoleName' ,U.UserName ,U.Name ,U.Email ,U.IsActive ,U.Phone FROM dbo.tbl_Security_Users U --INNER JOIN dbo.tbl_Security_Roles R ON U.RoleID = R.RoleID WHERE U.NAME LIKE @RepName AND U.UserTypeID < 3'+ @Criteria
Hi,I want to use a variable to put a value in a table but it doesn't seems to works. How can i do that? I have bolded and underlined the text that i think is not correct.What syntax can i use to make it work?Thanks----------------------------------------------------------------------dbo._UpdateImage(@ID int,@ImageID int) ASBegin Declare @PhotosThumb nvarchar(50)Declare @Photos nvarchar(50) SET @PhotosThumb = 'PhotosThumb' + convert(nvarchar, @ImageID)SET @Photos = 'Photos' + convert(nvarchar, @ImageID) SET NOCOUNT ON IF @ImageID = 1 UPDATE PhotosSET @PhotosThumb = 'Logo_thumb.gif',@Photos = 'Logo320x240.gif'WHERE ID = @ID ELSE UPDATE PhotosSET @PhotosThumb = NULL,@Photos = NULLWHERE ID = @ID SET NOCOUNT OFFEND
Yo people, got a little problem with this stored procedure, i go to save it and it kicks out these errors: Incorrect syntax near the keyword 'Drop'.Incorrect syntax near 'Go'.Incorrect syntax near 'Go'.'CREATE/ALTER PROCEDURE' must be the first statement in the query batch I dont no about this sort of stuff so a good break down of what wrong would be good, below is the whole procedure. CREATE PROCEDURE dbo.SQLDataSource1 Drop Table PersonGo Create Table Person (PersonID Int Identity, PersonEmail Varchar(255),PersonName Varchar(255), PersonSex Char(1),PersonDOB DateTime, PersonImage Image,PersonImageType Varchar(255) )
Drop Proc sp_person_isp Go Create Proc sp_person_isp @PersonEmail Varchar(255),@PersonName Varchar(255), @PersonSex Char(1),@PersonDOB DateTime, @PersonImage Image, @PersonImageType Varchar(255) As BeginInsert into Person (PersonEmail, PersonName, PersonSex, PersonDOB, PersonImage, PersonImageType) Values (@PersonEmail, @PersonName, @PersonSex, @PersonDOB, @PersonImage, @PersonImageType) End
Dim CN = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring")) Dim CM As New SqlCommand("spCCF_CrossTab", CN) CM.CommandType = CommandType.StoredProcedure CM.Parameters.Add(New SqlParameter("@LocationID", "CCFIF")) CM.Parameters.Add(New SqlParameter("@BeginDate", dtbStart.Text)) CM.Parameters.Add(New SqlParameter("@EndDate", dtbEnd.Text)) CN.Open() DR = CM.ExecuteReader(CommandBehavior.CloseConnection) dgReport.DataSource = DR dgReport.DataBind()
A SQL exception is thrown: Incorrect syntax near the keyword 'END'
But I turned on tracing in Enterprise Manager, the following request is sent to SQL: exec spCCF_CrossTab @LocationID = N'CCFIF', @BeginDate = N'11/3/2003', @EndDate = N'11/4/2003' In query analyzer the above line executes without error and returns the expected information.
My stored procedure is:
CREATE PROCEDURE spCCF_CrossTab @LocationID varchar(10), @BeginDate varchar(10), @EndDate varchar(10) AS
select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.Productivity_CCF.[Date] BETWEEN CONVERT(DATETIME, ''' + @BeginDate + ''', 101) AND CONVERT(DATETIME, ''' + @EndDate + ''', 101)) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name order by Action' select @sumfunc= 'COUNT(ActionID)' select @pivot='UserName' select @table= 'UserInfo' select @where='(dbo.UserInfo.LocationID = ''' + @LocationID + ''' and dbo.UserInfo.Inactive<>1 )'
DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null and ' + @where)
SELECT @delim=(CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '''' END) FROM tempdb.information_schema.columns WHERE table_name='##pivot' AND column_name='pivot'
I am trying to get a returned value from the stored procedure below CREATE PROC insert_and_return_id ( @parameter1 varchar, @parameter2 varchar
) AS DECLARE @newID int SELECT @newID = 0 INSERT INTO tbltest (field1, field2) VALUES (@parameter1, @parameter2) IF(@@ROWCOUNT > 0) BEGIN SELECT @newID = @@IDENTITY END RETURN @newID GO ___________________________ My asp Code looks like this ___________________________ Function InserTest(value1, value2) Dim objConn, objRs, objCmd ' Create a connection to the database Set objConn = Server.CreateObject("ADODB.Connection") objConn.open "DSN=" & CONNECTION_STRING ' Create the query command Set objCmd = Server.CreateObject("ADODB.Command") Set objCmd.ActiveConnection = objConn objCmd.CommandText = "insert_and_return_id" objCmd.CommandType = adCmdStoredProc ' Create the parameter for output and returned valueand populate it objCmd.Parameters.Append objCmd.CreateParameter("parameter1", adVarChar, adParamInput, 255, value1) objCmd.Parameters.Append objCmd.CreateParameter("parameter2", adVarChar, adParamInput, 255, value2) objCmd.Parameters.Append objCmd.CreateParameter("newID", adInteger, adParamReturnValue, 4)
objCmd.Execute objCmd0 response.write objCmd.Parameters("newID") 'objCmd.Close End Function
And I get the following ASP Error Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function insert_and_return_id has too many arguments specified. /netwasp/tester.asp, line 62
I only just started to use sp's hence it might be something really simple, Can anyone help, cheers?
I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ????? Thanks friends.. Procedure 'UpdateRowsReorderUp' expects parameter '@nextlowestsortID', which was not supplied. CREATE PROCEDURE [dbo].[UpdateRowsReorderUp] ( @intsortID int, @nextlowestsortID int, @MemberID int ) AS Select @nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@intsortID order by SortID DESC) UPDATE SelectedCredits SET SortID= CASE WHEN SortID = @nextlowestsortID then @intsortID WHEN SortID = @intsortID then @nextlowestsortID ELSE SortID End WHERE MemberID = @MemberID SELECT * FROM SelectedCredits WHERE MemberID= @MemberID ORDER BY SortID GO ************** // this is my script on the page void moveup(Object s, DataListCommandEventArgs e) { objcmd= new SqlCommand("UpdateRowsReorderUp",objConn); objcmd.CommandType = CommandType.StoredProcedure; objcmd.Parameters.Add("@intsortID",intsortID); objcmd.Parameters.Add("@MemberID",Session["MemberID"]); objRdr= objcmd.ExecuteReader(); dlSelCredits.DataSource = objRdr; dlSelCredits.DataBind(); objRdr.Close(); objConn.Close(); BindData(); }
I have a stored procedure to which I pass the following parameters @Date smalldatetime, @Amount decimal(15,3) @Exg_Rate decimal(5,3)Inside this stored procedure I call another one passing to it those parameters like thatEXECUTE dbo.[Opening_Balance_AfterInsert] @Date, @Amount*@Exg_RateI receive an error at the above line saying: Incorrect syntax near '*'if I try to put the expression between rounded brackets I receive the error Incorrect syntax near '('How can I pass that expression?
String[1]: the Size property has an invalid size of 0.I am using a Stored Procedure, shown hereCREATE PROCEDURE GetImagePath( @ID INT, @ImagePath VARCHAR(50) OUTPUT) AS SET NOCOUNT ONSELECT @ImagePath = path FROM dbo.docs WHERE table1= @IDRETURNGOHere is the asp.net code that I am usingSqlConnection conn = new SqlConnection(strConnection);conn.Open();SqlParameter param = new SqlParameter("@ImagePath", SqlDbType.VarChar);param.Direction = ParameterDirection.Output;SqlCommand cmd = new SqlCommand("GetImagePath", conn);cmd.Parameters.AddWithValue("@ID", 100);cmd.Parameters.Add(param);cmd.CommandType = CommandType.StoredProcedure;cmd.ExecuteNonQuery();cmd.Dispose();conn.Dispose();TextBox1.Text = param.Value.ToString();When I run it, I get the following error String[1]: the Size property has an invalid size of 0. The stored procedure is correct because I tested it wtih Query Analyzer. I cant seem to figure out what is causing this error.Any help would be appreciated.
I am using SQL Server 2005 and wrote a stored procedure as shown below.CREATE PROCEDURE [dbo].[GetUsers]( @StartRowIndex INT, @MaximumRows INT, @SortExpression VARCHAR(50))ASBEGIN DECLARE @SQL VARCHAR(1000); DECLARE @Start VARCHAR(10); SET @Start = CONVERT(VARCHAR(10), @StartRowIndex + 1); DECLARE @End VARCHAR(10); SET @End = CONVERT(VARCHAR(10), @StartRowIndex + @MaximumRows); SET @SQL = ' WITH Data AS( SELECT UserID, Username, FirstName, LastName, ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users) SELECT UserID, Username, FirstName, LastName FROM Data WHERE RowNumber BETWEEN ' + @Start + ' AND ' + @End EXEC(@SQL);ENDIn VS 2005, I am using a DataSet. Then I created an ObjectDataSource which binds the GridView control. However, I am getting the following error: Incorrect syntax near ')'After playing around with it, the error is from the line:ROW_NUMBER() OVER(ORDER BY ' + @SortExpression + ') AS RowNumber FROM Users)If I change the line to ROW_NUMBER() OVER(ORDER BY UserID') AS RowNumber FROM Users) it works fine, except for sorting.I dont understand what I might be doing wrong.
I am writing a stored procedure where I am adding a column to a table, doing some manipulation using the new column and then deleting the column. The problem is when I try to save the stored procedure, it gives me an error because it cannot find the new field on the table. How can I tell SQL to not compile a section or whole stored procedure?
does anyone see anything wrong with this stored procedure?? I keep getting a syntax error.
CREATE PROCEDURE [InsertGCTerms] AS INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title]) SELECT a.TM#, a.LASTNAME, a.FIRSTNAME, a.SSN#, a.JOBTITLE, a.HIREDATE, a.DEPT# FROM GOVEMPLYS AS a WHERE a.STATUS = 'TERMINATED' RETURN GO
DECLARE my_cursor CURSOR FOR SELECT spid,blocked from sys.sysprocesses where program_name ='PurveyanceImport' and status = 'suspended' and cmd = 'UPDATE'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
WHILE @@FETCH_STATUS = 0 BEGIN
Kill @tSPId
FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked END
SET @ExecuteSQL = @ExecuteSQL + 'Select MRNumber From #TempContractorTable '
IF (@Status = 'Load') BEGIN SET @ExecuteSQL = @ExecuteSQL + ' Where datediff(day, CallDate, getdate()) > 5 ORDER BY '+ @SortCol + ' ' + @SortOrder + '' END IF (@Status = 'Search') BEGIN IF LEN(@Fdate)>0 AND LEN(@Tdate)>0 BEGIN SET @ExecuteSQL=@ExecuteSQL + ' Where CallDate >= ''' + @FDate + ''' AND CallDate <= ''' + @TDate + ''' ' END ELSE IF LEN(LTRIM(RTRIM(@Fdate)))>0 BEGIN SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate >= ''' + @FDate + ''' ' END ELSE IF LEN(LTRIM(RTRIM(@Tdate)))>0 BEGIN SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate <= ''' + @TDate + ''' ' END SET @ExecuteSQL=@ExecuteSQL + 'ORDER BY '+ @SortCol + ' ' + @SortOrder + ' '
error in stored procedure an error or column name is missing for SELECT INTO statement,verify each column has name,for other statements look for empty alias name alies defiened as "" or [] add a name or single space as the alias name,an object name or column is missing or empty
stored procedure ALTER PROCEDURE dbo.spM3_ByLocationSearch
AS SET NOCOUNT ON DECLARE @Select NVARCHAR(4000), @Where NVARCHAR(4000), @SqlFinal NVARCHAR(4000), @ErrorMsgID INT, @ErrorMsg VARCHAR(200) SET @Select=''
SET @Where=''
SET @Select = 'WITH SearchLocList AS (select * from ( SELECT dp.depottrack_inventory_serial_number FROM depottrack DP, (SELECT depottrack_inventory_serial_number ,MAX(DEPOTTRACK_CREATEDDATE) AS DEPOTTRACK_CREATEDDATE FROM depottrack
) a left join ( SELECT I.INVENTORY_SERIAL_NUMBER AS CONTAINER#, product_class+ ' | ' + product_description as SIZE | TYPE, inventory_status as STATUS, inventory_vendor_id as VENDOR, ISNULL(INVENTORY_COST_PURCHASE,0) AS PURCHASE PRICE, ISNULL(INVENTORY_COST_UPFIT,0) + ISNULL(INVENTORY_COST_MATERIAL,0) AS UPFIT, ISNULL(INVENTORY_COST_DRAYAGE,0) AS DRAYAGE, ISNULL(INVENTORY_COST_PURCHASE,0) + ISNULL(INVENTORY_COST_UPFIT,0) + ISNULL(INVENTORY_COST_MATERIAL,0) + ISNULL(INVENTORY_COST_DRAYAGE,0) AS TOTAL from INVENTORY I LEFT JOIN VW_IV_COST_PURCHASE P on I .INVENTORY_GUID = P.INVENTORY_GUID LEFT JOIN VW_IV_COST_UPFIT U on I .INVENTORY_GUID = U.INVENTORY_GUID LEFT JOIN VW_IV_COST_DRAYAGE D on I .INVENTORY_GUID = D.INVENTORY_GUID LEFT JOIN VW_IV_COST_MATERIAL M on I .INVENTORY_GUID = M.INVENTORY_GUID left join product on i.inventory_product_id = product.product_id ) b on a.depottrack_inventory_serial_number = b.CONTAINER# where container# is not null order by depottrack_inventory_serial_number ) Select * from SearchLocList'
IF (@DEPOTTRACK_CREATEDDATE IS NOT NULL) BEGIN IF (@Where <>'' )
IF(@depottrack_locid<>"") BEGIN IF (@Where <>'' ) SET @Where =@Where + ' AND SearchLocList.depottrack_locid=''' + @depottrack_locid + '''' ELSE SET @Where = 'AND SearchLocList.depottrack_locid =''' + @depottrack_locid+ '''' END
IF ((@DEPOTTRACK_CREATEDDATE IS NOT NULL)) BEGIN IF (@Where <>'' )
ELSE SET @Where = 'SearchLocList. CONVERT(VARCHAR(10),DP.depottrack_createddate,101)<=''' + CONVERT(VARCHAR,@DEPOTTRACK_CREATEDDATE,101) + '''' END
IF (@OrderBy IS NULL) BEGIN SET @OrderBy= 'SearchLocList.DP.depottrack_createddate, DP.depottrack_inventory_serial_number' END
IF (@Where <> '') BEGIN SET @SqlFinal= @Select + ' where ' + @Where + ' order by ' + @OrderBy END ELSE BEGIN SET @SqlFinal= @Select + ' order by ' + @ORDERBY END
I have created a stored procedure for transferring data from one table to another table with a simple condition. but i am getting error 'Incorrect syntax near the keyword 'select'. may be it is a syntax error but i m not able to resolved it.as i have not worked in stored procedures before.
below is my code
///
alter procedure trans_data3 @nop int, @nop1 int as set @nop=select noofparts from test_products set @nop=select noofparts from test_products1 insert test_products1(prod_name,rate,qty) select prod_name,rate,qty from test_products --if (select @nop=noofparts from test_products1) > 0
if @nop > 0 and @nop1 > 0 insert test_products1_parts(noofparts,weight,height) select noofparts,weight,height from test_products_parts
go
///
in my code I want if column 'noofparts'of table 'test_products'>0 then second insert command should execute.
Hi, I am trying to execute a Stored Procedure. When i parse it, it's good, but when i click on Excecute i get the following error :"There is already an object named 'Waiters' in the database." Waiters is the name of the stored procedure. What's the problem?
I am trying to write what I thought would be a basic stored procedure, this is my first look at these, an I am running in to an error. The Precedure is as follows:
ALTER PROCEDURE uspSelectBarItemID
(
@BarTabID INT,
@DrinkID INT
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID FROM [Bar Items] WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
BarItemID is the primary key of the table with BarTabID and DrinkID as foreign keys in the same tabel.
The error I get is "Incorrect syntax near '@ReturnBarItemID' Must declare the scalar variable
Im think I'm not to far off, but a couple of hours a searching the net and changing things is getting me know where.
I am current creating a Oracle membership provider in dotnet based on the MS membership provider. One of the stored procedures that need converting is shown below. The variable UserId (highlighted blue) is declare and never assigned to but is used within the update query also highlighted in blue. If this is an oversight then I presume that this part of the stored procedure is never actually executed. Can somebody put an eye over this code to confirm.
Thanks.
ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName] @ApplicationName nvarchar(256), @UserName nvarchar(256), @CurrentTimeUtc datetime, @UpdateLastActivity bit = 0 AS BEGIN DECLARE @UserId uniqueidentifier
IF (@UpdateLastActivity = 1) BEGIN SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, @CurrentTimeUtc, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut,m.LastLockoutDate FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
IF (@@ROWCOUNT = 0) -- Username not found RETURN -1
UPDATE dbo.aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE @UserId = UserId END ELSE BEGIN SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut,m.LastLockoutDate FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId
IF (@@ROWCOUNT = 0) -- Username not found RETURN -1 END
I get an error when I try to run this stored procedure. The code stops on the first line where I try to open the connection. The error message reads something like "The format of the string doesn´t comply with the specification beginning on index 0"
SqlConnection sqlConnection1 = new SqlConnection("MyConnectionString"); //Code stops here
SqlCommand cmd = new SqlCommand();
Object returnValue;
cmd.CommandText = "get_startdate"; //Name of the stored procedure
I am trying to execute a store procedure from ASP/VB but it fails with the message: Incorrect syntax near 'InitProject'. 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: Incorrect syntax near 'InitProject'.Source Error:
Line 24: cmd.Parameters("@ProjectId").Value = 3 Line 25: cn.Open() Line 26: cmd.ExecuteNonQuery() Line 27: cn.Close() Line 28: End Sub Here is my code: 'Execute the InitProject stored procedure 'Create the connection from the string in the web.config file Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString) 'I want to execute InitProject stored procedure Dim cmd As SqlCommand = New SqlCommand("InitProject", cn) 'With the parameter @ProjectId = 3 cmd.Parameters.Add(New SqlParameter("@ProjectId", Data.SqlDbType.Int)) cmd.Parameters("@ProjectId").Direction = Data.ParameterDirection.Input cmd.Parameters("@ProjectId").Value = 3 cn.Open() 'But this fails cmd.ExecuteNonQuery() cn.Close() And my stored procedure is defined as: [dbo].[InitProject] @ProjectId int -- Add the parameters for the stored procedure hereASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure hereinsert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )select @ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID from MATERIAL_TEMPLATEEND The store procedure works fine when I do exec InitProject 3 in sql query.
I want to call a stored procedure in ASP.Net 2.0. I've already made sure that the SP contains no error by the Query Analyzer.However, when I try to run this in the ASP.Net application, error occured.This error message is {"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."} Calling the SP via a Class1 Public Function ExecuteStoredProcedure(ByVal Name As String, ByVal Para() As String, ByVal Value() As String) 2 If Para.Length <> Value.Length Then Exit Function 3 SqlCmd = New SqlCommand 4 SqlCmd.Connection = SqlConn 5 SqlCmd.CommandText = Name 6 SqlCmd.CommandType = CommandType.StoredProcedure 7 For i As Integer = 0 To Para.Length - 1 8 'SqlCmd.Parameters.AddWithValue(Para(i), Value(i)) 9 Dim p As New SqlParameter(Para(i), SqlDbType.NVarChar) 10 p.Direction = ParameterDirection.Input 11 p.Value = Value(i) 12 SqlCmd.Parameters.Add(p) 13 Next 14 SqlCmd.ExecuteNonQuery() 15 End Function The Code behind file 1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 2 If txtFromDate.Text = "" OrElse txtToDate.Text = "" Then 3 trMsg.Visible = True 4 Exit Sub 5 End If 6 7 Dim ts As New Thread(AddressOf GenerateReport) 8 ts.Start() 9 10 MultiView1.ActiveViewIndex = 1 11 lblConfirmationMessage.Text = "Report generating in progress." & vbNewLine & "You can continue using other functions." 12 End Sub 13 14 Protected Sub GenerateReport() 15 Dim userName As String = CType(Session("User"), clsUser).UserName 16 Dim from As String = txtFromDate.Text 17 18 Dim conn As New clsConnector 19 conn.OpenConnection() 20 'conn.Insert("EXEC gen_report '" + txtReportName.Text + "','" + txtDescription.Text + "','" + userName + "','" + txtFromDate.Text + "','" + txtToDate.Text + "';") 21 conn.ExecuteStoredProcedure("gen_report", _ 22 New String() {"@name", "@remark", "@by", "@fromdate", "@todate"}, _ 23 New String() {txtReportName.Text, txtDescription.Text, userName, txtFromDate.Text, txtToDate.Text}) 24 conn.CloseConnection() 25 End Sub 26 Thanks!
Hello frend...i have a problem with my application when i'm trying to delete a certain data from gridview...i have a store procedure that already create in my mssql server...and in datasource i'm using command that i've already created in my mssql server...the problem is i dont know how i can send my value to the parameter in datasource.....and one more thing what is exaclty the error with 'Store procedure has <my function> too many argument" occur? Please help me....so i paste my code below to easier and detect my problem....(sorry my english are no good).... 1) This is my code in asp.net <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:SqlDataSource ID="SqlDSRole" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>" DeleteCommand="Roles_delete" DeleteCommandType="StoredProcedure" SelectCommand="Roles_view" SelectCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="roleName" Type="String" /> </DeleteParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="Role_application_id" DataSourceID="SqlDSRole" ForeColor="#333333" GridLines="None" PageSize="4"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:CommandField ShowDeleteButton="True" /> <asp:TemplateField HeaderText="Role Application Id" Visible="False"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:Label ID="roleApplicationIdLabel" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Role Name" > <ItemTemplate> <asp:Label ID="roleName" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo" ></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="roleNameTxt" runat="server" Text='<%# Bind("Role_name") %>' Width="200px"></asp:TextBox> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo"></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Role Description"> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="roleDescriptionTxt" runat="server" Height="30px" Text='<%# Bind("Role_description") %>' TextMode="MultiLine" Width="300px"></asp:TextBox> </EditItemTemplate> <AlternatingItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label> </AlternatingItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#E0E0E0" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </ContentTemplate> </asp:UpdatePanel> 2) And this is my Store procedure that i created in mssql server Store Procedure: Roles_view set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Roles_view] AS BEGIN SET NOCOUNT ON; SELECT * FROM Role END Store Procedure: Roles_delete set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Roles_delete] @roleName varchar(50) AS BEGIN SET NOCOUNT ON; DELETE FROM Role WHERE Role_name LIKE @roleName END 3) The problem is when i'm trying to delete my certain data, the message box appear and say "Procedure or function Roles_delete has too many argument specified".what should i do????anabosy please help me..
I have an application where I am bcping a file into a holder table and then looping through each of the rows in that table to add it into the main table for the app. If the data is improperly formatted (ie someone accidently enters 39.Y6 instead of 39.66), we still want to keep it in the system, so we can update the bad fields manually and then import it into the system and keep going through the loop so that if there are 50 rows to import and only one of them is bad, the other 49 will still get imported fine.
I am putting each field from the holder table into a variable and passing them into an existing stored procedure that updates the main table. I had originally checked each one of those variables that had to be numeric or a date to make sure that it was correct before passing them into the procedure but there are about 30 fields so this made the application run unacceptably slow. I then just checked the @@Error value after passing them into the stored procedure (tried after the sp and after the INSERT statement inside the sp) to get that this row didn't insert correctly and move onto the next one. The problem is that the "Error converting data type varchar to numeric" doesn't seem to be handled by the error handling and just bombs the whole thing, so none of the subsequent rows or processing is done.
Is there any way to handle this error and continue the processing without the whole stored procedure crashing? The data entry is being outsourced to India (grrr...), so I don't have any control over checking the data when they enter it and have to do it from within my application on the database side.
The SqlParameter with ParameterName 'xxxxx' is already contained by another SqlParameterCollection.
I'am trying to create an dataset in which data of the different connectionpoints are separated into different tables. Herefor i'm using an stored procedure.
Below you'll find part of the main function and the full function which will execute the stored procedure.
kind regards
main function
Dim Parameters As SqlParameter() = { _ New SqlParameter("@ConnectionPointID", SqlDbType.NVarChar, 255)}
For x = 0 To myds.Tables("ConnectionPoints").Rows.Count - 1 connectionpointID = myds.Tables("connectionpoints").Rows(x).Item("Eancode") Parameters(0).Value = connectionpointID mydb.doStoredProcedure("SP_EDS_Dyomes_XML", Parameters,connectionpointID.ToString, myds) Next
Public Overloads Function doStoredProcedure( _ ByRef Mycommand As SqlCommand, _ ByRef myds As DataSet, _ ByVal Table As String)
Dim myda As New SqlDataAdapter
Mycommand.CommandTimeout = 180 If Mycommand.Connection Is Nothing Then Dim mydb As New Database Mycommand.Connection = mydb.generateconnection End If If Mycommand.Connection.State = ConnectionState.Closed Then Mycommand.Connection.Open() End If CheckParameters(Mycommand) myda.SelectCommand = Mycommand
Try myda.Fill(myds, Table) Catch ex As Exception Debug.Write(ex.Message) End Try myda.Dispose() Mycommand.Dispose()
I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??
Create Procedure AddLog ( @User char(10), @DLogon DateTime(8), @Status bit ) As Insert Into Log(UserID,LogInTime,Online) Values(@User,DLogon,Status)
ERROR:
Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9 The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
