SP_EXECUTESQL / RAISERROR

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!

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.

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

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