.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

SP_EXECUTESQL / RAISERROR

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server
 

I am using SP_EXECSQL to execute a select and potential insert into a dynamically named table if the value does not exist.  I am trying to catch / handle key violation errors and re-raise all other in an effort to terminate the batch and inform the caller of the error.  Unfortunately, the batch code after SP_EXECUTESQL continues to execute in the event of non 2601 errors.  Here is the relevant block inside of my sp:

DECLARE @ErrorMessage NVARCHAR(4000)
	DECLARE @ErrorSeverity INT
	DECLARE @ErrorState INT

	SELECT @WID = WordID FROM ' + @WTable + ' WHERE Word = @Word 
	IF @WID IS NULL 
		BEGIN TRY	
			INSERT INTO ' + @WTable + ' (Word) VALUES (@Word)
			SET @WID = SCOPE_IDENTITY()
		END TRY
		BEGIN CATCH
			SET @ErrorMessage = ERROR_MESSAGE()
			SET @ErrorSeverity = ERROR_SEVERITY()
			SET @ErrorState = ERROR_STATE();			
			
			IF ERROR_NUMBER() = 2601 --key violation, two threads tried to insert the same word at the same time
				SELECT @WID = WordID FROM ' + @WTable + ' WHERE Word = @Word 
			ELSE				
				RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) --some other error, raise it back to client
		END CATCH
	'					

SET @ParamDefs = N'@Word nvarchar(50), @WID bigint OUTPUT'
EXEC SP_EXECUTESQL @SQL, @ParamDefs, @Word, @WID OUTPUT

--Batch execution continues on here even though the catch block raises an


View Complete Post


More Related Resource Links

DTA doesn't find referenced table form stored procedure with SP_executesql

  
Using the SQL2008R2 profiler and DTA connected to a SQL2000 server. I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql statement. When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it. The DTA generates no Recommendatioin at all. How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do? Regards

sp_executesql vs. sp_sqlexec

  
what is the difference between these two stored procedures?

place a condition in the RAISERROR

  
i have a raiserror statement ansd i need to place a condition to not raise the error when specific records exist in the table.   BEGIN RAISERROR( 'TABLE1 records do not exist in refTABLE. Please verify with John.',16,1) END this works ***************************************************** BEGIN iif ((TABLE.TYP_ID) NOT IN (10,20,30,40)) THEN RAISERROR( 'TABLE1 records do not exist in refTABLE. Please verify with John.',16,1) END I need the correct syntax....)

Comparing Binary values in sp_executesql

  

Hello,

I am using a stored procedures to handle some maintainance stuff in my DB, I have a binary field called Container, that for all rows that have that specific container I need to remove the oldest n rows, I also have a time field so I can order by it, in addition my table is recursive and so if I delete one value I need to delete all it's children.

So in short what I did is create a CTE that selects all the lines that I want to delete, using SELECT TOP @n FROM mytable WHERE Container=@Container

Now I had to use sp_executesql due to using a variable to declare the TOP value, but now it won't let me compare the binary value stating that nvarchar and varbinary data types are incompatible through add operator (I tried to do Container='+@Container+')

How can I do this properly?

Thanks!


Explain Plan for sp_executesql

  

Hi,

please bear with me as I'm new to Sql Server.

I have extracted a SQL statement from a trace file using the sql profiler tool. This was for a SharePoint application. The SQL statement includes the stored procedure sp_executesql with a number of scalar variables and I was wondering how do I get the explain plan that was actually used when executing the sql.

Is there a system view I can query that gives this sort of information?

Many thanks in advance for any help.

 


using sp_executesql with different parameters

  

I've recently learned that it's much better to use sp_executesql with parameters instead of Execute for dynamic queries. The problem I am facing is that I have different parameters that are passed into my WHERE clause depending on the filters user selected on the website. Below is the example of my query. In this query there are 2 parameters: Category and Price. Sometime Price should be used in the WHERE and sometimes it shouldn't. But how can I define one SP to handle different scenarious?

Thank you in advance.

Eugene.

DECLARE @Category nvarchar(40)
DECLARE @Price int
DECLARE @SQLString NVARCHAR(3000)DECLARE @ParmDefinition NVARCHAR(1000)
set @Category=130
set @Price=125


SET @SQLString = 'SELECT distinct TOP 5 Products_Brand As Brand, Count(DISTINCT Products_pID) As CNT '
SET @SQLString = @SQLString  +
   'FROM _dta_mv_0_9987  WITH (NOEXPAND) ' +
   'LEFT OUTER JOIN Address ON ProductsLocation_aID = Address.aID ' +
   'WHERE ((ProductsLocation_aID = -1) OR (Address.City LIKE ''gft'' AND Address.State LIKE ''tx''  )) ' +
   'and products_subcategory = @ParamCategory ' +
   'and productslocation_price>@ParamPrice' +
   ' GROUP BY Prod

does sp_executesql output return more than one record

  
Does sp_executesql return more than one row outputs.
 I am having a issue with it: 

my code is supposed to return 2 rows in the output but it is returning onyl one row(second row).
I guess, the first one is being overwritten by the second one. I am using sp_executesql because the sql string is being dynamically built.

the output is supposed to be like 
sno sname
 1   clay
 2   teri
instead it is showing like this 
sno sname
 2   teri


any idea on this?




Problem with sp_executesql

  

Dear all

I created a function in  mssql 2005 express.

//return the current date with format :ex: oct 27 2010 12:00AM

ALTER FUNCTION [dbo].[fc_getcurrentDate]()
RETURNS NVARCHAR(36)
AS
BEGIN
  declare @date NVARCHAR(36)
  set @date=DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE()))
  return @date
END






and after that I created 2 SQL query do the same job:

1.Using sp_executesql

SELECT dbo.fc_getcurrentDate()
EXEC sp_executesql
N'SELECT * FROM BugNet_Issues WHERE cast(IssueDueDate as nvarchar(36)) = @p0',
N

Check Actual Error from sp_executesql

  
Hello,

CREATE PROC Test
AS
DECLARE @i int
BEGIN TRY
	EXEC @i = sp_executesql N'SELECT 1/0'
	PRINT 'Completed...'
END TRY
BEGIN CATCH
	SELECT @i
	SELECT ERROR_NUMBER(), ERROR_MESSAGE()
	IF @i <> 0
		PRINT 'Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE()
END CATCH
GO
I know the above statement fails and comes to Catch block, but the value of @i is NULL. Why? How can I get the actual return value when the

RAISERROR does not cause OLE DB Source Task to fail in SSIS 2008?

  

Greetings, 

I have a stored procedure with a TRY / CATCH block.  In the catch block I capture information about the error.  And then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.

I notice that there is a defect reported which is about Execute SQL Task. "Execute SQL Task not signalling failure on a Raiserror." https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828&wa=wsignin1.0

It was fixed in SSIS 2008. But I found that RAISERROR still cannot fail a data flow source task. The stored procedure is executed in OLE DB Source task. The RAISERROR is justed omitted by OLE DB Source task and the task box turns green. But if there is any exception throw by system (eg. 1/0), it would fail the task.

Here are the two stored procedures I tried.

CREATE PROCEDURE [dbo].[GenerateErrorA]
AS
BEGIN
 BEGIN TRY 
  Select 1/0 AS A
 END TRY
 BEGIN CATCH
  RAISERROR('Error Raised', 16, 1)
 END CATCH
END

CREATE PROCEDURE [dbo].[GenerateErrorB]
AS
BEGIN
 SET NOCOUNT ON;
 Select 1/0 AS A
END

Only stored proce

SQL replication error : MSSQL raiserror buffer overflow

  

Hi.

I have 2 server run SQl Server 2005 that replicate each other. It is in secure environment with firewall and IPS/IDS.

Here, I've a problem when I do merge replication. The replication is failed. When i check at IPS/IDS log, then it state "MSSQL Raiserror possible buffer overflow".

How do I manage to solve this problem ?


There's mountain to climb....
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend