Store procedure:why it does not ask me about variables?

Question

Store procedure:why it does not ask me about variables?

nazaninahmady_sh

Default port

Points: 1465
More actions
December 31, 2008 at 1:36 am

#199552

Hello,
I wrote a Store Procedure:
-- ================================================
alter PROCEDURE Garhasbi1
AS
BEGIN
declare @Cat1_Value int
declare @Cat2_Value int
declare @Date_Time datetime
SELECT ID,Title,Body,Cat1_ID ,Cat2_ID ,Date_Time,Lang_ID,Scr_ID
FROM NewsCenter.dbo.NewsRoom
WHERE ( Cat1_ID= @Cat1_Value) AND ( Cat2_ID= @Cat2_Value) AND (Date_Time = 12/25/2008 )
END
___________________________________________
___________________________________________
But in a New Query when i write:
exec Garhasbi1
why it doe not ask me about :
@Cat1_Value
@Cat2_Value

Viewing 8 posts - 1 through 7 (of 7 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

nazaninahmady_sh (12/31/2008)
Hello,
I wrote a Store Procedure:
-- ================================================
alter PROCEDURE Garhasbi1
AS
BEGIN
declare @Cat1_Value int
declare @Cat2_Value int
declare @Date_Time datetime
SELECT ID,Title,Body,Cat1_ID ,Cat2_ID ,Date_Time,Lang_ID,Scr_ID
FROM NewsCenter.dbo.NewsRoom
WHERE ( Cat1_ID= @Cat1_Value) AND ( Cat2_ID= @Cat2_Value) AND (Date_Time = 12/25/2008 )
END
___________________________________________
___________________________________________
But in a New Query when i write:
exec Garhasbi1
why it doe not ask me about :
@Cat1_Value
@Cat2_Value

Why should it?

You have not declared those variables as input parameters to the stored procedure, but rather as local variables inside the procedure itself.

Further, if you only declare a variable without assigning a value to it, it will default to NULL.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

nazaninahmady_sh Default port Points: 1465 More actions · Answer 2

i tried to correct it:

alter PROCEDURE Garhasbi1

@Cat1_Value int,

@Cat2_Value int,

@Date_Time datetime

AS

BEGIN

...........

but when i wrote :

exec Gahasbi1

in a new query

this mesage appeared:

Procedure or Function 'Garhasbi1' expects parameter '@Cat1_Value', which was not supplied.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

nazaninahmady_sh (12/31/2008)
i tried to correct it:
alter PROCEDURE Garhasbi1
@Cat1_Value int,
@Cat2_Value int,
@Date_Time datetime
AS
BEGIN
...........
but when i wrote :
exec Gahasbi1
in a new query
this mesage appeared:
Procedure or Function 'Garhasbi1' expects parameter '@Cat1_Value', which was not supplied.

Which again is expected. Now you need to supply a value for each input parameter.

What exactly do you want to do?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

nazaninahmady_sh Default port Points: 1465 More actions · Answer 4

i want it asks me about the value of those 3 variables, and when i wrote them , the select can Do,

for example iwant to write "exec Garhasbi "in a new query and when i execute that query it ask about

@Cat1_Value =?

@Cat2_Value =?

@Date_Time =?

then i write the values of each of them ten it select the records from database

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

nazaninahmady_sh (12/31/2008)
i want it asks me about the value of those 3 variables, and when i wrote them , the select can Do,
for example iwant to write "exec Garhasbi "in a new query and when i execute that query it ask about
@Cat1_Value =?
@Cat2_Value =?
@Date_Time =?
then i write the values of each of them ten it select the records from database

This only works in Access. In SQL Server you can not execute a procedure and have it ask you for the parameter values at runtime.

You either need to supply the parameter values upfront or assign default values to the input parameters.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

A way to mimic this Access behaviour would be to right-click on the stored procedure in SSMS -> Execute -> and then supply the values in the window that opens then. However, you will find that in the end only a script is created with the procedure call and those values you supplied.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Vee Hall of Fame Points: 3028 More actions · Answer 7

I think you previously worked in Oracle scripting or in VBSCripting or c,c++ like that. You should explicitly provide the values for variables when you are calling