execute sp with parameter

Question

Post reply

execute sp with parameter

pat-261672

Hall of Fame

Points: 3325
More actions
August 1, 2009 at 9:14 pm

#212772

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

have a look at sp_executesql in BOL. That will give you all the information you need.

Just a tip: prefix your strings with an N to get nvarchar string constants.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

melloman SSC Journeyman Points: 94 More actions · Answer 2

Probably a bit late, but it depends on what you’re trying to do.

You example is almost there. pre-fix the @sp-2 variables with the word SET, to place your value/string into your variables. You need to DECLARE then before that,

For example

------------

DECLARE @id varchar (28)

DECLARE @sp1 varchar (28)

SET @id = 'select disctint id from idtable'

SET @sp1 = 'select sp1 from table where key = '143''

Keep exec line as is:-

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

And in you stored procedure, add your variables between the CREATE and AS commands:-

CREATE PROCEDURE sp_data_cont

(

@id varchar(28)

,@sp1 varchar(28)

,@result varchar (100) OUTPUT

)

AS

If you want to pass a result/ return code or any data back to the executing SQL (i.e. your)

Example you need to use the OUTPUT command when defining your variable:-

DECLARE @id varchar (28)

DECLARE @sp1 varchar (28)

DECLARE @result varchar (28)

SET @id = 'select disctint id from idtable'

SET @sp1 = 'select sp1 from table where key = '143''

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

And on Stored procedure your top will look like the following example

(don’t declare these variables else wherein you stored procedure)

CREATE PROCEDURE sp_data_cont

(

@id varchar(28)

,@sp1 varchar(28)

,@result varchar (100) OUTPUT

)

AS

I hope that this helps