store procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

store procedure

Author

Topic

s.chyna
Starting Member

2 Posts

Posted - 2011-05-27 : 08:15:04

Alter procedure sp_branch_details
AS
SET NOCOUNT ON
Declare @stg varchar, @frmtype varchar(10),@st datetime
declare @i int

Declare mycursor cursor for
select frm_code from frmformtype where status = 'D'
open mycursor
FETCH NEXT FROM mycursor
into @frmtype
WHILE @@fetch_status=0
Begin
set @i=1
declare aucursor cursor for
select stg_name from mstr_stages where frm_code like '%1,%' and status = 'A'
open aucursor
FETCH NEXT FROM aucursor
into @stg
set @st= 'select '+@stg+' from FRMBarcodetrack'
exec(@st)
print @st
WHILE @@fetch_status=0
Begin
print @stg
insert into ATS_Formstatus_branch
select @i,@stg,datediff(d,Dateofapp,@st ),@frmtype,count(*),month(Dateofapp),'STATUS',year(Dateofapp),getdate(),branchsol,null,'2011-2012'
from FRMBarcodetrack where datediff(m,@st ,getdate())=0 and
FRMBarcodetrack.frmtype = @frmtype
group by branchsol, datediff(d,Dateofapp,@st),month(Dateofapp),year(Dateofapp)
set @i=@i+1
FETCH NEXT FROM aucursor
into @stg
if @@fetch_status=0
PRINT 'Warning: No rows were updated';
else
PRINT @@rowcount;

end
close aucursor
deallocate aucursor
FETCH NEXT FROM mycursor
into @frmtype

end
close mycursor
Deallocate mycursor

set nocount off

--select* from FRMBarcodetrack
--Execute sp_branch_details
--sp_help frmformtype
--select frm_code from frmformtype where status = 'D'
--select * from ATS_Formstatus_branch where rpt_type='status'and Create_dt=getdate()
--sp_help FRMBarcodetrack
--select * from mstr_stages

here @stg value is inward i am using it as a column name which is of datatype datetime. how to do that.
in this i am getting error as

Server: Msg 241, Level 16, State 1, Procedure sp_branch_details, Line 20
Syntax error converting datetime from character string.

s.singh

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 08:43:59

You have statements like this in your code:


   SET @st = 'select ' + @stg + ' from FRMBarcodetrack'

Yet, @st is declare as a datetime variable parameter at the very beginning of the code. Trying to put something that is not a datetime into a datetime variable will result in this error.

If I understood your code correctly, You have two cursors, nested, in this code. This would be a very undesirable thing for performance reasons. It may very well be possible to rewrite the query without using cursors - I would seriously consider if that can be done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources