I am trying to insert a record into 3

[johnrg1]

I am trying to insert a record into a table that was created on the fly. The table is created with errors, and when i check it, all the columns are of the correct data types. I am using the code

ALTER PROCEDURE InsertStudenttoClass
(
@tableName varchar(10),
@StudentID int
)
AS
DECLARE @myStr varchar(1500)

SELECT @mySTR= 'INSERT INTO [dbo].['+@tablename+'] (StudentID) VALUES (@StudentID)'

EXEC (@myStr)


The error messages is telling me to declare the @student variable.

Any ideas?

 

[MeanGreen]

Your problem is your @studentID. You need the actual value passed in for your insert. Try this:

ALTER PROCEDURE InsertStudenttoClass
(
@tableName varchar(10),
@StudentID int
)

AS
DECLARE @myStr varchar(1500)

select @tableName = 'test'
select @StudentID = 1
SELECT @mySTR= 'INSERT INTO [dbo].['+@tablename+'] (StudentID) VALUES (' + cast(@StudentID as varchar(10)) + ')'

exec (@myStr)

Hope this helps.

 

[sunila7]

Hi,

Change ur Select @myStr Line like this

SELECT @mySTR= 'INSERT INTO [dbo].['+@tablename+'] (StudentID) VALUES (' + @StudentID + ')'

EXEC (@myStr)


Sunil

 

[tlbroadbent]

When you execute the query dynamically, the scope changes and the variable is not available in the execute scope. The value of the parameter must be evaluated before executing the SQL statement. Modify the SELECT statemetn as follows.

SELECT @mySTR= 'INSERT INTO [dbo].['+@tablename+'] (StudentID) VALUES (' + str(@StudentID) + ')'

EXEC (@myStr) If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[johnrg1]

I do not care what anyone says, you guys ARE the best!

Thank you very much, it all works perfectly now.

John