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... -
Avoid error by following.
create procedure mype
as
DECLARE @a varchar(200)
set @a= 'create table #mytable
( itemcode varchar(15),
itemname varchar(50),
unitname varchar(15)
)'
exec (@a)
You may not be able to insert data into the temp table.
Try following
create procedure mype
as
set nocount on
create table #mytable
( itemcode varchar(15),
itemname varchar(50),
unitname varchar(15))
insert into #mytable values('1','2','3')
select * from #mytable
go
exec mype
-
try using sp_executesql
By the way varchar(100) is not sufficient you need more.
Also sp_executesql requires a nvarchar
create procedure mype
as
DECLARE @a nvarchar(200)
set @a= 'create table #mytable ( itemcode varchar(15),itemname varchar(50), unitname varchar(15),unitprice money )'
exec sp_executesql @a
go
exec mype
go
Hope it is helpful
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply