Procedure insert only single value out of 75 entries

Question

0.00/5 (No votes)

See more:

hello friends....
I have developed procedure sp_insertbatch_1_month but it insert only single value out of 75 values.

SQL

USE [PIMS_DB]
GO
/****** Object:  StoredProcedure [dbo].[sp_insertbatch_1_month]    Script Date: 02/16/2015 17:57:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_insertbatch_1_month]
@year varchar(4),
@month varchar(2)
as
begin
	declare @StartDt DATETIME
	declare @EndDt  DATETIME
	set @StartDt=@year+'/'+@month+'/'+'01 00:00:00'
	set @EndDt=dateadd(second,59,dateadd(minute,59,dateadd(hour,23,dateadd(day,-1,dateadd(month,1,@StartDt)))))
	
	if (convert(varchar,@EndDt,101)>=convert(varchar,getdate(),101) and month(@EndDt)>=month(getdate()) and year(@EndDt)>=year(getdate()))
	begin
		set @EndDt=(select getdate())
	end
	declare @starttime varchar(20)
	declare @endtime varchar(20)
	set @starttime=cast((month(@StartDt)) as varchar)+'/'+cast((day(@StartDt)) as varchar)+'/'+cast((year(@StartDt)) as varchar)+' '+cast((datepart(hour,@StartDt)) as varchar)+':'+cast((datepart(minute,@StartDt)) as varchar)+':'+cast((datepart(second,@StartDt)) as varchar)
	set @endtime=cast((month(@EndDt)) as varchar)+'/'+cast((day(@EndDt)) as varchar)+'/'+cast((year(@EndDt)) as varchar)+' '+cast((datepart(hour,@EndDt)) as varchar)+':'+cast((datepart(minute,@EndDt)) as varchar)+':'+cast((datepart(second,@EndDt)) as varchar)
	declare @var1 varchar(5000)
	set @var1='select * from openquery(pims_hist,''set samplingmode=interpolated,rowcount=0 select distinct value from ihrawdata where tagname=PIMS-LOTE-1.LOTE.PIMS.Global.BATCH_1.DATA and timestamp>="'+@starttime+'" and timestamp<="'+@endtime+'" and value like LT*'')'
	
	create table #temp
	(
		id int identity(1,1) not null,
		batch varchar(20)
	)
	
	insert into #temp exec(@var1)
	
	declare @count int
	declare @next int
	
	select @count=COUNT(batch) from #temp
	declare @batch varchar(20)
	set @next=@count-1
	if (@count>0)
	begin
	
		while(@next>=0)
		begin
			select top 1 @batch=batch from (select top (@count-@next) *  from #temp order by id desc) as f order by f.id asc
			insert into Batch_1_Data select @batch
			set @next=@next-1
		end
	end
end

it is supposed to insert all 75 values returned by following statement

SQL

insert into #temp exec(@var1)

Please help me

Posted 16-Feb-15 21:30pm

TAUSEEF KALDANE

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

anand vemuri · Answer 1 · 2015-02-16T22:41:00

SQL

IF (ISNULL(@VAR1, 0) <> 0)
BEGIN
--TEMP TABLE
DECLARE #TEMP TABLE
(
	ID INT IDENTITY(1,1) NOT NULL,
	BATCH VARCHAR(20)
) 
--INSERT #TEMP STATEMENTS	
INSERT INTO #TEMP EXEC(@VAR1) 
--VARIABLE DECLARATION
DECLARE @COUNT INT
 
--SELECT COUNT
SELECT @COUNT=COUNT(BATCH) FROM #TEMP
DECLARE @BATCH VARCHAR(20)

--INSERT MULTIPLE RECORDS
IF(@COUNT > 0)
	BEGIN
		SET @EMPLOOPCOUNT = 1;
		WHILE (@TEMPLOOPCOUNT <= @COUNT)
		BEGIN 
			--SELECT YOUR VALES 
			SELECT @BATCH = BATCH FROM #TEMP WHERE ID = @TEMPLOOPCOUNT
			-- INSERTING VALUES INTO EMPLOYEEPROJECTS TABLE
			INSERT INTO BATCH_1_DATA
			(	
				--COLUMNS
				-- EG:ID
				BATCH
			)
			VALUES 
			(
			   --SELECTED VALUES FROM #TEMP
			   -- EG:@ID
			   @BATCH
			)
			SET  @TEMPLOOPCOUNT = @TEMPLOOPCOUNT + 1
	    END
	END
END

Try this code in place of

SQL

create table #temp
	(
		id int identity(1,1) not null,
		batch varchar(20)
	)
	
	insert into #temp exec(@var1)
	
	declare @count int
	declare @next int
	
	select @count=COUNT(batch) from #temp
	declare @batch varchar(20)
	set @next=@count-1
	if (@count>0)
	begin
	
		while(@next>=0)
		begin
			select top 1 @batch=batch from (select top (@count-@next) *  from #temp order by id desc) as f order by f.id asc
			insert into Batch_1_Data select @batch
			set @next=@next-1
		end