Name of running procedure

Is it possible in 2000 (or 2005) to get the name of the currently running
procedure, from inside the procedure?
I would like to write code to log procedure events, without having to change
the procedure name as I copy it from one procedure to another.
Thanks,
JayOn Tue, 18 Sep 2007 10:59:51 -0700, "Jay" <nospan@.nospam.org> wrote:
>Is it possible in 2000 (or 2005) to get the name of the currently running
>procedure, from inside the procedure?
Yes.
CREATE PROCEDURE testprocedure AS
SELECT @.@.PROCID AS 'ProcID',
OBJECT_NAME(@.@.PROCID) AS 'Procedure'
GO
EXEC testprocedure
GO
Roy Harvey
Beacon Falls, CT|||Thanks Roy.
"Roy Harvey (MVP)" <roy_harvey@.snet.net> wrote in message
news:t260f39s14nj0ph8k4iofg7i6furlusfi7@.4ax.com...
> On Tue, 18 Sep 2007 10:59:51 -0700, "Jay" <nospan@.nospam.org> wrote:
>>Is it possible in 2000 (or 2005) to get the name of the currently running
>>procedure, from inside the procedure?
> Yes.
> CREATE PROCEDURE testprocedure AS
> SELECT @.@.PROCID AS 'ProcID',
> OBJECT_NAME(@.@.PROCID) AS 'Procedure'
> GO
> EXEC testprocedure
> GO
> Roy Harvey
> Beacon Falls, CT

Mysteriously disappearing stored proc

Hi,
I have an issue with a stored procedure that is disappearing periodically.
It is called from the Agent every minute, and runs just fine for long
periods of a time, often more than a w at once. Then the stored
procedure will be deleted and I will see an entry in the event log for every
time the agent calls it that the stored proc is missing.
It is always the same stored proc. There is another stored proc that also
runs once per minute, and it is never missing.
Does anyone have a clue as to what could cause this, or perhaps some
recommendations for troubleshooting steps I can take in order to find this
out.
Thanks!
Ray ReavisSounds like someone's playing an april fools gag on you.
Response from Microsoft: "It's a feature!"
Sorry, couldn't resist... ; )
"Ray Reavis" wrote:

> Hi,
> I have an issue with a stored procedure that is disappearing periodically.
> It is called from the Agent every minute, and runs just fine for long
> periods of a time, often more than a w at once. Then the stored
> procedure will be deleted and I will see an entry in the event log for eve
ry
> time the agent calls it that the stored proc is missing.
> It is always the same stored proc. There is another stored proc that also
> runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find this
> out.
> Thanks!
> Ray Reavis
>
>|||Ray Reavis wrote:
> Hi,
> I have an issue with a stored procedure that is disappearing
> periodically. It is called from the Agent every minute, and runs just
> fine for long periods of a time, often more than a w at once.
> Then the stored procedure will be deleted and I will see an entry in
> the event log for every time the agent calls it that the stored proc
> is missing.
> It is always the same stored proc. There is another stored proc that
> also runs once per minute, and it is never missing.
> Does anyone have a clue as to what could cause this, or perhaps some
> recommendations for troubleshooting steps I can take in order to find
> this out.
> Thanks!
> Ray Reavis
Some process is obviously dropping the procedure. What user accounts
have rights on the database to drop the procedure? I wouldn't think too
many processes have admin rights or DROP PROC rights on the production
server.
David Gugick
Imceda Software
www.imceda.com|||Yeah, I agree with David. Use Profiler to find the statement dropping
the SP. You may take the stored procedure "create date" as a hint if
the sp is being dropped and recreated.
Aramid
On Wed, 6 Apr 2005 21:08:37 -0400, "David Gugick"
<davidg-nospam@.imceda.com> wrote:

>Ray Reavis wrote:
>Some process is obviously dropping the procedure. What user accounts
>have rights on the database to drop the procedure? I wouldn't think too
>many processes have admin rights or DROP PROC rights on the production
>server.|||David,
Thanks for the tip. I'm not a dba so some of these things don't come
naturally to me. I'm using the profiler to run a trace on all object
deletions, so hopefully that will turn something up.
As far as permissions are concerned, I don't see a place in SQL2000 where it
specifically shows DROP PROC rights. I looked on the permissions of the
database itself and saw Create Proc rights, but couldn't find drop proc
rights. I'm not sure how much that would help anyway since my company
doesn't have control over this server. We're using windows authentication
rather than SQL Server accounts so I presume administrators on that server
will have those permissions.
Thanks,
Ray
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u#peO5wOFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Some process is obviously dropping the procedure. What user accounts
> have rights on the database to drop the procedure? I wouldn't think too
> many processes have admin rights or DROP PROC rights on the production
> server.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Ray Reavis wrote:
> David,
> Thanks for the tip. I'm not a dba so some of these things don't come
> naturally to me. I'm using the profiler to run a trace on all object
> deletions, so hopefully that will turn something up.
> As far as permissions are concerned, I don't see a place in SQL2000
> where it specifically shows DROP PROC rights. I looked on the
> permissions of the database itself and saw Create Proc rights, but
> couldn't find drop proc rights. I'm not sure how much that would
> help anyway since my company doesn't have control over this server.
> We're using windows authentication rather than SQL Server accounts so
> I presume administrators on that server will have those permissions.
Create Procedure rights should do it. As far as permissions, they are on
the SQL side, even with Windows Authentication.
This will show you everyone (excluding administrators and other
fixed-server roles who may have inherent rights) who have explicit
rights to create procs.
select user_name(uid) as "Has Create Proc Rights", * from
dbo.sysprotects
where action = 222 -- create proc
and protecttype != 206 -- Deny
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#YL3RW4OFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ray Reavis wrote:
> Create Procedure rights should do it. As far as permissions, they are on
> the SQL side, even with Windows Authentication.
> This will show you everyone (excluding administrators and other
> fixed-server roles who may have inherent rights) who have explicit
> rights to create procs.
> select user_name(uid) as "Has Create Proc Rights", * from
> dbo.sysprotects
> where action = 222 -- create proc
> and protecttype != 206 -- Deny
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Thanks! I've saved that query in my bag-o-tricks folder. It didn't return
any rows, which is not a surprise. I suspected it was all built-in accounts
that were doing the dirty deed. So far it's been two days since the last
missing sp. I'll be satisfied if it stops but it will kill me to not know
what is going on.
We have the same software running at a lot of businesses nationwide with no
problems such as this -- only this one customer has this issue. It makes us
look bad if we can't prove what is going on even when we're pretty certain
it's not our code causing the problem.
-- Ray

Mysterious Connections

A procedure is run that kills all connections to my SQL
server that have been idle for over 2 hours. I do this by
comparing the lastbatch time and current time. I have
noticed that with a few access databases that connect, the
last batch time is sometimes 01/01/1900. We reckon that it
is because it is of the linked tables refreshing, a
connection is present but no comands are issued.. are we
correct or is there some other reason?Hi Mat,
If you are using ADO, it will sometimes open 2 connections to the database
due to the connection pooling feature, even if you only use one. This might
be what you see.
Jacco Schalkwijk
SQL Server MVP
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:ee5e01c40c49$8f4e0f20$a301280a@.phx.gbl...
> A procedure is run that kills all connections to my SQL
> server that have been idle for over 2 hours. I do this by
> comparing the lastbatch time and current time. I have
> noticed that with a few access databases that connect, the
> last batch time is sometimes 01/01/1900. We reckon that it
> is because it is of the linked tables refreshing, a
> connection is present but no comands are issued.. are we
> correct or is there some other reason?

My Stored Procedure hell!

Ok, I posted here recently and received helpful replies which allowed me to work around a problem. The original question was posted here:

http://forums.asp.net/t/1112669.aspx

But because I'm learning both asp.net 2.0 AND vb 2005 I sort of want to get to the bottom of stuff. I've found out what was going wrong, but I don't understand it.

The problem related to retrieving an output parameter to a stored procedure. I was adding the parameter to the command object I was using as follows:

cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output))

but it wasn't working (ie I wasn't seeing the return value). A helpful poster's work around was to instead do this:

Dim pMemberId As New SqlParameter("@.memberid", SqlDbType.Int)
pMemberId.Direction = ParameterDirection.Output
cmd.Parameters.Add(pMemberId)

Having poked around some more, I've discovered that if I use the original code and then type:

?cmd.Parameters("@.memberid").Direction

I get the value:

Input {1}

This even happens if I explicitly use 2 instead of Data.ParameterDirection.Output

Can anyone explain why this is happening? What's the point of allowing me to pass a parameter into a constructor if it's just going to ignore it?

This just in...

Ah. Might have found the answer...not sure.

I looked up the constructors for SqlParameter, and found that there are 7. As I was typing the parameters it popped up with Direction for the 4th one, so I thought `that's easy - it's output` and selected it. I then closed the brackets and that was that. But I think what might be happening is that the compiler is saying "ah - 4 parameters? That means the fourth parameter is a string called `sourcecolumn`".

http://msdn2.microsoft.com/en-us/library/f38c3x2s(VS.80).aspx

At the point that you're entering the 4th parameter you can click on little arrows to cycle through the 3 remaining possible contructors. To be able to enter the direction requires me to enter a load more parameters.

Perhaps it's getting confused and instead of raising an error about the fact that the number 2 isn't a string it's instead turning my value of 2 into a 1? I've turned on Option Strict but it hasn't made any difference.

|||

Can you give some context on the commands execution? From looking at the previous post, it appears as though you are execiuting a NonQuery, is this still the case?

I ran into this problem a week or so ago, and the something along the following lines solved it:

SqlParameter p = cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.InputOutput))

...//other param code here

p.Value = 666

...//execute

myIntVar = Integer.Parse(p.value.ToString())

Not having a reference to the param seemed to ensure that everything was lost (though we are using the Microsoft DAAB, which does some param cleanup that explained things).

HTH

Aaron

|||

aaroncollett:

Can you give some context on the commands execution? From looking at the previous post, it appears as though you are execiuting a NonQuery, is this still the case?

I ran into this problem a week or so ago, and the something along the following lines solved it:

SqlParameter p = cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.InputOutput))

...//other param code here

p.Value = 666

...//execute

myIntVar = Integer.Parse(p.value.ToString())

Not having a reference to the param seemed to ensure that everything was lost (though we are using the Microsoft DAAB, which does some param cleanup that explained things).

HTH

Aaron

With respect, as explained above, the problem is nothing to do with the call to execute the stored procedure - it's about the way the SqlParameter object is created. Do you have your exact code to hand - it looks like you've sort-of copied mine there! I imagine that part of your

...//other param code here

code is to set the direction, because the way your code is looking makes me think you'll have the same problem as me. Immediately after you've called the constructor to SqlParameter, try:

?p.direction

and I bet you'll see 1 (input) instead of 2(output) ! You can call the SqlParameter constructor with just the name of the parameter and the type, and then set the direction with

p.Direction = ParameterDirection.Output

So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?

|||

Here is some suggested code for you to review. I'll comment inline:

1-- I reviewed your stored procedure from the previous post2-- and came up withthis code to execute it3int memberID = -1;45using( SqlConnection conn =new SqlConnection( connString ) )6{7using( SqlCommand cmd =new SqlCommand("[dbo].[ValidateMemberUsrPwd]", conn ) )8 {9 cmd.CommandType CommandType.StoredProcedure;1011 -- we are adding parametersin order of the12 -- stored procedure signature13 -- not required, but good practice14 cmd.Parameters.Add("@.username", SqlDbType.NVarChar, 16 ).Value = UserNameVariable;15 cmd.Parameters.Add("@.password", SqlDbType.NVarChar, 16 ).Value = PasswordVariable;1617 --this parameter, not onlyis it an output parameter18 -- but by the signature of the stored procedure itis19 -- a'required' parameter. So, it needs avalue.20 --if you want it to not be required, then give it adefault21 --in the signature. Something like22 -- @.memberidint = -1 OUTPUT23 SqlParameter pMemberID =new SqlParameter("@.memberid", SqlDbType.Int );24 pMemberID.ParameterDirection = ParameterDirection.Output;25 pMemberID.Value = DBNull.Value;26 cmd.Parameters.Add( pMemberID );2728 conn.Open();29 cmd.ExecuteNonQuery();3031 memberID = (int) pMamberID.Value;32 }33}

|||

poldie:

So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?

Because there is only runtime checking for the code - to - database relationship. The difference in the ParameterDirection is that it is not required to call a stored procedure with the right direction - it will honor it where appropriate. So, if you use ParameterDirection.Input on a parameter that is marked in the signature of a stored procedure as OUTPUT, it will just not assign the local parameter to the returned value.

|||

davidpenton:

poldie:

So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?

Because there is only runtime checking for the code - to - database relationship. The difference in the ParameterDirection is that it is not required to call a stored procedure with the right direction - it will honor it where appropriate. So, if you use ParameterDirection.Input on a parameter that is marked in the signature of a stored procedure as OUTPUT, it will just not assign the local parameter to the returned value.

No, I mean why isn't the compiler complaining at design time that I'm passing a Direction parameter to a constructor which requires either a string, or a direction followed by several other parameters? I've narrowed down this problem to the point where I no longer need a stored procedure as part of the test. I set the direction as Output and immediately after that, with a breakpoint on the next line, I can take a look at the value of the direction I've just stored and it's been changed to Input!

|||

Ahh! Sorry about that. You are not actually setting the ParameterDirection at all in your scenario. So, I would say that the designer is either casting that to a string and storing it in 'sourcecolumn'. If you want to set the ParameterDirection with the SqlParameter constructor, you must use a more complete signature:

public SqlParameter(string parameterName, SqlDbType dbType,int size, ParameterDirection direction,bool isNullable,byte precision,byte scale,string sourceColumn, DataRowVersion sourceVersion,object value);

So, it would be something like this:

cmd.Parameters.Add(new SqlParameter("@.memberdid", SqlDbType.Int, 4, ParameterDirection.Output,true, 0, 0,null, DataRowVersion.Current, memberId ) );

|||

davidpenton:

Ahh! Sorry about that. I would say that the designer is either casting that to a string and storing it in 'sourcecolumn'. If you want to set the ParameterDirection with the SqlParameter constructor, you must use a more complete signature:

public SqlParameter(string parameterName, SqlDbType dbType,int size, ParameterDirection direction,bool isNullable,byte precision,byte scale,string sourceColumn, DataRowVersion sourceVersion,object value);

So, it would be something like this:

cmd.Parameters.Add(new SqlParameter("@.memberdid", SqlDbTypeInt, 4, ParameterDirection.Output,true, 0, 0,null, DataRowVersion.Current, memberId ) );

Exactly! I did try to enter some of the extra parameters after direction but I couldn't see an end to it - they just went on forever!

So: What I Learnt Today - don't trust the little pop-up `enter some parameters for the method` things because they'll not perform any type checking to ensure that you pass enough parameters of the correct type. Instead, they'll be ordered in alphabetical order. Check that what you're trying to pass is valid for what you want by typing the opening bracket for your parameters and then looking at the whole set of possible signatures. The .Net compiler silently casts ints to strings without so much as a warning, even in Option Strict mode. Be good to yourself...and each other.

my store procedure that call back itself doesn't seems to work.

I have written a store procedure that will delete the record and any children record of it. That store procedure is suppose to call back itself to for every children record, but it only seems to delete the first record (the parent record). Below is my store procedure, pls help.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

AS

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

The global variable @.@.FETCH_STATUS is set only when you perform a fetch. So the WHILE loop condition is wrong. You should code it like:

while (1=1)

begin

fetch...

if @.@.fetch_status < 0 break

...

end

|||

I have change the code, and right now it only delete the first branch, eg. root, first child, child of first child ..etc, I am not sure why.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

AS

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

while (1=1)
BEGIN

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

|||

You have the CHECK for FETCH in the wrong place not like what I showed. Here you are checking FETCH_STATUS of some fetch operation that happened within the delete SP not the one before the call to the SP. So you should change below:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break

to:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

if @.@.fetch_status < 0 break

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID

|||thanks for you help, it's working now. :)

my Store procedure spend TOO MANY TIME, why?

Hi
I have a sql that return about 35 rows
If I execute that sql in the analizer it spend 4 seconds (with
variables)
If I execute the same sql in a store procedure it spend 35 seconds
(with parameters)
The size of the variables or parameters are the same as the columns of the
tables so...
Why it can be?
This is the store procedure
CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
@.Campania int,
@.FechaDesde nchar(10),
@.FechaHasta nchar(10),
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2)
+ '-' + Left(@.FechaDesde, 2)
SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2)
+ '-' + Left(@.FechaHasta, 2)
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
-- DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <=
CONVERT(DATETIME, @.FechaHasta, 102)) AND
(PreciosXFechasPVSRC.fechaBaja >=
CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
@.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
@.FechaDesde, 102)) AND
(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
102)) AND
(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
102)) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
ORDER BY TGENERAL.PVP
END
RETURN @.@.ROWCOUNT
GORal,
First of all, you must change the name of your stored procedure.
Procedures named sp_* are treated differently by the query
processor. This name is used to indicate that there is a system
stored procedure in the master database available from all databases,
so the processor must first search the objects in master, find no
system procedure, then look for your procedure in your database.
If this doesn't solve the problem, consider adding WITH RECOMPILE
to the definition of the stored procedure. What you may be seeing is
the result of a cached plan for the stored procedure that is optimal
for one set of parameters and very bad for another. When you run
this query outside a procedure, the plan will not be cached, but
will be recompiled for the particular variable values.
If that still doesn't work, try declaring local variables for your
parameters and reassigning them inside the procedure:
...
declare @.Campania2 int,
@.FechaDesde2 nchar(10),
@.FechaHasta2 nchar(10),
@.Pais2 nchar(5),
@.Ciudad2 nchar(5),
@.Duracion2 int,
@.Precio2 int
select
@.Campania2 = @.Campania,
@.FechaDesde2 = @.FechaHasta,
..
This sometimes improves the choice of plan with or
without adding WITH RECOMPILE. In your case, it
might also be worthwhile to use local datetime variables for
the result of CONVERT(DATETIME, @.FechaHasta, 102),
CONVERT(DATETIME, @.FechaDesde, 102), and
CONVERT(DATETIME, GETDATE(), 102)
Queries this complicated with many parameters can be difficult
to optimize, particularly if the optimal query plan depends on
the particular parameter values.
Finally, I didn't look closely, but I don't quite understand the
purpose of
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
HAVING (.Multimedias.IDtipoMultimedia = 0)
instead of just putting .Multimedias.IDtipoMultimedia = 0 into
the where clause before grouping. I'm also not sure why the
extra . before Multimedias.
Steve Kass
Drew University
Ral Martn wrote:

>Hi
> I have a sql that return about 35 rows
> If I execute that sql in the analizer it spend 4 seconds (with
>variables)
> If I execute the same sql in a store procedure it spend 35 seconds
>(with parameters)
>The size of the variables or parameters are the same as the columns of the
>tables so...
> Why it can be?
>This is the store procedure
>CREATE PROCEDURE sp_PVSRC_BuscardorOfertasmejorCIUDADESsi
ncursor
> @.Campania int,
> @.FechaDesde nchar(10),
> @.FechaHasta nchar(10),
> @.Pais nchar(5),
> @.Ciudad nchar(5),
> @.Duracion int,
> @.Precio int
>AS
>
>DECLARE @.PrecioMin int, @.PrecioMax int
>SET @.FechaDesde = Right(@.FechaDesde, 4) + '-' + SubString(@.FechaDesde, 4, 2
)
>+ '-' + Left(@.FechaDesde, 2)
>SET @.FechaHasta = Right(@.FechaHasta, 4) + '-' + SubString(@.FechaHasta, 4, 2
)
>+ '-' + Left(@.FechaHasta, 2)
>DECLARE @.TablaAcrear varchar(2000)
>DECLARE @.STRINGINSERT varchar(4000)
>DECLARE @.STRINGadevolver varchar(1000)
>DECLARE @.NombreTablaTemporal nvarchar(250)
>BEGIN
>
> SET @.PrecioMin = 0
> SET @.PrecioMax = 999999
> IF @.Precio = 1
> BEGIN
> SET @.PrecioMin = 0
> SET @.PrecioMax = 300
> END
> ELSE IF @.Precio = 2
> BEGIN
> SET @.PrecioMin = 301
> SET @.PrecioMax = 600
> END
> ELSE IF @.Precio = 3
> BEGIN
> SET @.PrecioMin = 601
> SET @.PrecioMax = 900
> END
> ELSE IF @.Precio = 4
> BEGIN
> SET @.PrecioMin = 901
> SET @.PrecioMax = 1200
> END
> ELSE IF @.Precio = 5
> BEGIN
> SET @.PrecioMin = 1201
> SET @.PrecioMax = 999999
> END
>
> DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
>decimal(9,0),
> @.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
>@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
> DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
>@.IDduracion int
> DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
>
> set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
>int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
>decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
> exec (@.TablaAcrear)
>
> -- CIUDADES
>
> -- DECLARE Cursor1 CURSOR FOR
>
> SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
>PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
>TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
>TGENERAL.IDduracion as IDduracion,
> TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
>Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
>Multimedias.alto as alto
> FROM (
> SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
>TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
> MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
>AS Ciudad
> FROM (
> SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
>MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
> MIX.IDduracion, MIX.nombreIDPVSRC AS
>nombreIDPVSRC, MIX.nombre AS Ciudad
> FROM (
> SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
>PVSRC.IDvuelo,
> PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
>Ciudades.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
>INNER JOIN
> Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
> Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
> CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
> WHERE
> (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
>(PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta, 102)) AN
D
>(PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(), 102)) AND
>(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
>(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
>@.Campania)
> AND (Ciudades.IDCIUDAD = @.Ciudad)
> GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
> PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
> )
> MIX
> INNER JOIN
> (
> SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> FROM PVSRC INNER JOIN
> PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
>PreciosXFechasPVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <=
>CONVERT(DATETIME, @.FechaHasta, 102)) AND
> (PreciosXFechasPVSRC.fechaBaja >=
>CONVERT(DATETIME, GETDATE(), 102)) AND (PreciosXFechasPVSRC.pvp >=
>@.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
> )
> TABLAPVSRC
> ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
>TABLAPVSRC.nombre
> GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
>MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
> ORDER BY MIX.PVP
> )
> MIXG
> INNER JOIN
> (
> SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
>PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
> PVSRC.IDpaquete
> FROM PreciosXFechasPVSRC INNER JOIN
> PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
> WHERE (PreciosXFechasPVSRC.fechaInicio >= CONVERT(DATETIME,
>@.FechaDesde, 102)) AND
> (PreciosXFechasPVSRC.fechaInicio <= CONVERT(DATETIME, @.FechaHasta,
>102)) AND
> (PreciosXFechasPVSRC.fechaBaja >= CONVERT(DATETIME, GETDATE(),
>102)) AND
> (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
> (PreciosXFechasPVSRC.pvp <= @.PrecioMax)
> GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
>PVSRC.IDpaquete
> )
> TABLAPVP
> ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
> )
> TGENERAL
> INNER JOIN PVSRC
> ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
> INNER JOIN
> .Multimedias ON .PVSRC.IDmultimedia =
>.Multimedias.IDmultimedia
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
>TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
>Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> ORDER BY TGENERAL.PVP
>
> END
>
>RETURN @.@.ROWCOUNT
>GO
>
>|||Thanks a lot Steve but with those changes it is worse than before.
I modified de "sp_" , added WITH RECOMPILE, added local parameters and I
modified the query
at the end with
WHERE (.Multimedias.IDtipoMultimedia = 0)
in stead of
HAVING (.Multimedias.IDtipoMultimedia = 0)
but now it spend 80 seconds so if you have more ideas...
now the store procedure is:
CREATE PROCEDURE BuscardorOfertasmejorCIUDADES
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
DECLARE @.TablaAcrear varchar(2000)
DECLARE @.STRINGINSERT varchar(4000)
DECLARE @.STRINGadevolver varchar(1000)
DECLARE @.NombreTablaTemporal nvarchar(250)
DROP TABLE BORRAR4
SET @.NombreTablaTemporal = '_tempBusc' + LTRIM(RTRIM(@.Pais)) +
LTRIM(RTRIM(@.Ciudad)) + LTRIM(RTRIM(STR(@.Campania))) +
LTRIM(RTRIM(@.FechaDesde)) + LTRIM(RTRIM(@.FechaHasta)) +
LTRIM(RTRIM(STR(@.Precio)))
SET @.NombreTablaTemporal = LTRIM(RTRIM( replace(@.NombreTablaTemporal, '-',
'_') ))
SET @.NombreTablaTemporal = 'BORRAR4'
if NOT exists (select * from sysobjects where name = @.NombreTablaTemporal )
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
DECLARE @.IDPVSRC int, @.IDPVSRCNombre nVarChar(500), @.IDPVP int, @.PVP
decimal(9,0),
@.IDPVSRCNomCiudad nVarChar(250), @.IDPVSRCUrl nVarChar(250),
@.IDPVSRCAncho smallInt, @.IDPVSRCAlto smallInt
DECLARE @.IDpaquete int , @.IDvuelo int , @.IDgrupoRC int,
@.IDduracion int
DECLARE @.IDPXFecha int, @.IDPXFPeso int, @.IDPXFechaInicio char(10)
set @.TablaAcrear = 'CREATE TABLE ' + @.NombreTablaTemporal + ' (IDPVSRC
int, nombreIDPVSRC nVarChar(500), CIUDAD nVarChar(250), IDPVP int, PVP
decimal(9,0), URLMULTIMEDIA nVarChar(250), Ancho smallint, Alto smallint)'
exec (@.TablaAcrear)
-- CIUDADES
DECLARE Cursor1 CURSOR FOR
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGINSERT = 'INSERT INTO ' + @.NombreTablaTemporal + ' (
IDPVSRC, nombreIDPVSRC , CIUDAD, IDPVP, PVP, URLMULTIMEDIA , Ancho, Alto) '
SET @.STRINGINSERT = @.STRINGINSERT + 'VALUES (' +
RTRIM(LTRIM(STR(@.IDPVSRC))) + ', '
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNombre)) + '" ,'
SET @.STRINGINSERT = @.STRINGINSERT + '"' +
RTRIM(LTRIM(@.IDPVSRCNomCiudad)) + '" , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(STR(@.IDPVP)))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.PVP))) + ' , '
SET @.STRINGINSERT = @.STRINGINSERT + '"' + RTRIM(LTRIM(@.IDPVSRCUrl))
+ '" , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAncho)))
+ ' , '
SET @.STRINGINSERT = @.STRINGINSERT + RTRIM(LTRIM(STR(@.IDPVSRCAlto)))
SET @.STRINGINSERT = @.STRINGINSERT + ' ) '
-- @.STRINGINSERT
EXEC (@.STRINGINSERT)
FETCH NEXT FROM Cursor1 INTO @.IDpaquete, @.PVP , @.IDPVSRC, @.IDPVP,
@.IDvuelo , @.IDgrupoRC , @.IDduracion , @.IDPVSRCNombre, @.IDPVSRCNomCiudad
, @.IDPVSRCUrl, @.IDPVSRCAncho, @.IDPVSRCAlto
END
CLOSE Cursor1
DEALLOCATE Cursor1
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
RETURN @.@.ROWCOUNT
END
ELSE
BEGIN
set @.STRINGadevolver = 'SELECT * FROM ' + @.NombreTablaTemporal + '
ORDER BY PVP '
exec (@.STRINGadevolver)
END
GO
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:ejJalkYlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Ral,
> First of all, you must change the name of your stored procedure.
> Procedures named sp_* are treated differently by the query
> processor. This name is used to indicate that there is a system
> stored procedure in the master database available from all databases,
> so the processor must first search the objects in master, find no
> system procedure, then look for your procedure in your database.
> If this doesn't solve the problem, consider adding WITH RECOMPILE
> to the definition of the stored procedure. What you may be seeing is
> the result of a cached plan for the stored procedure that is optimal
> for one set of parameters and very bad for another. When you run
> this query outside a procedure, the plan will not be cached, but
> will be recompiled for the particular variable values.
> If that still doesn't work, try declaring local variables for your
> parameters and reassigning them inside the procedure:
> ...
> declare @.Campania2 int,
> @.FechaDesde2 nchar(10),
> @.FechaHasta2 nchar(10),
> @.Pais2 nchar(5),
> @.Ciudad2 nchar(5),
> @.Duracion2 int,
> @.Precio2 int
> select
> @.Campania2 = @.Campania,
> @.FechaDesde2 = @.FechaHasta,
> ...
> This sometimes improves the choice of plan with or
> without adding WITH RECOMPILE. In your case, it
> might also be worthwhile to use local datetime variables for
> the result of CONVERT(DATETIME, @.FechaHasta, 102),
> CONVERT(DATETIME, @.FechaDesde, 102), and
> CONVERT(DATETIME, GETDATE(), 102)
> Queries this complicated with many parameters can be difficult
> to optimize, particularly if the optimal query plan depends on
> the particular parameter values.
> Finally, I didn't look closely, but I don't quite understand the
> purpose of
> GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
> TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
> TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
> Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
> HAVING (.Multimedias.IDtipoMultimedia = 0)
> instead of just putting .Multimedias.IDtipoMultimedia = 0 into
> the where clause before grouping. I'm also not sure why the
> extra . before Multimedias.
>
> Steve Kass
> Drew University
>
> Ral Martn wrote:
>
seconds
the
2)
2)
(IDPVSRC
smallint)'
,
MIXG.Ciudad
IDPVSRC,
AS
PreciosXFechasPVSRC.IDPVSRC
AND
MIX.IDvuelo,
IDPVP,
@.FechaHasta,
TABLAPVP.PVP|||Sorry, the correct store procedure is like that
(without cursors)
CREATE PROCEDURE BuscardorOfertasmejorCIUDADESsincursor
@.Campania1 int,
@.FechaDesde1 nchar(10),
@.FechaHasta1 nchar(10),
@.Pais1 nchar(5),
@.Ciudad1 nchar(5),
@.Duracion1 int,
@.Precio1 int
WITH RECOMPILE
AS
DECLARE @.PrecioMin int, @.PrecioMax int
SET @.FechaDesde1 = Right(@.FechaDesde1, 4) + '-' + SubString(@.FechaDesde1, 4,
2) + '-' + Left(@.FechaDesde1, 2)
SET @.FechaHasta1 = Right(@.FechaHasta1, 4) + '-' + SubString(@.FechaHasta1, 4,
2) + '-' + Left(@.FechaHasta1, 2)
DECLARE
@.Campania int,
@.FechaDesde datetime ,
@.FechaHasta datetime ,
@.Pais nchar(5),
@.Ciudad nchar(5),
@.Duracion int,
@.Precio int
declare @.MiGetdate datetime
SET @.FechaDesde =CONVERT(DATETIME, @.FechaDesde1, 102)
SET @.FechaHasta =CONVERT(DATETIME, @.FechaHasta1, 102)
SET @.MiGetdate = CONVERT(DATETIME, GETDATE(), 102)
SET @.Campania = @.Campania1
SET @.Pais = @.Pais1
SET @.Ciudad = @.Ciudad1
SET @.Duracion =@.Duracion1
SET @.Precio =@.Precio1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 999999
IF @.Precio = 1
BEGIN
SET @.PrecioMin = 0
SET @.PrecioMax = 300
END
ELSE IF @.Precio = 2
BEGIN
SET @.PrecioMin = 301
SET @.PrecioMax = 600
END
ELSE IF @.Precio = 3
BEGIN
SET @.PrecioMin = 601
SET @.PrecioMax = 900
END
ELSE IF @.Precio = 4
BEGIN
SET @.PrecioMin = 901
SET @.PrecioMax = 1200
END
ELSE IF @.Precio = 5
BEGIN
SET @.PrecioMin = 1201
SET @.PrecioMax = 999999
END
SELECT TOP 100 TGENERAL.IDpaquete as IDpaquete , TGENERAL.PVP AS
PVP, TGENERAL.IDPVSRC as IDPVSRC , MIN(TGENERAL.IDPVP) as IDPVP,
TGENERAL.IDvuelo as IDvuelo, TGENERAL.IDgrupoRC as IDgrupoRC,
TGENERAL.IDduracion as IDduracion,
TGENERAL.nombreIDPVSRC AS nombreIDPVSRC, TGENERAL.Ciudad AS Ciudad ,
Multimedias.url AS URLMULTIMEDIA, Multimedias.ancho as ancho ,
Multimedias.alto as alto
FROM (
SELECT MIXG.IDpaquete, MIXG.PVP AS PVP, MIXG.IDPVSRC,
TABLAPVP.IDPVP, MIXG.IDvuelo, MIXG.IDgrupoRC, MIXG.IDduracion,
MIXG.nombreIDPVSRC AS nombreIDPVSRC, MIXG.Ciudad
AS Ciudad
FROM (
SELECT TOP 100 MIN(TABLAPVSRC.IDPVSRC) AS IDPVSRC,
MIX.IDpaquete, MIX.PVP AS PVP, MIX.IDvuelo, MIX.IDgrupoRC,
MIX.IDduracion, MIX.nombreIDPVSRC AS
nombreIDPVSRC, MIX.nombre AS Ciudad
FROM (
SELECT PVSRC.IDpaquete, MIN(PreciosXFechasPVSRC.pvp) AS PVP,
PVSRC.IDvuelo,
PVSRC.IDgrupoRC, PVSRC.IDduracion, PVSRC.nombre AS nombreIDPVSRC,
Ciudades.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC = PreciosXFechasPVSRC.IDPVSRC
INNER JOIN
Destinos ON PVSRC.IDdestino = Destinos.IDdestino INNER JOIN
Ciudades ON Destinos.IDtipo = Ciudades.IDciudad INNER JOIN
CampaniaPaquete ON PVSRC.IDpaquete = CampaniaPaquete.IDpaquete
WHERE
(PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND (PreciosXFechasPVSRC.pvp
>= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax) AND (CampaniaPaquete.IDcampania =
@.Campania)
AND (Ciudades.IDCIUDAD = @.Ciudad)
GROUP BY PVSRC.IDduracion, PVSRC.nombre, Ciudades.nombre,
PVSRC.IDduracion, PVSRC.IDgrupoRC, PVSRC.IDvuelo, PVSRC.IDpaquete
)
MIX
INNER JOIN
(
SELECT PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
FROM PVSRC INNER JOIN
PreciosXFechasPVSRC ON PVSRC.IDPVSRC =
PreciosXFechasPVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <=
@.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate)
AND (PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PVSRC.IDPVSRC, PVSRC.IDpaquete, PVSRC.nombre
)
TABLAPVSRC
ON MIX.IDpaquete = TABLAPVSRC.IDpaquete AND MIX.nombreIDPVSRC =
TABLAPVSRC.nombre
GROUP BY MIX.IDpaquete, MIX.PVP, MIX.IDvuelo,
MIX.IDgrupoRC, MIX.IDduracion, MIX.nombreIDPVSRC, MIX.nombre
ORDER BY MIX.PVP
)
MIXG
INNER JOIN
(
SELECT MIN(PreciosXFechasPVSRC.IDprecioXFechaPVSRC) AS IDPVP,
PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
FROM PreciosXFechasPVSRC INNER JOIN
PVSRC ON PreciosXFechasPVSRC.IDPVSRC = PVSRC.IDPVSRC
WHERE (PreciosXFechasPVSRC.fechaInicio >= @.FechaDesde) AND
(PreciosXFechasPVSRC.fechaInicio <= @.FechaHasta) AND
(PreciosXFechasPVSRC.fechaBaja >= @.MiGetdate) AND
(PreciosXFechasPVSRC.pvp >= @.PrecioMin) AND
(PreciosXFechasPVSRC.pvp <= @.PrecioMax)
GROUP BY PreciosXFechasPVSRC.IDPVSRC, PreciosXFechasPVSRC.pvp,
PVSRC.IDpaquete
)
TABLAPVP
ON MIXG.IDpaquete = TABLAPVP.IDpaquete AND MIXG.PVP = TABLAPVP.PVP
)
TGENERAL
INNER JOIN PVSRC
ON .PVSRC.IDPVSRC = TGENERAL.IDPVSRC
INNER JOIN
.Multimedias ON .PVSRC.IDmultimedia =
.Multimedias.IDmultimedia
WHERE (.Multimedias.IDtipoMultimedia = 0)
GROUP BY TGENERAL.IDpaquete, TGENERAL.PVP , TGENERAL.IDPVSRC,
TGENERAL.IDvuelo, TGENERAL.IDgrupoRC, TGENERAL.IDduracion,
TGENERAL.nombreIDPVSRC , TGENERAL.Ciudad , Multimedias.url ,
Multimedias.ancho, Multimedias.alto, .Multimedias.IDtipoMultimedia
ORDER BY TGENERAL.PVP
RETURN @.@.ROWCOUNT
END
GO

My store procedure returns 2 grids HELP

How do I access the 2nd grid/table in my report?
Basically I have a SP that has a SUM in one grid. The second grid
cotains the data I want to display in RS. Is there a way to get to the
2nd grid without altering the store procedure(Its being used on another
report)?
regards,
Stas K.I just found a similar topic where it says I cannot do this.
Any ideas?|||No, RS can only take a single resultset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136923749.664965.81380@.z14g2000cwz.googlegroups.com...
> How do I access the 2nd grid/table in my report?
> Basically I have a SP that has a SUM in one grid. The second grid
> cotains the data I want to display in RS. Is there a way to get to the
> 2nd grid without altering the store procedure(Its being used on another
> report)?
> regards,
> Stas K.
>

my procedure not returning any value

hi evry one
is there any problem with my code it dos'nt returen any value
please help
CREATE PROCEDURE [dbo].[mMaxId]
AS
set nocount on
declare @.Id bigint
declare @.mID bigint
select @.id = max(TransId) from tbltransaction
if (@.id is null)
begin
set @.mid = 1
end
if ( @.id is not null)
begin
set @.mid = @.id +1
end
return
GOWhat is it supposed to return ? Mid , ID ? Then you have to specify an outpu
t
paramter to catch the value from the calling procedure, if you have a
resultset you can use the resultset by inserting that in temp table while
executing
Insert into #SomeTable
EXEC(Someprocedure)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"mowafy" wrote:

> hi evry one
> is there any problem with my code it dos'nt returen any value
> please help
> CREATE PROCEDURE [dbo].[mMaxId]
> AS
> set nocount on
> declare @.Id bigint
> declare @.mID bigint
> select @.id = max(TransId) from tbltransaction
> if (@.id is null)
> begin
> set @.mid = 1
> end
> if ( @.id is not null)
> begin
> set @.mid = @.id +1
> end
> return
> GO
>|||Hi
Although you can use a select statement to return @.mid as a result set,
depending on what your requirements are, it may be more efficient to use an
output parameter
CREATE PROCEDURE [dbo].[mMaxId] @.mID bigint OUTPUT
AS
set nocount on
SET @.mid =ISNULL(SELECT max(TransId) from dbo.tbltransaction ),0) + 1
return
GO
DECLARE @.nextId bigint
EXEC [dbo].[mMaxId] @.nextId OUTPUT
...
If you are only wanting this number to allocate unique numbers to the
transaction id, then an identity column may be an easier way to implement
this functionality (although they may not be contiguous). You will need to
make sure that you don't write the code in such a way that two processes can
use the same transaction id.
John
"mowafy" wrote:

> hi evry one
> is there any problem with my code it dos'nt returen any value
> please help
> CREATE PROCEDURE [dbo].[mMaxId]
> AS
> set nocount on
> declare @.Id bigint
> declare @.mID bigint
> select @.id = max(TransId) from tbltransaction
> if (@.id is null)
> begin
> set @.mid = 1
> end
> if ( @.id is not null)
> begin
> set @.mid = @.id +1
> end
> return
> GO
>

my problem in detail

How I can rectify divide by zero exception in stored procedure

By checking first, to make sure you're not about to divide by zero ? I suspect a CASE statement is the easiest way to do this.

|||i know it, but actually I want that display 0 or my error message instead of server error message.|||

OK - so what do you need to know ? In your stored proc, return the result of the divide if it's not going to divide by zero, or zero if it is. Then you can check for 0 and show an error if you prefer.

|||

when i write Query like

select 4/0 then it display error msg - 8134

but i don't wan't to display it, i want it as 0.

i have too much table like apr0405i, aprs0506i similary as years wise table. So i create sp and passing '0405' value of parameter of sp which select the table of corresponding year then it display value. but in 0506 then it display the divide by zero exception. so i want to suppress this error message and display 0. because it can't possible to use case with big sql query since i am using more than 15 sql query in one query statement and stored that value in parameter & then print value of paramter

|||

OK - so your database design is not so great ? You create a table for each month ?

I don't know of any way to make SQL Server return 0 when you divide by 0.

|||Joining this thread a little late, oh well....

As far as I am aware, you can't make SQL Server return 0 but you can override the default behaviour (suppressing error messages and the like) and define your own behaviour, such as returning 0. For example:

/*
Override default behaviour
*/
SET ARITHABORT OFF -- Divide by 0 does not halt execution
SET ARITHIGNORE ON -- Divide by 0 does not display error message
SET ANSI_WARNINGS OFF -- Divide by 0 does not display error message

DECLARE @.returnOfCalc As INT

SELECT @.returnOfCalc = 4/0
IF @.returnOfCalc IS NULL
BEGIN
SET @.returnOfCalc = 0
PRINT @.returnOfCalc -- Optional, just as a demo of what can be done
END

/*
Restore default behaviour
*/
SET ARITHABORT ON
SET ARITHIGNORE OFF
SET ANSI_WARNINGS ON

So in your stored procedure, you would issue the first bunch of SET statements at the beginning of the procedure (to override default behaviour) and just before the end of the stored procedure you would issue the second set of SET statements (to restore the default behaviour).

I really don't suggest you mess with the default behaviour in SQL Server, but if you really want/need to then knock yourself out. Just remember to be a good citizen and reset the default behaviour when you finish.

Hope that helps a bit, but sorry if it doesn't
|||

You can do something like:

select coalesce(@.i/nullif(@.j, 0), 0)

-- or

select case @.j when 0 then @.j else @.i/@.j end

|||

use the try... catch

feature of sql2k5

|||

Thanks for ur suggestion, but I already solved it by

case method it goes too much bigger but it gives satisfied results

well, ur answer giving very well theoritical concept, Thank u once again, Next I will send u another query, if i have

|||Thank U chandra ji once again, i was already using same ideas which u send. But Its very well felling inside me, Thanks once again

my output parameter is being treated as an input parameter...why

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems...

ASP Code...

<asp:SqlDataSourceID="SqlDS_Form"runat="server"ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>"SelectCommand="PTN_sp_getFormDD"SelectCommandType="StoredProcedure"OldValuesParameterFormatString="original_{0}"UpdateCommand="PTN_sp_Form_Update"UpdateCommandType="StoredProcedure"OnUpdated="SqlDS_Form_Updated"OnUpdating="SqlDS_Form_Updating"DeleteCommand="PTN_sp_Form_Del"DeleteCommandType="StoredProcedure"OnDeleting="SqlDS_Form_Updating"OnDeleted="SqlDS_Form_Deleted">
<UpdateParameters><asp:ControlParameterControlID="GridView1"Name="DescID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="GridView1"Name="FormNum"PropertyName="SelectedValue"Type="String"/><asp:ParameterName="original_FormNum"Type="String"/><asp:ParameterDirection="InputOutput"size="25"Name="RetVal"Type="String"/></UpdateParameters>

<DeleteParameters>
<asp:ParameterName="original_FormNum"Type="String"/>
<asp:ParameterDirection="InputOutput"Size="1"Name="NewRetVal"Type="Int16"/>
</DeleteParameters>
</asp:SqlDataSource>

Code Behind:

protectedvoid SqlDS_Form_Deleted(object sender,SqlDataSourceStatusEventArgs e)
{
if (e.Exception ==null)
{ string strRetVal = (String)e.Command.Parameters["@.NewRetVal"].Value.ToString();
.....................

Stored Procedure:

CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (@.original_FormNumnvarchar(20), @.NewRetValINT OUTPUT )ASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @.stoptransvarchar(5), @.AvailFoundint, @.AssignedFoundintSet @.stoptrans ='NO'/* ------- Search PART #1 ---------------- */SET @.AvailFound = (SELECTCOUNT(*)FROM dbo.AvailableNumberWHERE dbo.AvailableNumber.FormNum = @.original_FormNum )SET @.AssignedFound = (SELECTCOUNT(*)FROM dbo.AssignedNumberWHERE dbo.AssignedNumber.FormNum=@.original_FormNum )IF @.AvailFound > 0OR @.AssignedFound > 0/* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/--This means the delete can't happen.........BEGINIF @.AssignedFound > 0AND @.AvailFound = 0BEGINSET @.NewRetVal = 1ENDIF @.AssignedFound > 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 2ENDIF @.AssignedFound = 0AND @.AvailFound > 0BEGIN SET @.NewRetVal = 3ENDENDELSEBEGINDELETE FROM dbo.FormWHERE dbo.Form.FormNum=@.original_FormNumSET @.NewRetVal = 0--Successful deletionENDGO

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

When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

Not sure whether it will help, but try to set the Direction of NewRetVal to Output instead of IntputOutput:

<asp:ParameterDirection="Output"Size="1"Name="NewRetVal"Type="Int16"/>

You may also take a look at this article:

Input and Output Parameters, and Return Values

|||It could also be because the type doesn't match. A SQL int is 32-bits, and you've specified a 16-bit int.