Calling stored proc with default params from .NET not working.

All,

I have the following :

ALTERPROCEDURE [dbo].[sp_FindNameJon]

@.NameNamevarchar(50)='',

@.NameAddressvarchar(50)='',

@.NameCityvarchar(50)='',

@.NameStatevarchar(2)='',

@.NameZipvarchar(15)='',

@.NamePhonevarchar(25)='',

@.NameTypeIdint=0,

@.BureauIdint,

@.Pageint=1,

@.Countint=100000

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

DECLARE @.SqlStringnvarchar(3000),@.SelectClausenvarchar(1000), @.FromClausenvarchar(1000),@.WhereClausenvarchar(1000)

DECLARE @.ParentSqlStringnvarchar(4000)

DECLARE @.Startint, @.Endint

INSERTinto aaJonTempvalues(@.Page,'here2', @.NameCity);

And inside of aaJonTemp, I have the following :

NULLhere2NULLNULLhere2NULL

How is this possible? If @.Page or @.NameCity is NULL, how come it doesn't default to a value in the stored proc?

Thx

jonpfl

Because a parameter value being NULL is not the same thing as not supplying a parameter at all.

The defaults basically say... If the user hasn't supplied the parameter, then use this. You have supplied a parameter, although the value is NULL. If you want to make it so that if someone sends in a NULL and you want to change it to something else, then you need to code that.

IF @.param IS NULL SET @.param=...

sql

calculating age using sql server function

Hi ya,

I have a table which will be having DOB and I want to calculate the current age of persons, i want to save it into currentage as default.

I have searched over the interent and find some solutions but in my database the DOB is stored as = 'dd/mm/yyyy' and none of the solutions work on this.

Any ideas?

Prince:

I assume by your post that your DOB is stored as a string. Try:

select datediff (year, convert (datetime, '07/15/1908'), getdate())

Dave

|||

Mugambo wrote:

Prince:

I assume by your post that your DOB is stored as a string. Try:

select datediff (year, convert (datetime, '07/15/1908'), getdate())

Dave

Hello Dave,

No you got me wrong, the DOB is stored as Datetime in Sql 2005, the other thing is that datediff is not going to calculate right and thirdly my dates are stored as datetime but in dd/mm/yyyy format.

You can try it by yourself.

|||

First, create this user defined function.

ALTER function [dbo].[fn_GetAge]

(@.in_DOB AS datetime,@.now as datetime)

returns int

as

begin

DECLARE @.age int

IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)

else

IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)

IF datepart(d,@.now) >= datepart(d,@.in_DOB)

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)

ELSE

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1

ELSE

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1

RETURN @.age

end

-

Then, to use it...

Select DOB, dbo.fn_GetAge(DOB, GetDate()) As Age From Table

Since all calculations are based on DateTime's, your formatting shouldn't matter

|||

thanks mastros,

it works really great.