passing parameters to storedprocedure dynmaically

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

passing parameters to storedprocedure dynmaically

Author

Topic

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-04-22 : 02:39:24

hi the following is the query i tried to execute in sqlserver storedprocedure,the problem shows is in '+@CSE' .'+@CSE' is actually a parameter value,but shows error as "invalid column name 'sekar'",please give solution for this task

set @sql='insert into '+@client1+'(MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date)
select MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date from ' +@master1+ '
where ' +'Client_Name='+@CSE+ ' and datepart(dd,Updated_Date) =datepart(dd,getdate()) and datepart(mm,updated_date)= datepart(mm,getdate()) and datepart(yy,Updated_Date)= datepart(yy,getdate())'
and datepart(mm,cast(updated_date as datetime))=datepart(mm,getdate()) and datepart(yy,cast(updated_date as datetime))=datepart(yy,getdate())
exec (@sql)

karthikeyan

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 02:50:15

Ok your +@CSE variable contain table name or what?

Raghu' S

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-04-22 : 02:53:58

quote:
Originally posted by raghuveer125

Ok your +@CSE variable contain table name or what?

Raghu' S

@CSE is a column name
for eg:'select * from table where Client_name=teptronic'

Here @CSE is 'teptronic'

karthikeyan

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 02:58:24

Try this

set @sql='insert into '+@client1+'
(MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date)
select
MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date from ' + @master1 + '
where ' +'Client_Name='+ @CSE + ' and datepart(dd,Updated_Date) =datepart(dd,getdate()) and datepart(mm,updated_date)= datepart(mm,getdate()) and datepart(yy,Updated_Date)= datepart(yy,getdate())
and datepart(mm,cast(updated_date as datetime))=datepart(mm,getdate()) and datepart(yy,cast(updated_date as datetime))=datepart(yy,getdate())'
exec (@sql)

Raghu' S

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-04-22 : 03:01:08

Hi ,i have given tablename ,columnname dynmically through storedprocedure parameters,(i have send the storedprocedure below)

create procedure [dbo].[Fetchclient](@client1 varchar(50),@client2 varchar(50),@master1 varchar(50),@master2 varchar(50),@CSE char(50))
as
begin
declare @sql varchar(1000)
set @sql='insert into '+@client1+'(MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date)
select MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date from ' +@master1+ '
where Client_Name='+ @CSE +' and datepart(dd,Updated_Date) =datepart(dd,getdate()) and datepart(mm,updated_date)= datepart(mm,getdate()) and datepart(yy,Updated_Date)= datepart(yy,getdate())'
exec (@sql)
end

karthikeyan

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 03:04:21

Could you post some dummy data 2 or 3 records only

select MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date from Your_Table

Raghu' S

karthikeyan.marlen
Starting Member

16 Posts

Posted - 2011-04-22 : 03:10:46

hi following is the data for the above mentioned column

901 pricegrabber NULL price01 price123 2 das dsa 120 sd NULL 2011-04-22 00:00:00.000 2011-04-21 20:20:00.000 1 2011-04-23 00:00:00.000

karthikeyan

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 05:17:54

Try this
create procedure [dbo].[Fetchclient](@client1 varchar(50),@client2 varchar(50),@master1 varchar(50),@master2 varchar(50),@CSE char(50))
as
begin
declare @comp nvarchar(50)
set @comp=quotename(@CSE,'''')
declare @sql varchar(1000)
set @sql='insert into '+@client1+'(MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date)
select MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date from ' +@master1+ '
where Client_Name='+ @comp +' and datepart(dd,Updated_Date) =datepart(dd,getdate()) and datepart(mm,updated_date)= datepart(mm,getdate()) and datepart(yy,Updated_Date)= datepart(yy,getdate())'
exec (@sql)
end

---If not works
--then
create procedure [dbo].[Fetchclient](@client1 varchar(50),@client2 varchar(50),@master1 varchar(50),@master2 varchar(50),@CSE char(50))
as
begin
declare @comp nvarchar(50)
select @CSE --Send this output
set @comp=quotename(@CSE,'''')
declare @sql varchar(1000)
set @sql='insert into '+@client1+'(MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date)
select MOBID,Client_Name,Image_URL,Model_No,MPN_SKU,No_of_Merchants,Product_Description,Product_Name,My_Price,Product_Page_URL,Status,Updated_Date,Updated_Time,Client_ID,Delivery_Date from ' +@master1+ '
where Client_Name='+ @comp +' and datepart(dd,Updated_Date) =datepart(dd,getdate()) and datepart(mm,updated_date)= datepart(mm,getdate()) and datepart(yy,Updated_Date)= datepart(yy,getdate())'
exec (@sql)
end

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources