Criar SP no SQLExpres

[[C4]]

Boas,
ja tou a dar em maluco com isto.
Estou tentar criar uma SP no sql express mas ta a dar um erro estupido.

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURE dbo.get_product 
    @id int,
    @sortby varchar(10),
    @page int= 1,
    @items_per_page int= 20
AS
    if(@id is not null)
       select*into #tmp from product where active = 1
    else
       select*into #tmp from product where id = @id and active = 1
 
    if(@sortby ='ID'or @sortby is null)
      select * into #tmp2 from #tmp order by id
    if(@sortby ='NAME')
      select*into #tmp2 from #tmp order by [name]
    if(@sortby ='REFERENCE')
      select*into #tmp2 from #tmp order by reference
    if(@sortby ='PRICE')
      select*into #tmp2 from #tmp order by price
 
    select top (@page * @items_per_page) * into #tmp3 from #tmp2
    delete top ((@page - 1) * @items_per_page) from #tmp3
 
    select*from #tmp3
go

e o erro que da:
Msg 2714, Level 16, State 1, Procedure get_product, Line 11
There is already an object named '#tmp' in the database.
Msg 2714, Level 16, State 1, Procedure get_product, Line 16
There is already an object named '#tmp2' in the database.
Msg 2714, Level 16, State 1, Procedure get_product, Line 18
There is already an object named '#tmp2' in the database.
Msg 2714, Level 16, State 1, Procedure get_product, Line 20
There is already an object named '#tmp2' in the database.

Nao percebo pq ta a dar erro se estou a tentar criar a sp e nao a executa-la

Cps

 

[alfinete]

esse #tmp etc é o nome de um campo da tabela?

penso que é disso

mas não tenho a certeza

 

[[C4]]

#tmp é uma tabela temporaria criada em memoria apenas para a transaccao em que é criada.

 

[geoblast]

Boas,
ja tou a dar em maluco com isto.
Estou tentar criar uma SP no sql express mas ta a dar um erro estupido.

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURE dbo.get_product 
    @id int,
    @sortby varchar(10),
    @page int= 1,
    @items_per_page int= 20
AS
    if(@id is not null)
       select*into #tmp from product where active = 1
    else
       select*into #tmp from product where id = @id and active = 1
 
    if(@sortby ='ID'or @sortby is null)
      select * into #tmp2 from #tmp order by id
    if(@sortby ='NAME')
      select*into #tmp2 from #tmp order by [name]
    if(@sortby ='REFERENCE')
      select*into #tmp2 from #tmp order by reference
    if(@sortby ='PRICE')
      select*into #tmp2 from #tmp order by price
 
    select top (@page * @items_per_page) * into #tmp3 from #tmp2
    delete top ((@page - 1) * @items_per_page) from #tmp3
 
    select*from #tmp3
go

e o erro que da:
Msg 2714, Level 16, State 1, Procedure get_product, Line 11
There is already an object named '#tmp' in the database.
Msg 2714, Level 16, State 1, Procedure get_product, Line 16
There is already an object named '#tmp2' in the database.
Msg 2714, Level 16, State 1, Procedure get_product, Line 18
There is already an object named '#tmp2' in the database.
Msg 2714, Level 16, State 1, Procedure get_product, Line 20
There is already an object named '#tmp2' in the database.

Nao percebo pq ta a dar erro se estou a tentar criar a sp e nao a executa-la

Cps

Não te faltam os begin e os end nas instruções??

 

[JGAlmeida]

Isso deve ser devido a este problema: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714

Mas tens aí outras questões, "select top" com uma variavel não funciona. E essas tabelas temporarias todas podem ser evitadas.

Tenta isto, não testei mas deve andar perto do pretendido:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE get_product 
    @id 		int,
    @sortby 		varchar(10),
    @page 		int = 1,
    @items_per_page 	int = 20
AS

DECLARE @inicio int, @fim int

--page = 1 : inicio=1 fim=20
--page = 2 : inicio=21 fim=40
SET @fim = @page * @items_per_page
SET @inicio = (@page - 1) * @items_per_page + 1

--IDENTITY(int, 1,1) rownum vai numerar os registos de 1 a n, respeitando o order escolhido

    SELECT 	IDENTITY(int, 1,1) rownum, *
    INTO 	#tmp
    FROM 	product 

--se o @id tiver valor filtra por Active=1 e id=@id, se for null filtra apenas por Active = 1
    WHERE 	Active = 1 
    AND 	([id] = @id OR @id IS NULL)

    ORDER BY
        CASE WHEN @sortby = 'ID' 
	    THEN [id] END,
        CASE WHEN @sortby = 'NAME' 
	    THEN [name] END,
        CASE WHEN @sortby = 'REFERENCE' 
	    THEN [reference] END,
        CASE WHEN @sortby = 'PRICE' 
	    THEN [Price] END,
        CASE WHEN @sortby = NULL
	    THEN [id] END

    
    SELECT * FROM #tmp WHERE rownum BETWEEN @inicio AND @fim

GO

Cumps.

 

[[C4]]

Boas
obrigado pelas dicas.
Ja consegui resolver o problema.

o "select top" com variavel funciona e o delete top tb.
optei por criar as tabelas do tipo variavel, e em vez de select * into usei insert into e manipulei os dados.

JGAlmeida: gostei do teu algoritmo.

 

[JGAlmeida]

o "select top" com variavel funciona e o delete top tb.

Mas assim como estavas a usar, ou meteste sql dinamico?

 

[[C4]]

no select top fiz como estava a usar
insert into @tmp3 select top (@page * @items_per_page) * from @tmp2
funcionou.


nao tenho aqui o codigo todo agora. assim que puder meto aqui