You could rewrite it this way:

DECLARE @sqlRowset nvarchar(4000)

DECLARE @results TABLE (comeColumn int)

set @execStmt = 'sp_stored_procedure'

set @execStmt = 'EXEC master..sp_executesql N''' + @execStmt + ''''

set @sqlRowset = 'SELECT * FROM OPENROWSET(''SQLOLEDB'','''+...

Have you tried with OPENROWSET?

set @execStmt = 'sp_stored_procedure'

set @execStmt = 'EXEC master..sp_executesql N''' + @execStmt + ''''

SELECT * FROM OPENROWSET('SQLOLEDB', @ODS_parms, @execStmt)

OK, so you should find the information you're looking for in the document I posted.

I think you can retrieve the encrypted password, but you won't do much with it.

If you want to migrate logins, follow the guidelines described here: http://support.microsoft.com/?scid=kb;en-us;246133&x=15&y=18

AFAIK, the default trace cannot be altered.

Have you looked it up in BOL?

You can pre-aggregate the results in the pivot source:

IF OBJECT_ID('TempDB..#Table') IS NOT NULL

DROP TABLE #Table

CREATE TABLE #Table (Department VARCHAR(20), EmpID INT, [Month] INT)

INSERT INTO #Table

SELECT 'Marketing',268638,3 UNION ALL

SELECT 'Marketing',268638,3 UNION...

BEGIN/END is used for delimiting blocks in control of flow language. Example:

IF GETDATE() > '2010-06-01'

BEGIN

UPDATE someTable SET someColumn = 'someValue'

UPDATE anotherTable SET...

This should help:

DECLARE @test-2 TABLE (

size int,

sdate datetime

)

INSERT INTO @test-2

SELECT 1 , '2010-07-01 09:21' UNION ALL

SELECT 35, '2010-07-02 09:11' UNION ALL

SELECT 3 , '2010-07-03 09:31' UNION ALL

SELECT 33, '2010-07-04 09:21'

SELECT...

BEGIN / END delimits a block of code, without controlling a transaction. If not already inside a transaction, each statement will execute in an autonomous transaction. Typically BEGIN/END is used...

OMG! I see it can be read that way... 😀

I simply mean it would be quite problematic finding the query that returns the largest amount of rows, because both queries...

If you use sp_executesql sql injection is not an issue.

I don't see any problems.

I would use dynamic sql and sp_executesql.

Here's a great article on how to use dynamic search conditions based on the parameters passed to the procedure:

http://www.sommarskog.se/dyn-search-2005.html#sp_executesql

Glad you found it helpful.

Next time, you could provide your table scripts and some sample data, so that one can test against your tables.

This is how I would do it:

ALTER PROCEDURE [dbo].[Overseas_getEOIWaitingForApproval]

@DepartureDate DATETIME,

@ReturnDate DATETIME,

@Department VARCHAR(100),

@YearGroup VARCHAR(20),

@ApprovalId INT

AS

BEGIN

DECLARE @sql NVARCHAR(2000)

SET @sql='

SELECT OverseasExcursionId,TourTitle,Destination,DepartmentId,DepartmentName,

DepartureDate,ReturnDate

FROM OverseasExcursionDetails

WHERE 1=1 '

IF @DepartureDate IS NOT NULL

SET @sql =...

It's something that can be done and should not affect performance. The views should be expanded as the source tables and cause no harm on the performance side.

Have you tried...