IF @Url > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET URL = @Url ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'URL', @Url SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Org_Type > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET ORG_TYPE = @Org_Type ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'ORG_TYPE', @Org_Type SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Par_Org_Id IS NOT NULL BEGIN UPDATE USER1122500.ORGANIZATION SET PAR_ORG_ID = @Par_Org_Id ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'PAR_ORG_ID', @Par_Org_Id SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Name > ' ' OR @Location > ' ' BEGIN
IF EXISTS (SELECT ROW_ID FROM USER1122500.ORGANIZATION WHERE NAME = @Name AND LOCATION = @Location) BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'NAME', @Name, N'LOCATION', @Location SET @Error_Code = 55004 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code)-- RAISERROR(@Error_Text, 10, 1, @spName, @Error_Code, 'ORGANIZATION', @errorMsg) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'sp_name',@spName)) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'err_cd',@Error_Code)) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'tbl_name','ORGANIZATION')) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'err_msg',@errorMsg)) RETURN(@Error_Code) END
IF @Name > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET NAME = @Name ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'PAR_ORG_ID', @Name SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
IF @Location > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET LOCATION = @Location ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'LOCATION', @Location SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Hi dear users, I have created procedure which main function is to update row of the table. Below, there is SQL code of this procedure.
Code Block
CREATE PROCEDURE Zakl_UpdateRow @zakl_id INT, @zakl_nazwa VARCHAR(25), @zakl_miasto VARCHAR(20), @zakl_ulica VARCHAR(30) AS UPDATE Zaklady SET Zakl_Nazwa=@zakl_nazwa, Zakl_Miasto=@zakl_miasto, Zakl_Ulica=@zakl_ulica WHERE Zakl_ID=@zakl_id
When I execute this procedure I have to give all declared variables: "@zakl_id", "@zakl_nazwa" and so on. Sometimes I don't want to update all columns in the row but only one f.ex name of the street ("Zakl_Ulica"). And I want other colums of the row to stay the same ("Zakl_Nazwa","Zakl_Miasto"). There is no point in rewriting values of "Zakl_Nazwa","Zakl_Miasto" as I want only to change value of "Zakl_Ulica".
this is execution clause
Code Block
EXECUTE Zakl_UpdateRow @zakl_id=70, @zakl_ulica='kosciuszki 7' If I don't ascribe value to the variables (@zakl_nazwa, @zakl_miasto ), the columns in the row ("Zakl_Nazwa","Zakl_Miasto") should stay unchanged.
So what should I modify in my procedure, to have it working?
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
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
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 ,@AccountNumber VARCHAR(30) = '' ,@ReferenceNumber VARCHAR(30) = '',@Tier INT = 0 ,@Status VARCHAR(5) = '' ,@UserID INT ,@Month DateTime = NULL ,@FromDate DateTime = NULL ,@ToDate DateTime = NULL,@OriginalMin decimal = 0 ,@OriginalMax decimal = 0,@CurrentMin decimal = 0 ,@CurrentMax decimal =0 ,@lstAmountSelect VARCHAR(3),@IsActive bit = 1
ASDECLARE @SQLTier1Select VARCHAR(2000) ,@SQLTier2Select VARCHAR(2000) ,@Criteria VARCHAR(2000) ,@SQL VARCHAR(8000) ,@CRI1 VARCHAR(100) ,@CRI2 VARCHAR(100) ,@CRI3 VARCHAR(100) ,@CRI4 VARCHAR(100) ,@CRI5 VARCHAR(100) ,@CRI6 VARCHAR(200) ,@CRI7 VARCHAR(500) ,@CRI8 VARCHAR(500) ,@CRI9 VARCHAR(500) SELECT @CRI1 = '' ,@CRI2 = '' ,@CRI3 = '' ,@CRI4 = '' ,@CRI5 = '' ,@CRI6 = '' ,@CRI7 = '' ,@CRI8='' ,@CRI9='' ,@Criteria = '' SELECT @DebtorName = REPLACE(@DebtorName,'''',''''''); Print @DebtorName if(SELECT UserTypeID FROM dbo.tbl_Security_Users Where UserID = @UserID) = 3 AND @ClientCode = '' return (-1)IF LEN(@DebtorName) > 0 SET @CRI1 = ' AND Name like ' + '''%' + @DebtorName + '%'''IF @DebtorNumber > 0 SET @CRI2 = ' AND Number = ' + CAST(@DebtorNumber AS VARCHAR(7))IF LEN(@AccountNumber) > 1 SET @CRI3 = ' AND AccountNumber like ' + '''%' + @AccountNumber + '%'''IF LEN(@ReferenceNumber) > 0 SET @CRI4 = ' AND Account like ' + '''%' + @ReferenceNumber + '%'''IF LEN(@ClientCode) > 1 SET @CRI5 = ' AND Customer = ' + '''' + @ClientCode + '''' SET @Status = RTRIM(@Status) IF ((@Status Not IN ('ALL','ALA','ALI')) AND (LEN(@Status)>1)) BEGIN
IF(@Status = 'PAID') SET @CRI6 = '' IF(@Status = 'CANC') SET @CRI6 = ' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'
END --PRINt @CRI6IF LEN(CONVERT(CHAR(8), @Month, 112)) > 0 BEGIN
IF(LEN(CONVERT(CHAR(8), @FromDate, 112)) > 0 AND LEN(CONVERT(CHAR(8), @ToDate, 112)) > 0 ) BEGIN SET @CRI7 = ' AND Received BETWEEN ' + '''' + CONVERT(CHAR(8), @FromDate, 112)+ '''' + ' AND ' + '''' + CONVERT(CHAR(8), @ToDate, 112) +''''END ELSEBEGIN SET @CRI7 = ' AND DATEPART(mm, Received) = DATEPART(mm, ' + '''' + CONVERT(CHAR(8), @Month, 112) + '''' + ') AND DATEPART(yy, Received) = DATEPART(yy, ' + '''' + CONVERT(CHAR(8), @Month, 112) + ''''
END END IF @lstAmountSelect='ALL' SET @CRI8='' else IF @lstAmountSelect = 'DR' BEGIN SET @CRI8=' AND OriginalBalance >= '+ convert(Varchar,@OriginalMin) + 'AND OriginalBalance<=' + convert(Varchar,@OriginalMax)+' AND CurrentBalance >= '+ convert(Varchar,@CurrentMin) + 'AND CurrentBalance<=' +convert(Varchar,@CurrentMax) END ELSE IF @lstAmountSelect = 'OLC' BEGIN SET @CRI8=' AND OriginalBalance < CurrentBalance ' END ELSE IF @lstAmountSelect = 'OGC' BEGIN SET @CRI8=' AND OriginalBalance > CurrentBalance ' END ELSE IF @lstAmountSelect = 'OEC' BEGIN SET @CRI8=' AND OriginalBalance = CurrentBalance ' END SELECT @Criteria = @CRI1 + @CRI2 + @CRI3 + @CRI4 + @CRI5 + @CRI6 + @CRI7 + @CRI8
--PRINT @Criteria --PRINT @CRI7 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 PRINT @SQL --SELECT @SQL EXEC (@SQL)
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
