[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Debug T-Sql

Posted on 2014-08-04
9
Medium Priority
?
348 Views
Last Modified: 2014-08-05
Hi ,

     I have a Function in Sql Server (2008 R2)  . But there is something wrong  when I call the function

      But how can I debug the function  .. PASSING THE INPUT PARAMETERS

      My function name is GetRelObj and  is there a way I can debug  by calling a it in a query
      something   like  
        Select   GetRelOBJ (obj.ID , 'Newface','12' ,'','')  from SOBJ OBJ  where obj.DEFID = 'Class'  

      Infact If I can debug the function by passing the input parameters  , that is good enough

    Thanks

      Sam
0
Comment
Question by:Sam OZ
9 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 600 total points
ID: 40240557
what happens if you try this?

Select   GetRelOBJ (obj.ID , 'Newface','12' ,'','')  from SOBJ OBJ  where obj.DEFID = 'Class'  

or this?

Select   GetRelOBJ (100 , 'Newface','12' ,'','')

without any access to the function itself, it's hard to suggest much
0
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 1000 total points
ID: 40240559
Hi,

You can debug in UDF function using SSMS

select * from [dbo].[CommaSeparatedToTable]('he,loo',',')
Click on Debug and then press F11 step by step it will take you to the Function and return values for it.

you can also follow this link which would let you know something about UDF debugging

http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-8-debugging-udfs.htm

Hope this will help you
0
 

Author Comment

by:Sam OZ
ID: 40240631
Hi PortletPaul /Other Experts,

      The issue looks fairly simple .. It is  just  me being too naive with T-Sql ,

 #  How I  check if a value is null ( actually check for '')  . I tried
       if  @PWF  <> ''    
   But it doesn't  look like the right one ( Though there is no syntax error)
         

##        Can I really have optional  input parameters  ( I have  5 input parameters , can I make last two optional with a default value ?)

   Thanks

       Sam
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40240644
Hi,

You can have optional parameter for that you can pass value inside the function if you want and while calling the function you have to write default in place of value

select * from [dbo].[CommaSeparatedToTable]('he,loo',default) which accepts two parameter but for second i have assigned default which I dont want to pass from outside
0
 

Author Comment

by:Sam OZ
ID: 40240649
Hi Vikas,

     Can you please elaborate ?
   
            If I take your example
                    CommaSeparatedToTable   has two input parameters   and second one is optional

               So Can I call this function as
         
                    dbo.CommaSeparatedToTable ('heloo')
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40240653
No,

Second is option so if you want to pass value then you have to write like

dbo.CommaSeparatedToTable ('heloo',',')

And if you dont want to pass the second parameter then write like

dbo.CommaSeparatedToTable ('heloo',default)

So it will take the default value if you have mentioned during Function creation
0
 

Author Comment

by:Sam OZ
ID: 40240661
Then that won't help me much ..But thanks for the Defualt option

   Also can you please look my  first question

    #  How I  check if a value is null ( actually check for '')  . I tried
       if  @PWF  <> ''    
   But it doesn't  look like the right one ( Though there is no syntax error)
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40240670
You can default pass null to the function parameter while creating the function.

But i am not getting your point for checking the parameter value ...
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 400 total points
ID: 40240702
Do you actually want to check for NULL, or check for blank?

IF @PWF IS NULL     -- check for NULL

IF @PWF <> ''     -- check for non-blank

Open in new window

Blank and NULL are different things. Blank is an empty value whereas NULL is the absence of a value, so you can't use a check for '' to determine if the value is null.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question