Ref: Stored Procedures, Mixed Data Type In Sql Command

Su writes "I'm trying to use a stored procedure to dynamically update a table whenever other staff in other departments update their do any changes to their databaseds. and thanks for your web site taught me how to pass table names as parameters. But I still have problems withe sql command. You have an example in your article ('dynamic sql 2'), showing how to do a sql SELECTION using a table name and a local variable. But the sql command only use a local variable of varchar type. I'm trying to do INSERT with local variables with different data types. For example:

CREATE PROCEDURE KPISU_F_TotalByF
@inputT_From varchar(10),
@inputT_To varchar(10),
@TableName varchar(1000)
AS
-----------------------------------------------------
--------input variable-------------------------------
DECLARE @inputTerm_From varchar(10),
@inputTerm_To varchar(10),
@sql_empty varchar(2000),
@sql_refresh varchar(2000)
----------------------------------------------------
IF EXISTS (select * from tempdb.dbo.sysobjects
where id LIKE object_id('tempdb..#tmpOTLTotalByF'))
DROP TABLE #tmpOTLTotalByF

CREATE TABLE #tmpOTLTotalByF (Faculty varchar(50),Term_From varchar(10), Total_G12 int, Total_G3 int, Total_G4 int, Total_Faculty int)

DECLARE @iFaculty varchar(50),
@iTerm_From varchar(10),
@iTotal_G12 int,
@iTotal_G3 int,
@iTotal_G4 int,
@iTotal_Faculty int

SET @iTotal_Faculty = 0
SET @iTotal_G12 = 0
SET @iTotal_G3 = 0
SET @iTotal_G4 = 0

DECLARE su_OTL_F_cursor CURSOR

FOR
SELECT Faculty, Term_From, SUM(Grades_12), SUM(Grades_3), SUM(Grades_4)
FROM #tmpOTLTotalByFaculty
GROUP BY Faculty, Term_From

OPEN su_OTL_F_cursor

FETCH NEXT FROM su_OTL_F_cursor INTO @iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sql_refresh = 'INSERT '
SELECT @sql_refresh = @sql_refresh + @TableName
SELECT @sql_refresh = @sql_refresh + ' VALUES (' + @iFaculty + ', ' + @iTerm_From + ', ' + @iTotal_G12 + ', ' + @iTarget_12 + ', ' + @iTotal_G3 + ', ' + @iTarget_3 + ', ' + @iTotal_G4 + ', ' + @iTarget_4 + ', ' + @iTotal_Faculty + ')'

SET @iTotal_Faculty = 0

SET @iTotal_Faculty = @iTotal_G12 + @iTotal_G3 + @iTotal_G4

INSERT #tmpOTLTotalByF VALUES (@iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4, @iTotal_Faculty)

Exec ( @sql_refresh)

FETCH NEXT FROM su_OTL_F_cursor INTO @iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4
END

CLOSE su_OTL_F_cursor -----line 222
DEALLOCATE su_OTL_F_cursor

CLOSE su_OTL_T_cursor -----line 63
DEALLOCATE su_OTL_T_cursor

SELECT * FROM #tmpOTLTotalByF ORDER BY Faculty

SELECT * FROM KPISU_F_OTLTotalByF05 ORDER BY Faculty

GO

EXECUTE KPISU_F_TotalByF '2005', '2006', 'KPISU_F_OTLTotalByF05'
GO

----------------------------------------------------------

I got the following error message:

Server: Msg 245, Level 16, State 1, Procedure KPISU_F_TotalByF, Line 256
Syntax error converting the varchar value 'INSERT KPISU_F_OTLTotalByF06 VALUES (14-19 Academy, 2005, ' to a column of data type int.
-----------------------------------------------------------

I guess I could change all the columns in the table to data type of varchar. But are there any other way to solve this problem?

Many thanks.

Su"