execute sp with parameter - 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 2005 Forums

Transact-SQL (2005)

execute sp with parameter

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2009-08-02 : 16:54:44

Hi all,

I am lost to do this:

@id = 'select disctint id from idtable'
@sp1 = 'select sp1 from table where key = '143''
@sp2 = 'select sp2 from table where key = '143''
@sp3 = 'select sp3 from table where key = '143''
@sp4 = 'select sp4 from table where key = '143''

Exec sp_data_cont 143, 234, @id, @sp1, @sp2, @sp3, @sp4

how can i pass values to parameters!?

please guide!!

Thanks
pat

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-02 : 21:11:10

[code]@id = 'select disctint id from idtable'
@sp1 = 'select sp1 from table where key = @variable'
@sp2 = 'select sp2 from table where key = @variable'
@sp3 = 'select sp3 from table where key = @variable'
@sp4 = 'select sp4 from table where key = @variable'
@variable = '143'
Exec sp_data_cont @variable, 234, @id, @sp1, @sp2, @sp3, @sp4
[/code]
this works?

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-02 : 21:35:12

quote:
Originally posted by PatDeV

Hi all,

I am lost to do this:

@id = 'select disctint id from idtable'
@sp1 = 'select sp1 from table where key = '143''
@sp2 = 'select sp2 from table where key = '143''
@sp3 = 'select sp3 from table where key = '143''
@sp4 = 'select sp4 from table where key = '143''

Exec sp_data_cont 143, 234, @id, @sp1, @sp2, @sp3, @sp4

how can i pass values to parameters!?

please guide!!

Thanks
pat

If you are gonna execute the sp_data_cont for each of the distinct id of idtable, you need a loop to do that.

as for the sp1 . . sp4, you can simplified into one select statement


select @sp1 = sp1,
       @sp2 = sp2,
       @sp3 = sp3,
       @sp4 = sp4
from   table 
where  key = '143'

KH
[spoiler]Time is always against us[/spoiler]

PatDeV
Posting Yak Master

197 Posts

Posted - 2009-08-03 : 08:00:57

no it only one id at a time so there will be one at all time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources