Very strange the code now looks like this as you replied first time

(SELECT Id FROM

OPENXML(@idoc, '/Ids/Id', 2)

WITH( Id int '.')) AS s

ON f.FileId = s.Id

I made some changes but undo...

Now I don't get any result...

Thanks for answering

Yes the isolation level is default.

Great.

Thanks Gila

IF exists (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[spBackupAllDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].spBackupAllDatabases

GO

CREATE PROCEDURE spBackupAllDatabases

@Path varchar(128) ,

@Path1 varchar(128)

AS

BEGIN TRY

IF OBJECT_ID ('DatabaseBackup') IS NOT NULL

DROP TABLE...

Thanks a lot for the reply.

Here is what I've end up with

DECLARE @sql nvarchar(max)

if object_id('tempdb..#Result','U') IS not NULL

DROP TABLE #Result

CREATE TABLE #Result (DBName SYSNAME, UserName SYSNAME, AssociatedDBRole...

I think a solution without hard coding or using a 'select count(*)' statement or a 'if clause' doesn't exist??

I mean if @top parameter is passed return the @top rows if...

Is there another way to do this without using the Select count statement because for a large table and a large amount of data that is retrieve, it will run...

Thank you both. With your answers combined I got the desire result.

First in the parm declaration for the sp I initialize the @top with 0 and then in the sp...

Well I need the @top parameter so when I call the procedure if the @top is not supplied to return all the rows, else return the @top rows so I...