August 17, 2007 at 4:06 am
Hi,
I have used the following script and created a procedure..
/********************************/
create procedure temp_proc1
as
begin
declare @sp_id varchar(10)
set @sp_id = cast(@@spid as varchar)
IF EXISTS(select * from sysobjects where name like 'temptable1%')
begin
EXEC ('DROP TABLE temptable1'+ @sp_id)
end
EXEC('Create Table temptable1'+ @sp_id +' (ID int NOT NULL, FieldName VarChar(10))')
EXEC('Insert into temptable'+ @sp_id + 'values (10,''XXXXX'')')
EXEC ('Delete from temptable1'+ @sp_id)
EXEC ('Drop table temptable1'+ @sp_id)
end
/*************************************/
It got created successfully. But, when I am trying to execute the procedure, I am getting the following error..
/**********/
EXEC temp_proc1
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
/*******************/
Problem is with the follwoing line in the procdure...
EXEC('Insert into temptable'+ @sp_id + 'values (10,''XXXXX'')')
Could you please clarify me where I went wrong..
Thanks in Advance.
Ezhilan
August 17, 2007 at 4:24 am
Hi,
It doesn't like concatenating strings to execute a command. You're better off putting the whole line into a string:
DECLARE
@spID NVARCHAR (10),
@CommandString NVARCHAR(255)
SET @spID = '1'
SET @CommandString = 'DROP TABLE TEMPTABLE' + @spID
sp_executessql @CommandString
August 17, 2007 at 4:35 am
Hi,
It's a minor mistake. you might overlooked at it.
change the code to
EXEC('Insert into temptable1'+ @sp_id + 'values (10,''XXXXX'')')
It works. by mistake, you have removed 1 in the insert statement.
check it out.
--Prasad
August 17, 2007 at 4:56 am
Hi,
I have modified it...
Still I am getting that error in the line...
/********/
EXEC('Insert into temptable1'+ @sp_id + 'values (10,''XXXXX'')')
/**********/
I think problem should be somewhere here - 'values (10,''XXXXX'')')
Could you please check whether it is concatenating properly..
Thanks in Advance.
Ezhilan
August 17, 2007 at 5:00 am
It works. I just checked. just copy from here and paste in your code.
EXEC('Insert into temptable1'+ @sp_id + ' values (10,''XXXXX'')')
Try and let me know.
--Prasad
August 17, 2007 at 5:15 am
Thanks a Lot prasad...
It is working fine..
Warm Regards,
Ezhilan
August 17, 2007 at 5:39 am
Hi..
I have one more query...
How about using this Dynamic Query extensively in the procedure..(In almost all the Updates and Inserts)
Will performace gets affected because of this?
Regards,
Ezhilan
August 17, 2007 at 5:50 am
Very badly.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply