SQL Server Performance Forum – Threads Archive

Executing Temp SP

I created temporary Procedure #test
When I tried to execute it
EXEC Tempdb..#test But got the error Server: Msg 201, Level 16, State 4, Procedure #test___________________________________________________________________________________________________________________00000019, Line 0
Procedure ‘#test___________________________________________________________________________________________________________________00000019’ expects parameter ‘@no’, which was not supplied.
Database name ‘tempdb’ ignored, referencing object in tempdb. Once, I knew it how to execute temp sps. Now I have forgotten it Madhivanan Failing to plan is Planning to fail

What does the #test stored procedure look like? Where is it declared? Does it have the @no parameter the error is showing? John

Your proc has a parameter @no declared, without a default value. When you don’t supply a value for the parameter, you get that error – it’s as simple as that.

I agree with Adriaan. If you try the exec statement and set the parameter value, it will likely work. John

Hi Madhivanan,
I ran this and got the result create procedure #tmp1 (@no int) as
select top 1 * from configuration where [email protected] exec tempdb..#tmp1 1
exec #tmp1 1 but when i run exec tempdb..#tmp1 without parameter i got the same error which you are getting.
so need to pass that parameter

Well. Too much of work stops me thinking [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] I forget to supply parameter value Thanks for the help [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan Well. Too much of work stops me thinking [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] I forget to supply parameter value Thanks for the help [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> Working Hard for the Queen[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

>>Working Hard for the Queen Yes it is [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Has this had a problem – it’s stopping me from posting
For temp sps there is no need to specify the database as they are always held in tempdb. To look at the text of your sp you do need to specify the database suggest you do that to see what the parameters needed are.

test
test
sphelptext #myproc fails
tempdb..sphelptext #myproc works add the _ to sphelptext yourselves

Thanks Nigel. Specifying Tempdb is not necessary. As I got error when not supplying parameter value, I tried by executing tempdb..#test. It is the problem of not passing parameter value Madhivanan Failing to plan is Planning to fail

]]>