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

You're welcome.

Another trick could be using a loopback linked server or coding some kind of CLR function. Once again, not so easy, not so readable.