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


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

sp_executesql vs. sp_sqlexec

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
what is the difference between these two stored procedures?


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

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!


SP_EXECUTESQL / RAISERROR

  

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

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
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