dynamic sql

Question

dynamic sql

Cherey

SSC Enthusiast

Points: 119
More actions
November 24, 2005 at 1:46 am

#88575

i calling ProcGenReport from asp, passing all the variable,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)'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

First off, why is this Dynamic SQL? You seem to know which table you are using as you are specifying each column independently. I would suggest you alter this to run as a straight insert.

As to the errors, first, datatime for formats cannot be used directly in Dynamic SQL. They must be converted to varchar fields. Secondly, there is no EXCEUTE( @InsertString). I would suggest you try PRINT @InsertString from Query Analyzer and see the results. If you can run the insert from that output, your Dynamic SQL should work.

I wasn't born stupid - I had to study.

David Burrows SSC Guru Points: 65143 More actions · Answer 2

Because the table name is not known, only the structure and therefore assumes that the structure is the same for any table name passed to the proc.

Try executing the sql by adding the following to the end

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

Far away is close at hand in the images of elsewhere.
Anon.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

That was my point. In other words, all the tables contain the same columns, just different names?

I wasn't born stupid - I had to study.

Cherey SSC Enthusiast Points: 119 More actions · Answer 4

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"

David Burrows SSC Guru Points: 65143 More actions · Answer 5

sorry my fault, did not check parameter names properly, try this instead

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),@trandate char(12),@batchno char(3)',

@Curcode,@ExchRate,@GLDesc,@Glcode,@debit,@credit,@dtmyr,@crmyr,@refno,@itiscode,@trandesc,@Createdby,@trandate,@batchno

Far away is close at hand in the images of elsewhere.
Anon.