Ref: Stored procedures, mixed data type in sql command

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

General SQL Server Forums

New to SQL Server Programming

Ref: Stored procedures, mixed data type in sql command

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-20 : 09:10:52

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"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-20 : 09:21:30

Please provide structure of the 'KPISU_F_OTLTotalByF05' table.

Your SP seems incomplete - you only have a close for cursor su_OTL_T_cursor - I don't see an open of declare.

It seems to me you are trying to insert @sql_refresh into an integer, based on the error meesage.

Suggest you try and debug the SP using query analyzer object browser. Also, consider putting in a few "display" lines to check status of variables (this is done via debug, but might be useful if you have an issue).

HTH

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources