I am a little confused about something and I am hoping that someone out there has some insight.
Within a stored procedure, I have error handling code just after an INSERT statement. the error handling code basically checks @@ERROR, and if non-zero, logs a message to the NT Event Log. One of the things I want to be able to handle is if the transaction log on the database is full, and the INSERT fails for this reason. However, it appears that for this particular situation (and others I've seen in the past), the stored procedure aborts at the INSERT statement. Never makes it to the error handling code. Is there any way to control this behaviour?
I really need to put in place a failure scenario, if the insert fails, but i can't do that if the stored proc. just aborts on certain types of insert failures.
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.
I have a huge stored procedure. part of the query is, the cursor is open and it consist of the implementation for subquery, I want to do an implementation if this subquery return more than one value, catch the error, but continue the cursor operation.
my SQL procedure part looks like:
---code to open the cursor here WHILE @@FETCH_STATUS=0 BEGIN SELECT @attachmentCount=count(metaDataID) FROM View_1 WHERE parentMetaDataID='' + ( SELECT metaDataID FROM View_1 WHERE metaDataStorageID = @metaStorageID AND parentMetaDataID='0') + '' AND metaDataContentTypeID=@metaDataContentTypeID
--error handling DECLARE @err int SELECT @err = @@error IF @err <> 0 BEGIN FETCH NEXT FROM CursorDataStorageID INTO @metaStorageID CONTINUE END
FETCH NEXT FROM CursorDataStorageID INTO @metaStorageID END
-----------------
when I execute this query in SQL management studio, it does return me the no of rows with the msg:
Msg 512, Level 16, State 1, Procedure sp_AdvanceSearchHugeExecution, Line 522
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But when the stored procedure is called from the within the code ASP.Net, to fill the dataset, I get an exception.
my C# code is simple to fill the dataset using data adapter in try catch block.
Can anyone please suggest how will I able to fill dataset successfully overcoming this mesg?
OK, i'm trying to do some error checking on stored procedures and amfollowing the advise in Erland Sommarskog's 'Implementing ErrorHandling with Stored Procedures' document.Can anybody help with my stored procedures and why it keeps erroring atthe '-- Create new Address Detail stage'? The errorCode value that isbeing return in my web app is 0, so i'm not even sure why it's evenraising the error!!Rather than executing the INSERT INTO AddressDetail in myCreateSupplier procedure and checking for errors, i'd like to be ableexecute a CreateAddressDetail SP, so that i can reuse it throughout myweb app.New suppliers must have a contact address associated with it, so ifthere's an error creating the suppliers address, i need myCreateSupplier stored procedure to ROLLBACK and not create the newsupplier. That's why i'm not doing two separate calls to the proceduresfrom my app code.Any suggestions are most appreciated.Many thanksDan Williams.CREATE PROCEDURE CreateSupplier@supplierName varchar(50),@userId bigint,@address varchar(50),@town varchar(50),@county varchar(50),@postCode varchar(15),@contactName varchar(50)ASBEGINDECLARE @newSupplierId as bigintDECLARE @newAddressDetailId as bigintDECLARE @errorCode as bigintSET NOCOUNT ONBEGIN TRANINSERT INTO Supplier(supplierName, accOpenedBy, accOpenedDate)VALUES (@supplierName, @userId, getDate())SET @newSupplierId = SCOPE_IDENTITY()-- Check for an error creating new supplierSELECT @errorCode = @@ERRORIF (@errorCode <> 0) BEGIN ROLLBACK TRAN RAISERROR ('Error creatingsupplier',16,1) RETURN @errorCode END-- Create new Address DetailEXEC @errorCode = CreateAddressDetail @address, @town, @county,@postCode, @contactName, @newAddressDetailId OUTPUTSELECT @errorCode = coalesce(nullif(@errorCode, 0), @@error)if @errorCode <> 0 BEGIN ROLLBACK TRAN RAISERROR ('Error creatingaddress. ErrorCode = %d',16, @errorCode) RETURN @errorCode ENDCOMMIT TRANSET NOCOUNT OFFRETURN @newSupplierIdENDGOCREATE PROCEDURE CreateAddressDetail@address varchar(50),@town varchar(50),@county varchar(50),@postCode varchar(15),@contactName varchar(50),@newAddressDetailId bigint OUTPUTASBEGIN-- Create new AddressDetailDECLARE @errorCode as bigintSET NOCOUNT ONBEGIN TRANINSERT INTO AddressDetail(address, town, county, postCode, contactName)VALUES (@address, @town, @county, @postCode, @contactName)SET @newAddressDetailId = SCOPE_IDENTITY()-- Check for an error creating new addressSELECT @errorCode = @@ERRORIF (@errorCode <> 0)BEGINRAISERROR ('Error creating new address detail',16,1)ROLLBACK TRANENDELSECOMMIT TRANSET NOCOUNT OFFRETURN @newAddressDetailIdENDGO
I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure: Code: ( text )
Hi all, I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have: ExecuteNonQuery(cmd); return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower(); Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so: return (int)ExecuteNonQuery(cmd); Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help. Thanks
I have a select query that soemtimes errors out. Hence I need to get an email , if this query errors out. I have my email etc setup... However, how can i detect in a stored proc, that this qry has an error? I used this, but if it had an error, it does not return any value for @@error.
BEGIN TRANSACTION select * from vw_dailydel IF @@ERROR != 0 BEGIN print @@ERROR RAISERROR('There was an error here.', 11, 1) RETURN END
Hi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated
Hi, I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @@rowcount and @@error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?Thanks.
I have a stored proc that loops through records using a cursor. If during the loop an error occurs i need to trap the error and write it to a log file. Error handling needs to be placed in the update and insert sections and the error that occurs needs to get recorded along current row from the cursor.
Here is the code: declare @tier_id int, @tier_desctext varchar(50), @tier_shortdesc varchar(50), @long_name varchar(50), @short_name varchar(50), @rec_count int
--Cursor for UES data DECLARE cr_ues_tier INSENSITIVE CURSOR FOR SELECT DISTINCT tier_id, tier_desctext, tier_shortdesc FROM "dbsourcedayoldprod".ues.dbo.Tiers
OPEN cr_ues_tier
--Select cursor values into local variables FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc WHILE (@@FETCH_STATUS <> -1) BEGIN
--Set Data Mart variables SELECT @long_name = long_name, @short_name = short_name FROM uesrpt..coverage_level WHERE ues_tier_id = @tier_id
--Set the record counter SET @rec_count = (select ues_tier_id from uesrpt..coverage_level where ues_tier_id = @tier_id)
--Are there exsisting records if so go to the update section IF @rec_count <> 0
--Update Data Mart values if they have changed BEGIN IF @long_name <> @tier_desctext OR (@long_name IS NULL AND @tier_desctext IS NOT NULL) OR (@long_name IS NOT NULL AND @tier_desctext IS NULL)
OR @short_name <> @tier_shortdesc OR (@short_name IS NULL AND @tier_shortdesc IS NOT NULL) OR (@short_name IS NOT NULL AND @tier_shortdesc IS NULL)
UPDATE uesrpt..coverage_level SET long_name = @tier_desctext, short_name = @tier_shortdesc WHERE ues_tier_id = @tier_id END
--Rows don't exsist in the Data Mart - Insert new rows ELSE INSERT INTO uesrpt..coverage_level (ues_tier_id, long_name, short_name) SELECT @tier_id, @tier_desctext, @tier_shortdesc
--Get next row from UES cursor FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc END
Hi all,I have a sproc that uses OpenRowset to an Oracle db. If OpenRowseterrors, it terminates the procedure. I need it to continueprocessing. Is there any workaround for this?ThanksPachydermitis
If an update in a stored procedure fails/errors (as in (a) below) the procedure will not continue with (b) - I need the code in (b) to run despite whether the previous update was successful or not - Any ideas?
(a) if(@Data2 = 6) begin update SCHEDULE set Start_CallBack = getdate() where (Block = @Block) end
(b) WHILE @Block_Count > 0 BEGIN UPDATE BLOCK SET Status = @Block_Status END
I have a Stored Proc that is called by a SQL Job in SQL Server 2000. This stored proc deadlocks once every couple of days. I'm looking into using the @@error and try to doing a waitfor .5 sec and try the transaction again. While looking around google I've come across a few articles stating that a deadlock inside a Stored Proc will stop all execution of the stored proc so I will not be able doing any error handling. Is this true? Does anyone have any experience that could help me out?
I know the best solution would be to resolve why I get a deadlock. We are currently looking into that but until we can resolve those issues I would like to get some type of error handling in place if possible.
Hi,I a stored procedure that inserts a record into a table asbelow.The insert works OK, but if the insert violates a uniqueindewx constraint on one of the columns, the proc terminatesimmediately, and does NOT execute the 'if @@ERROR <> 0'statement.Am I doing something wrong, or do I need to set an attributesomewhere?tia,Billbegin traninsert into Users(UserName, UserPWD, Lname, Fname, UserDesc)values (@userName, @userPWD, @lname, @fname, @userDesc)if @@ERROR <> 0beginrollback transet @returnCode = -2set @errMsg = 'SQL error '+ convert(varchar(6), @@ERROR)+ ' occurred adding user '+ @userNameend
Can anyone let me know the prefered method for handling stored procedure errors. I usually trap the error in the stored proc and then return a value using an output parameter e.g stored proc if @@error <> 0 beginset @returnValue = -1returnend c#com.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;con.Open();com.ExecuteNonQuery(); int result = (int)com.Parameters[0].Value; if (result == -1){//throw exception}else{//do whatever}
I would like to capture any errors thrown by linked server in my stored procedure, How do I do this? eg: Select statement in sp to remote server fails if login credentials were changed for some reason on remote server. @@error is not able to capture this error and stored procedure is terminated when this type of error occurs.
I have tried and tried but I get all types of errors.Someone I hope has done this and perhaps can explain in more detail how to get this done. I have my Store Procedure.... I added output to the data I will be needing. at the bottom I added my code....I need to some how run this procedure...Get all the info in the select statement and display it in a form.I want to use a variable for each field...Ihave no clue why my code does not work... CREATE procedure dbo.App_Login_NET ( @LoginName nvarchar(15), @Password NvarChar(15) ) as select UserName OUTPUT, UserPassword OUTPUT, UserClinic OUTPUT, UserTester OUTPUT from Clinic_users where UserName = @LoginName and UserPassword = @Password GO
When I type in a 'correct Username/Password' it gives me this error Procedure or function Appt_Login_NET has too many arguments specified. I just want to contain the values in separte variables for later use...Also how do you use rowcount????
my Code
Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest") Dim cmd As New String("Appt_Login_NET") Dim mycommand As New SqlCommand(cmd, con)
I was under the impression or i always got following entries in SQL Agent error log when server restart and there are some entries in sql error log as well which confirms that server restarted. but today when i was checking error log i saw same entreis in SQL agent error log but there were no entries in server error log. now i got really confused whether my server is restarted or not........... because if it is restarted then there must be some messages in server error log..... can some one please let me know when and why following messages appear in SQL Agent Error Log
Information,[171] There are 9 alert(s) in the alert cache Warning,[425] delay_between_response attribute (10 sec) of alert (9) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (8) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (7) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (6) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (5) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (4) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (3) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (1) is less than poll interval for this alert (20 sec) Warning,[425] delay_between_response attribute (10 sec) of alert (2) is less than poll interval for this alert (20 sec) Information,[170] Populating alert cache... Information,[168] There are 3 job(s) [0 disabled] in the job cache Information,[297] SQLServer Message: 0<c/> SQLServerAgent Monitor started successfully. [SQLSTATE 01000] Information,[133] Support engine started Information,[167] Populating job cache... Information,[110] Starting SQLServerAgent Monitor using '' as the notification recipient... Information,[193] Alert engine started (using Eventlog Events) Information,[146] Request servicer engine started Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect Information,[174] Job scheduler engine started (maximum worker threads: 800) Information,[129] SQLAgent$XYZ starting under Windows NT service control Error,[364] The Messenger service has not been started - NetSend notifications will not be sent Information,[124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 800) Information,[124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 800) Information,[124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 800) Information,[124] Subsystem 'Merge' successfully loaded (maximum concurrency: 800) Information,[124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 800) Information,[124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 200) Information,[124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 800) Information,[124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 80) Information,[124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 80) Information,[124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 160) Information,[432] There are 10 subsystems in the subsystems cache Information,[431] Populating subsystems cache... Information,[339] Local computer is ABC running Windows NT 5.2 (3790) Service Pack 1 Information,[310] ** processor(s) and **** MB RAM detected Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is ServerName Information,[102] SQL Server ODBC driver version 9.00.2047 Information,[101] SQL Server ABC version 9.00.2047 (0 connection limit) Information,[100] Microsoft SQLServerAgent version 9.00.2047.00 (x86 unicode retail build) : Process ID 8100 Error,[298] SQLServer Error: 2812<c/> Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)
I have a transaction that calls one other sproc and also executes another set of queries, but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."There is NO transaction in the sproc AddTag. It is also included below.Here is the sproc with the transaction:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddUserHaveTag] 6 (7 @UserHaveID int,8 @Tag varchar(24),9 @UserHaveTagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 DECLARE @ErrorCode int14 DECLARE @TagID int15 DECLARE @TagExists bit16 17 BEGIN TRAN 18 19 -- Call proc to add tag to Tags table 20 EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT 21 22 -- Check for errors 23 IF @ErrorCode <> 0 GOTO ERROR24 25 -- Check for existing record, otherwise insert 26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)27 BEGIN28 SET @UserHaveTagExists = 129 RETURN 030 END31 ELSE32 BEGIN33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)34 SET @UserHaveTagExists = 035 END 36 37 -- Check for errors 38 IF @ErrorCode <> 0 GOTO ERROR39 40 COMMIT TRAN 41 42 ERROR:43 IF (@ErrorCode <> 0)44 BEGIN45 PRINT 'Unexpected error occurred!' 46 ROLLBACK TRAN47 END Here is the AddTag sproc:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON 3 go4 5 ALTER PROCEDURE [dbo].[AddTag] 6 (7 @Tag varchar(24),8 @TagID int OUTPUT,9 @TagExists bit OUTPUT 10 )11 AS12 SET NOCOUNT OFF13 14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)15 BEGIN16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag17 SET @TagExists = 118 RETURN 019 END20 ELSE21 BEGIN22 INSERT INTO Tags (Tag) VALUES (@Tag)23 SET @TagID = SCOPE_IDENTITY()24 SET @TagExists = 025 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions. Thanks,Travis
I am using the HitSoftware driver to pass data to an AS400 from a SQL 7 database. Data makes it fine to 2 of the 4 tables but I cannot get the syntax correct to even get out of the S/P edtitor in SQL for the other 2.
I have fields in the SQL S/P defined as VARCHAR. The target fields on the AS400 are ALPHA. So I figured the insert statement to look like:
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?
