Execute a query stored in a variable inside the stored procedure?

Posted by Rajujrk under Sql Server on 3/2/2010 | Views : 6527 | Status : [Member] | Replies : 2
Hai All,

I need to execute the query stored in a variable in stored procedure

My Code is

[code]
CREATE PROCEDURE cloneTable (@users varchar(250))
-- Add the parameters for the stored procedure here
AS
Declare @categ varchar(150)
Declare @vd varchar(150)
BEGIN
set @categ=''+@users + '_Catalog_Category'
--set @prod=@users + '_Catalog_Product'
--set @proddoc=@users + '_Catalog_Product_Document'
--set @prodimag=@users + '_Catalog_Product_Image'
--set @prodsub=@users + '_Catalog_Product_Subccat'

set @vd='select * into '+@categ+' from Catalog_Category'


--select * into @prod from Catalog_Product
--select * into @proddoc from Catalog_Product_Document
--select * into @prodimag from Catalog_Product_Image
--select * into @prodsub from Catalog_Product_Subcat
END
[/code]

i need to execute @vd

Thanks & Regards

Rajkumar J



Responses

Posted by: Deeraj on: 3/2/2010 [Member] Starter

Up
0
Down

Alter PROCEDURE cloneTable (@users varchar(250)) 

AS
Declare @StatementToExecute nvarchar(150)
BEGIN
set @StatementToExecute='select * into '+ @users + '_Catalog_Category from Employee'
exec sp_executesql @StatementToExecute
END


HTH!

Thanks,
Dheeraj.

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sagarp on: 3/2/2010 [Member] Bronze

Up
0
Down
CREATE PROCEDURE cloneTable (@users varchar(250))

-- Add the parameters for the stored procedure here

AS


BEGIN

Declare @categ as varchar(150)

Declare @vd as varchar(150)



set @categ=''+@users + '_Catalog_Category'

--set @prod=@users + '_Catalog_Product'

--set @proddoc=@users + '_Catalog_Product_Document'

--set @prodimag=@users + '_Catalog_Product_Image'

--set @prodsub=@users + '_Catalog_Product_Subccat'



set @vd='select * into '+@categ+' from Catalog_Category'





--select * into @prod from Catalog_Product

--select * into @proddoc from Catalog_Product_Document

--select * into @prodimag from Catalog_Product_Image

--select * into @prodsub from Catalog_Product_Subcat

EXEC (@vd )
END


Thanks
SagarP
http://www.emanonsolutions.net
http://emanonsolutions.blogspot.com/

Rajujrk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response