sp dynamic - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

sp dynamic

Author

Topic

afri
Starting Member

8 Posts

Posted - 2013-06-21 : 00:01:27

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-21 : 00:22:54

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:16:09

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

afri
Starting Member

8 Posts

Posted - 2013-06-21 : 03:25:07

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

afri
Starting Member

8 Posts

Posted - 2013-06-21 : 03:26:54

[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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 03:48:08

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

afri
Starting Member

8 Posts

Posted - 2013-06-21 : 04:26:23

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 04:41:11

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-21 : 05:13:22

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)