SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 sp dynamic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

afri
Starting Member

Indonesia
8 Posts

Posted - 06/21/2013 :  00:01:27  Show Profile  Reply with Quote
hai..i'm newbe in sql server 2008..

i have example for my query..like this bellow

declare
@iccid varchar(19)= '8943150000015874507%',
@iccid1 varchar(19)= '8943150000015875744%',
@agent_name VARCHAR(50)='Ponniah Srishkanda',
@country varchar(20)='Finland',
@table varchar(max)='rms_at_esp.esp.dbo.mvno_account',
@table1 varchar(max)='rms_at_esp.esp.dbo.mobile_brand',--Austria,Denmark,Finland,France,Netherlands,Poland,Portugal,Sweden,UK
@Message varchar(100)



IF exists (select * from dummy_sim where iccid between @iccid and @iccid1)
begin
SET @Message = 'Criteria Already Exist'
end
else
begin
insert into dummy_sim
(mobileNo,custcode,batchcode,serialcode,iccid,agent_id,shop_id,sim_brand,visit_id,return_status,return_date,country)

select
mobileNo,
custcode,
batchcode,
serialcode,
iccid,
(select top 1 agent_id from agents(nolock) where agent_name =@agent_name) agent_id
,null,
sim_brand=CASE WHEN left(@iccid,11)in(select iccid_prefix from @table1) THEN 'DM' ELSE 'VM' end,
null,
null,
null,country=@country
from @table
where iccid between @iccid and @iccid1

select * from #test1#

end


when i running this query..i have error message

like this :

Msg 1087, Level 16, State 1, Line 29
Must declare the table variable "@table1".
Msg 1087, Level 16, State 1, Line 33
Must declare the table variable "@table".

so what must we do to solve this problem ??

thanks


bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/21/2013 :  00:22:54  Show Profile  Reply with Quote
There are two ways to solve the above problem
1) Using Dynamic Query
http://stackoverflow.com/questions/11669345/sql-stored-procedure-argument-as-parameter-for-dynamic-query
2) Pass Table name as Table Valued Parameter
http://www.aspdotnet-suresh.com/2012/09/pass-table-as-parameter-to-stored.html
http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  02:16:09  Show Profile  Reply with Quote
Please keep in mind that passing the table as table valued parameter can be done only in read only mode

My question is why does your table have to be parameterized? why it changes at runtime? do you mean you've several of those type of tables all with identical type of data? can you explain the need for these redundant structured objects then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

afri
Starting Member

Indonesia
8 Posts

Posted - 06/21/2013 :  03:25:07  Show Profile  Reply with Quote
quote:
Originally posted by afri

hai..i'm newbe in sql server 2008..

i have example for my query..like this bellow

declare
@iccid varchar(19)= '8943150000015874507%',
@iccid1 varchar(19)= '8943150000015875744%',
@agent_name VARCHAR(50)='Ponniah Srishkanda',
@country varchar(20)='Finland',
@table varchar(max)='rms_at_esp.esp.dbo.mvno_account',
@table1 varchar(max)='rms_at_esp.esp.dbo.mobile_brand',--Austria,Denmark,Finland,France,Netherlands,Poland,Portugal,Sweden,UK
@Message varchar(100)



IF exists (select * from dummy_sim where iccid between @iccid and @iccid1)
begin
SET @Message = 'Criteria Already Exist'
end
else
begin
insert into dummy_sim
(mobileNo,custcode,batchcode,serialcode,iccid,agent_id,shop_id,sim_brand,visit_id,return_status,return_date,country)

select
mobileNo,
custcode,
batchcode,
serialcode,
iccid,
(select top 1 agent_id from agents(nolock) where agent_name =@agent_name) agent_id
,null,
sim_brand=CASE WHEN left(@iccid,11)in(select iccid_prefix from @table1) THEN 'DM' ELSE 'VM' end,
null,
null,
null,country=@country
from @table
where iccid between @iccid and @iccid1

select * from #test1#

end


when i running this query..i have error message

like this :

Msg 1087, Level 16, State 1, Line 29
Must declare the table variable "@table1".
Msg 1087, Level 16, State 1, Line 33
Must declare the table variable "@table".

so what must we do to solve this problem ??

thanks




Go to Top of Page

afri
Starting Member

Indonesia
8 Posts

Posted - 06/21/2013 :  03:26:54  Show Profile  Reply with Quote
[quote]Originally posted by afri

hai..i'm newbe in sql server 2008..

i have example for my query..like this bellow

declare
@iccid varchar(19)= '8943150000015874507%',
@iccid1 varchar(19)= '8943150000015875744%',
@agent_name VARCHAR(50)='Ponniah Srishkanda',
@country varchar(20)='Finland',
@table varchar(max)='rms_at_esp.esp.dbo.mvno_account',
@table1 varchar(max)='rms_at_esp.esp.dbo.mobile_brand',--Austria,Denmark,Finland,France,Netherlands,Poland,Portugal,Sweden,UK
@Message varchar(100)



IF exists (select * from dummy_sim where iccid between @iccid and @iccid1)
begin
SET @Message = 'Criteria Already Exist'
end
else
begin
insert into dummy_sim
(mobileNo,custcode,batchcode,serialcode,iccid,agent_id,shop_id,sim_brand,visit_id,return_status,return_date,country)

select
mobileNo,
custcode,
batchcode,
serialcode,
iccid,
(select top 1 agent_id from agents(nolock) where agent_name =@agent_name) agent_id
,null,
sim_brand=CASE WHEN left(@iccid,11)in(select iccid_prefix from @table1) THEN 'DM' ELSE 'VM' end,
null,
null,
null,country=@country
from @table
where iccid between @iccid and @iccid1

select * from #test1#

end


when i running this query..i have error message

like this :

Msg 1087, Level 16, State 1, Line 29
Must declare the table variable "@table1".
Msg 1087, Level 16, State 1, Line 33
Must declare the table variable "@table".

so what must we do to solve this problem ??

thanks



can you help me how to convert this query to dynamic query ?

thanks...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  03:48:08  Show Profile  Reply with Quote
you need to form a dynamic string using @tablename variable and then execute it using EXEC. See links posted by Bandi

Still you didnt answer my earlier question. Why do you need tablename to come from a variable? why cant it be static?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

afri
Starting Member

Indonesia
8 Posts

Posted - 06/21/2013 :  04:26:23  Show Profile  Reply with Quote
dear bandi

i want to set my table from statics to dynamyc because it's query will use in many server..

so i only change the server name if i use in another server..

i have change my query to dinamyc like this bellow :

declare
@iccid varchar(19)= '894619009990166450%',
@iccid1 varchar(19)= '894619009990166574%',


@agent_name VARCHAR(50) = '%HemandKumar%',
@country varchar(20)='Sweden',
@table varchar(max)='rms_se_esp.esp.dbo.mvno_account',
@table1 varchar(max)='rms_se_esp.esp.dbo.mobile_brand',

@Message varchar(100),
@sql varchar(4000)



IF exists (select * from #test1 where iccid between @iccid and @iccid1)
begin
SET @Message = 'Criteria Already Exist'
end
else
begin
set quoted_identifier off
select @sql ='insert into #test1
(mobileNo,custcode,batchcode,serialcode,iccid,agent_id,shop_id,sim_brand,visit_id,return_status,return_date,country)'

select @sql ='select
mobileNo,
custcode,
batchcode,
serialcode,
iccid,
(select top 1 agent_id from agents(nolock) where agent_name like '+@agent_name+') agent_id
,null,
sim_brand=CASE WHEN left(@iccid,11)in(select iccid_prefix from '+@table1+') THEN ''DM'' ELSE ''VM'' end,
null,
null,
null,country=@country
from' +@table+'
where iccid between '+@iccid+' and '+@iccid1+''


exec(@sql)
end


its still error if i running that

the message error :

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'HemandKumar'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'THEN'.


please how to solved this

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  04:41:11  Show Profile  Reply with Quote
why would you still need a variable? so far as tablename is the same it will work well in any server any db provide you didnt hardcode dbname in tablename

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/21/2013 :  05:13:22  Show Profile  Reply with Quote
quote:
Originally posted by afri

dear bandi
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'HemandKumar'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'THEN'.
please how to solved this
thanks

declare @iccid varchar(19)= '894619009990166450',    
@iccid1 varchar(19)= '894619009990166574',


@agent_name VARCHAR(50) = 'HemandKumar',
@country varchar(20)='Sweden',
@table varchar(max)='rms_se_esp.esp.dbo.mvno_account',
@table1 varchar(max)='rms_se_esp.esp.dbo.mobile_brand',

@Message varchar(100),
@sql varchar(4000)

select @sql ='select
mobileNo,
custcode,
batchcode,
serialcode,
iccid, 
(select top 1 agent_id from agents(nolock) where agent_name like ''%'+@agent_name+'%'' ) agent_id
,null,
sim_brand=CASE WHEN left( ''' + @iccid +''' , 11)in(select iccid_prefix from '+@table1+') THEN ''DM'' ELSE ''VM'' end,
null,
null,
null,country=@country
from ' +@table+' 
where iccid between '''+@iccid+''' and '''+@iccid1+''';'
--Print @sql  
 EXEC( @sql)


--
Chandu
Go to Top of Page

afri
Starting Member

Indonesia
8 Posts

Posted - 06/21/2013 :  05:42:19  Show Profile  Reply with Quote
dear Candu

THanks for your query....


Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/21/2013 :  05:43:52  Show Profile  Reply with Quote
quote:
Originally posted by afri

dear Candu
THanks for your query....


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000