How to Execute quary in store procedure
-
i would like to create a temp table and insert values into the temp table in a store procedure, is it possible?, i have tried it as follows
create procedure mype
as
DECLARE @a varchar(100)
set @a= 'create table #mytable
( itemcode varchar(15),
itemname varchar(50),
unitname varchar(15),
unitprice money
)'
exec @a
.....
exec mype
...
but, shows an error message:
"Server: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'create table #mytable
( itemcode varchar(15),
itemname varchar(50),
"
can any one help me?
Thanks in advance
..Better Than Before... -
Hi,
This should work...
alter procedure mype
as
DECLARE @a varchar(200)
set @a= 'create table #mytable
( itemcode varchar(15),
itemname varchar(50),
unitname varchar(15),
unitprice money
)'
exec (@a) -- add ( and ) around the @a
I've increased the variable size to 200 since it looks like your original string is 101 characters long and this is truncating the last bracket of the create table...
-
But why do you want to create the temp table in two steps, is there any specific reason, you can as well create it with the
CREATE table #mytable (column1 datatype ) syntax directly right? Sorry if I am missing anything here. Well if you are passing the table structure as input parameter to the sp and executing it then it is a different case though.
Thanks
Prasad Bhogadi
www.inforaise.com -
thx winash!
you r right...
now i m doing it as follows:
create procedure mype
as
create table #mytable
( itemcode varchar(15),
itemname varchar(50),
unitname varchar(15),
unitprice money
)
insert into #mytable values('1','a','s',1.2)
SELECT * FROM #mytable
DROP TABLE #mytable
..Better Than Before...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply