quote:

---

Originally posted by tduggan

We need to see the code in order to tell you why it is putting ALTER instead of CREATE.

Tara

---

CREATE PROCEDURE [dbo].[pr_CreateSP]
	@Guid varchar(50),
	@CreateFields varchar(1000),
	@InsertFields varchar(1000),
	@Select varchar(500)
AS

DECLARE @SQL varchar(4000), @NEWLINE varchar(1)
SET @NEWLINE = Char(10)

SET @SQL = '
CREATE PROCEDURE dbo.pr_DY' + @guid + '
	@Page INT = 1, 
	@RecsPerPage INT = 25,
	@SortExpression VARCHAR(500) = ''1'',
	@Query varchar(500) = NULL
AS
	CREATE TABLE #t' + @guid + '
	(
	[ID] [int] IDENTITY (1, 1) NOT NULL , ' +
	@CreateFields + '
	)
	 ON [PRIMARY]'

SET @SQL = @SQL +
'DECLARE @sql AS varchar(2000)
SET @sql = 
''INSERT INTO #t' + @guid + '
(' + @InsertFields + ') ''' + @Select

SET @SQL = @SQL + @NEWLINE + 
' IF NOT RTrim(@Query) IS NULL AND LEN(RTrim(@Query)) <> 0 
BEGIN
SET @SQL = @SQL + '' WHERE '' + RTrim(@Query)
END'

SET @SQL = @SQL+ '
IF NOT RTrim(@SortExpression) IS NULL AND LEN(RTrim(@Query)) <> 0 
BEGIN 
SET @SQL = @SQL + 
'' Order By '' + RTrim(@SortExpression)
END'

SET @SQL = @SQL +
' EXEC(@SQL)
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
'

SET @SQL = @SQL +
'-- get total records
Declare @TotalCount as int
select @TotalCount = count(ID) from #t' + @guid


SET @SQL = @SQL +
'-- if the recs per page is 0 then the user wants to see all records. 
-- usually this is required for printing
if @RecsPerPage = 0 
BEGIN
SELECT @LastRec = @TotalCount + 1
-- we need to set the recs per page to 25 otherwise we will get an division by 0 error.
SET @RecsPerPage = 25
End '

SET @SQL = @SQL +
'-- number of total pages
DECLARE @TotalPages as int
select @TotalPages = case when @TotalCount % @RecsPerPage = 0 then @TotalCount/@RecsPerPage 
else @TotalCount/@RecsPerPage + 1
End '


SET @SQL = @SQL +
'-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT 
*,
@TotalPages as TotalPages,
MoreRecords = (
Select COUNT(ID)
FROM #t4' + @guid + ' TI
WHERE TI.ID >= @LastRec
) 
FROM #t' + @guid + '
WHERE ID > @FirstRec AND ID < @LastRec
Drop Table #t' + @guid + '
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
'

Print(@SQL)
EXEC(@SQL)
GO

quote:

---

Originally posted by TG

what tool are you using to "open up" and "save" the SPs?

when you comment out the "exec (@sql)" and just let the "print @sql" run, can you paste the output into a new query analzyer window and run it successfully?

Be One with the Optimizer
TG

---

pr_CreateSP
	 @Guid = '46174213209044c4a9146531ec8d76ecb'
	,@CreateFields = 'col1 int, col2 int'
	,@InsertFields = 'col1, col2'
	,@Select = 'select col1, col2 from myTable where 1=2'

pr_CreateSP
	 @Guid = '46174213209044c4a9146531ec8d76ecb'
	,@CreateFields = 'col1 int, col2 int'
	,@InsertFields = 'col1, col2'
	,@Select = 'select col1, col2 from myTable where 1=2'