stored procedure debugging - 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)

stored procedure debugging

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-08-06 : 10:16:31

Hi all,

i am trying to debug a stored procedure by adding a conditon to it

*****************
@ID int = NULL

SELECT * FROM TABLE
WHERE ID = @ID
*******************

From this query i want to display record if @id has a value otherwise display all records.. is this be possible using a IF condition. with one query..

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-06 : 10:35:23

[code]WHERE (@ID IS NULL OR ID = @ID)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:05:01

or use

WHERE ID = COALESCE(@ID,ID)

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-08-06 : 15:14:59

Thanks guys,

but i don't want to accept 0 where am passing 0 by default and i want to eliminate that by not validating against 0.

WHERE ID !=0 SO.. should i just type

WHERE ID = COALESCE(@ID,ID) and @id !=0 (does this work)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 15:18:36

quote:
Originally posted by chava_sree

(does this work)

Did you try it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-08-06 : 15:38:11

yeah i tried it..works with STORED PROCEDURE IN SQL SERVER, but when passing my argument from an APPLICATION.. which is 0 (blogid=0) has a problem displaying results. other than this it works fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 16:08:04

If it works in SQL Server, then you've got some application code issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-08-06 : 16:36:24

Tara,

sorry to confuse you, am trying to run this SCRIPT with these parameters, when i pass blogid to the WHERE Clause am using in MY STORED PROCEDURE... am getting 0 results.. coz 0 is been passed for BLOGID to the Where clause and am not getting results, all i need is i want to eliminate that 0 in where cluase and PASS NULL..

here is the WHERE CLAUSE am using in my SP.

SELECT * FROM BLOG
WHERE BLOG.BLOGID = COALESCE(@blogid,BLOG.BLOGID)

USE [vportalDEV]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[up_getSearchBlogEntries_FullText]
@ss = N'test',
@blogid = 0

SELECT 'Return Value' = @return_value

GO

liza1
Starting Member

4 Posts

Posted - 2008-08-07 : 02:01:30

May be this query will help you to Debugging a stored procedure.

In this example, will keep the T-SQL code simple but add another stored procedure in order to understand the callstack window. In order to illustrate this, execute this code to create these stored procedures:

CREATE TABLE [Table1] (
[TestId] [int] IDENTITY (1, 1) NOT NULL ,
[A] [int] NOT NULL ,
[B] [int] NOT NULL ,
[Total] [int] NOT NULL
)

ALTER procedure DebugA
(
@IntIn int,
@IntOut int OUTPUT
)
as

Declare @Id int

exec DebugB
@IntOut,
10,
@IntOut OUTPUT,
@Id OUTPUT

return

Create procedure DebugB
(
@IntIn int,
@IntConst int,
@Total int OUTPUT,
@Id int OUTPUT
)
as

Set @Total = @IntIn + @IntConst

Insert into Table1
(A,B,Total)
values
(@IntIn,@IntConst,@Total)

Select @Id = @@identity

for more information ther are number of SQl Database on http://codervods.com/

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 02:20:26

In the front end app pass a dbnull value to the procedure. I am assuming you are using .net because a integer by default is set to nothing (or 0).

you can use the line of iif(Myint = nothing,dbnull.value,Myint) on the front end application to pass the null value.

Not really recommended but you can also add this to your script if you want the poor mans fix



if @BlogID = 0
Begin
  Set @BlogID = null
end

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-08-11 : 19:18:18

vinnie,

tried your code, it worked.
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources