Error in Dynamic SQL - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Error in Dynamic SQL

Author

Topic

great_sandeep2001
Starting Member

2 Posts

Posted - 2005-05-18 : 01:01:34

DECLARE @desc1 char(4),@desc2 char(4),@desc3 char(4),@mon1 int,@mon2 int,@mon3 int
DECLARE @count_loop int
DECLARE @SQL as varchar(1000)
DECLARE temp_cur1 CURSOR
FOR
SELECT desc1,desc2,desc3,mon1,mon2,mon3 FROM original
OPEN temp_cur1
FETCH NEXT FROM temp_cur1 INTO @desc1,@desc2,@desc3,@mon1 ,@mon2 ,@mon3
WHILE (@@fetch_status =0)
BEGIN
SET @count_loop=1
WHILE @count_loop<=3
BEGIN
Select @SQL='UPDATE new_tab SET all_month ='''+cast('@mon' + cast(@count_loop as varchar(5)) as varchar(10))
+ ''' WHERE desc1=''' + @desc1 + ''' and desc2='''+ @desc2 + ''' and desc3=''' +
@desc3 +''' and m_code=' + cast(@count_loop as char(2))
print @sql
exec(@SQL )
Set @count_loop=@count_loop+1
END
FETCH NEXT FROM temp_cur1 INTO @desc1,@desc2,@desc3 ,@mon1 ,@mon2 ,@mon3
END
CLOSE temp_cur1
DEALLOCATE temp_cur1

while executing this block i am getting error : Syntax error converting the varchar value '@mon1' to a column of data type int.
I am printing the string using Print @SQL and the output is :UPDATE new_tab SET all_month ='@mon1' WHERE desc1='X1 ' and desc2='X2 ' and desc3='X3 ' and m_code=1
what i want is instead of @mon1 its value should come in the update statement.Please help me out

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-18 : 01:21:24

[code]
CREATE TABLE original(desc1 varchar(55),desc2 varchar(55),desc3 varchar(55),mon1 INT, mon2 INT, mon3 INT)
GO
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
GO

DECLARE @desc1 char(4),@desc2 char(4),@desc3 char(4),@mon1 int,@mon2 int,@mon3 int
DECLARE @count_loop int
DECLARE @SQL as varchar(1000)
DECLARE temp_cur1 CURSOR
FOR
SELECT desc1,desc2,desc3,mon1,mon2,mon3 FROM original
OPEN temp_cur1
FETCH NEXT FROM temp_cur1 INTO @desc1,@desc2,@desc3,@mon1 ,@mon2 ,@mon3
WHILE (@@fetch_status =0)
BEGIN
SET @count_loop=1
WHILE @count_loop<=3
BEGIN
Select @SQL='UPDATE new_tab SET all_month =cast(' + cast(@mon1 as varchar(10)) + ' as varchar(10)) + cast(' + cast(@count_loop as varchar(10)) + ' as varchar(5))
WHERE desc1=''' + @desc1 + ''' and desc2='''+ @desc2 + ''' and desc3=''' +
@desc3 +''' and m_code=' + cast(@count_loop as char(2))
print @sql
--exec(@SQL )
Set @count_loop=@count_loop+1
END
FETCH NEXT FROM temp_cur1 INTO @desc1,@desc2,@desc3 ,@mon1 ,@mon2 ,@mon3
END
CLOSE temp_cur1
DEALLOCATE temp_cur1
GO

DROP TABLE original
GO

[/code]

Well, that one at least compiles. WHYYYYYYYYY are you doing this? Are you trying to combine every worst practice in the history of SQL Server all into one big statement? This is a mess, so please tell me this is some experiment not for a production system.

cursors, EXEC() instead of sp_executesql, dynamic SQL, badly defined columns variables and objects, no standard layout or format, code that won't compile. Yep, this is about the worst piece of crap I've seen in quite some time.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-18 : 01:54:04

quote:
Originally posted by derrickleggett

Yep, this is about the worst piece of crap I've seen in quite some time.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Derrick
Your too kind

There obviously isnt another way of doing this is there

The next quetion will be my sp performs like a dog!!

Beauty is in the eyes of the beerholder

great_sandeep2001
Starting Member

2 Posts

Posted - 2005-05-18 : 05:58:21

CREATE TABLE original(desc1 varchar(55),desc2 varchar(55),desc3 varchar(55),mon1 INT, mon2 INT, mon3 INT)
GO
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
INSERT original(desc1,desc2,desc3,mon1,mon2,mon3) VALUES(12,123,1344,124,1414,1414)
GO

DECLARE @desc1 char(4),@desc2 char(4),@desc3 char(4),@mon1 int,@mon2 int,@mon3 int
DECLARE @count_loop int
DECLARE @SQL as varchar(1000)
DECLARE temp_cur1 CURSOR
FOR
SELECT desc1,desc2,desc3,mon1,mon2,mon3 FROM original
OPEN temp_cur1
FETCH NEXT FROM temp_cur1 INTO @desc1,@desc2,@desc3,@mon1 ,@mon2 ,@mon3
WHILE (@@fetch_status =0)
BEGIN
SET @count_loop=1
WHILE @count_loop<=3
BEGIN
Select @SQL='UPDATE new_tab SET all_month =cast(' + cast(@mon1 as varchar(10)) + ' as varchar(10)) + cast(' + cast(@count_loop as varchar(10)) + ' as varchar(5))WHERE desc1=''' + @desc1 + ''' and desc2='''+ @desc2 + ''' and desc3=''' +
@desc3 +''' and m_code=' + cast(@count_loop as char(2))
print @sql
--exec(@SQL )
Set @count_loop=@count_loop+1
END
FETCH NEXT FROM temp_cur1 INTO @desc1,@desc2,@desc3 ,@mon1 ,@mon2 ,@mon3
END
CLOSE temp_cur1
DEALLOCATE temp_cur1
GO

DROP TABLE original
GO

Ok let me tell you ..what I realy want to do.i have two 'tables'original and 'new_tab'.the structure of original is same as you have created.new_tab contains desc1,desc2,desc3,all_month(int) and m_code (int).m_code contains the code for months such as 1,2,...12. desc1,desc2,desc3 forms an unique key thats why i have used all the three along with m_code in the where clause.

table:original
desc1 desc2 desc3 mon1 mon2 mon3
X1 X2 X3 1 2 3
Y1 Y2 Y3 4 5 6
Z1 Z2 Z3 7 8 9

table:new_tab
desc1 desc2 desc3all_month m_code
X1 X2 X3 1 1
Y1 Y2 Y3 4 1
Z1 Z2 Z3 7 1
X1 X2 X3 2 2
Y1 Y2 Y3 5 2
Z1 Z2 Z3 8 2
X1 X2 X3 3 3
Y1 Y2 Y3 6 3
Z1 Z2 Z3 9 3

all_month column will be empty.the sql will update all_month column.

now i think you will understand.please help me out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources