question

FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 asked FarhanJamil-5363 commented

stuck in a sql query

Hi All

Not sure where I am going wrong. Any assistance will be off great help

I am trying to run a stored procedure against a cursor which will insert a one year record into a table

This is my SQL query:-


declare @TxnDate Date
declare cur Cursor local for
select cast(Date_fld as date) from dbo.Calendar where cast(date_fld as date) between '2020-01-26' and '2021-01-30'
open cur
fetch next from cur into @TxnDate


 While @@FETCH_STATUS = 0
 Begin
        
     insert into [dbo].ONLINEFY2020 
     Exec dbo.[ONLINE_IMPORT] @TxnDate
        
 Fetch next from cur into @TxnDate
 End

close cur
deallocate cur

Here is my table definition

83615-image.png


and here is the error i am getting

83560-image.png




Any assistance is appreciated

Thanks
Farhan Jamil

sql-server-transact-sql
image.png (13.7 KiB)
image.png (1.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe ONLINE_IMPORT procedure expects a number, not a date? Show some details about this procedure or its parameters.

0 Votes 0 ·

HI

This sp has two parameters @Store int = null, @date date = null
as you can see both accepts null value. I did change the data type of StoreId to int in my table definition thinking that could be the problem but still I get the same error.


Regards
Farhan Jamil

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered FarhanJamil-5363 commented

Try this too:

 insert into [dbo].ONLINEFY2020 
 exec dbo.[ONLINE_IMPORT] @date = @TxnDate

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks mate. this was the missing link

Farhan Jamil

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered FarhanJamil-5363 commented

select cast(Date_fld as date)

The column name "Date_fld" don't match with the table definition/don't exist.
So it's from a different table and by the error message it's type "integer", right?



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi

this is my sql query

declare @TxnDate Date
declare cur Cursor local for
select cast(Date_fld as date) from dbo.Calendar where cast(date_fld as date) between '2020-01-26' and '2021-01-30'
open cur
fetch next from cur into @TxnDate


While @@FETCH_STATUS = 0
Begin

  insert into [dbo].ONLINEFY2020 
  Exec dbo.[ONLINE_IMPORT] @TxnDate

Fetch next from cur into @TxnDate
End
close cur
deallocate cur

the column date_fld is a column name from a view (dbo.calendar)which is only fetching dates and all dates are then assigned to @txndate as you can see in the cursor.

Regards
Farhan Jamil

0 Votes 0 ·