Conversion proble, in SP

[johnrg1]

Hi all,

I am trying to create a SP that will insert some data into a table, where the table name is provided as a varialbe.

ALTER PROCEDURE InsertStudentAttendance
(
@Date datetime,
@tableName varchar(15)
)
as
DECLARE @myStr varchar(500)
declare @CDATE varchar(25)

set @CDATE = CONVERT(VARCHAR(25), @Date , 102 )

set @myStr = 'insert into [dbo].['+@tableName+'] (Lecture_4) Values (' + @CDATE +')'

EXEC (@myStr)

With the Date set to 102, and many others, i get an error
"invaild SQL statement. Checkthe server filter on the form record course"
with it set to 103 i get a value of 20 (sent it to a char column to check value returned)

I can not understand where the problem is. Anyone see what i am missing?

Cheers

John

 

[swampBoogie]

The whole design seems wrong to me. Why do you have different tables with identical structure?

ALTER PROCEDURE InsertStudentAttendance
(
@Date datetime,
@tableName varchar(15)
)
as
DECLARE @myStr varchar(500)

set @myStr = 'insert into [dbo].['+@tableName+'] (Lecture_4) Values (''' + cast(@DATE as varchar) +''')'

EXEC (@myStr)

 

[smin]

execute sp_executesql @mySql

 

[sunila7]

Hi,

Change @mystr like this

set @myStr = 'insert into [dbo].['+@tableName+'] (Lecture_4) Values (''' + @CDATE +''')'

Does it help

Sunil

 

[johnrg1]

Thank you all for such a fast response

sunila,

Worked first time.

What a star.

John