Call Dynamic sql - 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)

Call Dynamic sql

Author

Topic

tanpl3
Starting Member

4 Posts

Posted - 2005-11-21 : 01:57:46

I calling Procedure "ProcGenReport" from asp by passing the table name and the neccessary parameter, but i get an error message "Procedure or function procgenreport has too many arguments specified "

how to passing the table name to @table?

this is my code
"
create procedure ProcGenReport
@Curcode char(3),@ExchRate char(25),@GLDesc char(100),@Glcode char(10),@debit char(25),@credit char(25),@dtmyr char(25),@crmyr char(25),
@refno char(16),@itiscode char(3),@trandesc char(100),@Createdby char(50),@tmpdate char(12),@batchno char(3)
as
DECLARE @InsertString NVARCHAR(1000)
DECLARE @table char(30)
DECLARE @trandate DATETIME
SELECT @trandate=CONVERT(datetime, @tmpdate)
SET @InsertString = 'set dateformat dmy insert into ' + @table +
'(Curcode,ExchRate,GLDesc,Glcode,debit,credit,dtmyr,crmyr,refno,itiscode,trandesc,Preparedby,trandate,batchno)
values (@Curcode,@ExchRate,@GLDesc,@Glcode,@debit,@credit,@dtmyr,@crmyr,@refno,@itiscode,
@trandesc,@Createdby,@trandate,@batchno)'

EXEC sp_executesql @InsertString
"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-21 : 02:40:21

you need to move your declaration of @table char(30) to the parameter section of your procedure

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 03:04:31

>>'set dateformat dmy insert into ' + @table +

What are you trying to do?
Why do you want to change the Server dateFormat?

Madhivanan

Failing to plan is Planning to fail

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-21 : 04:20:52

May be the line should be like this

SET @InsertString = 'set dateformat dmy; insert into ' + @table +

Surendra

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 04:24:55

SET @InsertString = 'set dateformat dmy; insert into ' + @table +
'(Curcode,ExchRate,GLDesc,Glcode,debit,credit,dtmyr,crmyr,refno,itiscode,trandesc,Preparedby,trandate,batchno)
values (''' + @Curcode + ''',''' + @ExchRate + ''',''' + @GLDesc + ''',''' + @Glcode + ''',''' + @debit + ''',''' + @credit
+ ''',''' + @dtmyr + ''',''' + @crmyr + ''',''' + @refno + ''',''' + @itiscode + ''',''' + @trandesc + ''',''' + @Createdby
+ ''',''' + @trandate + ''',''' + @batchno''' + ''')'

i must say that this is ridicolous... create an insert sproc for each table.

Go with the flow & have fun! Else fight the flow

tanpl3
Starting Member

4 Posts

Posted - 2005-11-24 : 02:40:33

i must format the date as ddmmyyyy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 02:55:50

Dont change the Date Format of server. When sending the date from Client, send it in Universal format yyyymmdd so that you need not worry on how SQL Server stores Date

Madhivanan

Failing to plan is Planning to fail

tanpl3
Starting Member

4 Posts

Posted - 2005-11-24 : 03:08:55

ok, this is the amendment i did. and i remove the
"execute sp_executesql @InsertString", no error message BUT it does not Insert any data to my table

create procedure ProcGenReport
@table char(30),@Curcode char(3),@ExchRate char(25),@GLDesc char(100),@Glcode char(10),@debit char(25),@credit char(25),@dtmyr char(25),@crmyr char(25),
@refno char(16),@itiscode char(3),@trandesc char(100),@Createdby char(50),@tmpdate char(12),@batchno char(3)
as
DECLARE @InsertString NVARCHAR(1000)
DECLARE @trandate DATETIME
SELECT @trandate=CONVERT(datetime, @tmpdate)
SET @InsertString = 'insert into ' + @table +
'(Curcode,ExchRate,GLDesc,Glcode,debit,credit,dtmyr,crmyr,refno,itiscode,trandesc,Preparedby,trandate,batchno)
values (@Curcode,@ExchRate,@GLDesc,@Glcode,@debit,@credit,@dtmyr,@crmyr,@refno,@itiscode,
@trandesc,@Createdby,@trandate,@batchno)'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-24 : 04:05:49

you will need the sp_executesql

something like this


exec sp_executesql @InsertString, N'@table char(30), @Curcode char(3), @ExchRate char(25) . . .',
    @table, @Curcode, @ExchRate . . .

-----------------
[KH]

tanpl3
Starting Member

4 Posts

Posted - 2005-11-25 : 03:05:14

i adding the following
"exec sp_executesql @InsertString,
N'@Curcode char(3),@ExchRate char(25),@GLDesc char(100),@Glcode char(10),@debit char(25),@credit char(25),@dtmyr char(25),@crmyr char(25),
@refno char(16),@itiscode char(3),@trandesc char(100),@Createdby char(50),@tmpdate char(12),@batchno char(3)',
@Curcode,@ExchRate,@GLDesc,@Glcode,@debit,@credit,@dtmyr,@crmyr,@refno,@itiscode,
@trandesc,@Createdby,@trandate,@batchno "

after i add, another error "Must declare @trandate variable"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-25 : 03:25:03

quote:
Originally posted by tanpl3

i adding the following
"exec sp_executesql @InsertString,
N'@Curcode char(3),@ExchRate char(25),@GLDesc char(100),@Glcode char(10),@debit char(25),@credit char(25),@dtmyr char(25),@crmyr char(25),
@refno char(16),@itiscode char(3),@trandesc char(100),@Createdby char(50),@tmpdate char(12),@batchno char(3)',
@Curcode,@ExchRate,@GLDesc,@Glcode,@debit,@credit,@dtmyr,@crmyr,@refno,@itiscode,
@trandesc,@Createdby,@trandate,@batchno "

after i add, another error "Must declare @trandate variable"

-----------------
[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources