CREATE PROCEDURE
[dbo].[ShowPrototypes]
-- Add the parameters for the stored procedure here
@idPrescriptor
varchar(10)='',
@state
varchar(200)=''
AS
BEGIN
declare
@stringtoexecute nvarchar(1000)--,@stringinterim nvarchar(1000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[temp3]') and OBJECTPROPERTY(id, N'Istable') = 1)
- drop table
[dbo].[temp3]
set
@stringtoexecute=N'select MODEL_COUNT,PROTOTYPE_MODEL_ID,c.model_id,A.PROTOTYPE_ID,PROTO_ETAT,PROTO_UTILCRT,PROTO_LIBELLE,MODEL_LIBELLE,MODEL_LIBPOSOLOGIE
into temp3 FROM PROTOTYPE A
left outer join
PROTOTYPE_MODEL B
on A.PROTOTYPE_ID=B.PROTOTYPE_ID
left outer join
MODEL C
ON
B.MODEL_ID=C.MODEL_ID'
if
(@idprescriptor!='')
begin
undefined@StringToExecute=@StringToExecute+' where (PROTO_UTILCRT='
end
if
(@state!='')
begin
if charindex('where',@stringtoexecute)>0
set @StringToExecute=@StringToExecute+' and (PROTO_ETAT in '+@state+')'
else
set
@StringToExecute=@StringToExecute+' where (PROTO_ETAT in '+@state+')'
end
exeC
sp_executesql @stringtoexecute
select
prototype_model_id_1 , prototype_model_id_1 as grup,model_type_lien into #temp1 from model_lie group by prototype_model_id_1,model_type_lien
select
* into #tempfin from #temp1
union
select
prototype_model_id_2 ,grup,A.model_type_lieN from #temp1 a,model_lie b where a.prototype_model_id_1=b.prototype_model_id_1
--left outer join #temp3 #tempfin
select
* from temp3 as a
left outer join
#tempfin as b
on
a.PROTOTYPE_model_id=b.prototype_model_id_1
drop table
temp3
END
Nedumerirrea mea se refera la temp3 in @StringToExecute am pus intial into #temp3 sa-mi puna in temporara.Nu o recunpaste si a trebuit sa pun o tabela normala si sa gestionez eu stergerea ei e vorba de temp3.
Aveti cumva idee de ce nu am putut folosi in sql dynamic @string to execute tabela temporara la into ci normala?
Multumesc
Secolul XXI ori va fi religios ori nu va fi deloc