need help adding onto my stored procedure

calculate age:
CREATE PROCEDURE selectage
AS
SELECTAge = datediff(yy, birthday, getdate()) +
case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else -1
end
FROM nopay
my ? is how do fix this problem:
I add in
WHERE (((Age)>= @.age1 And (Age)<= @.age2))
and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?

Unfortunately SQL won't handle it that way. One approach is to use your calculation in the WHERE column:

WHERE

    datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

             -1

        end BETWEEN @.age1 And @.age2)

Alternately, you could do this:

SELECT

    age

FROM

(SELECT

   Age = datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

            -1

        end

FROM

    nopay) AS subquery

WHERE Age BETWEEN @.age1 And @.age2

Also, I would suggest writing a user-defined function (UDF) tocalculate the age. This will keep your code cleaner and is thesort of thing you will likely need to reuse. For example:

SELECT

   dbo.fnAge(birthday) AS age

FROM

    nopay

WHERE

     dbo.fnAge(birthday)BETWEEN @.age1 And @.age2)

CREATE FUNCTION age (@.indate datetime) -- note: untested!!

RETURNS int

AS

BEGIN

    DECLARE @.age int

    SELECT @.age = datediff(yy, @.indate, getdate()) +

        CASE

            WHEN datepart(dy, @.indate) <= datepart(dy, getdate()) then 0

        ELSE

            -1

        END

    RETURN @.age

END

|||

Can I kiss you?

One more thing I tried to add onto the where and it gave me an error about the comma

WHERE Age BETWEEN @.age1 And @.age2, breed = @.breed

i also did

WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

than it complained about input was not in correct form

Dim paramBreed As SqlClient.SqlParameter
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, breed.SelectedValue)
paramBreed.Value = breed.SelectedValue
objCM.Parameters.Add(paramBreed)

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

InvalidCastException: Cast from string "Dalmation" to type 'Integer' is not valid.]
if i change that statement to

paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar)

the program will execute but than it wont display as if the breed = @.breed was incorrect

|||Don't get excited, it's only SQL after all!
If that's an exact copy-and-paste of your WHERE clause, you need a space between the @.breed and the AND:
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2
(You should use the UDF. This is the sort of thing they were made for.)
|||

my mistake, no the syntax checks fine, just the asp gives that error

i tried writing

SELECT

   dbo.fnAge(birthday) AS age

FROM

and i dont understand dbo.fnAge

do i delete create stored procedure?

|||In your stored procedure, give a length to the @.breed nvarchar parameter
@.breed nvarchar(100)
And in your code, try this line instead for creating the parameter:
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, 100)
(Be sure to use the same length that corresponds to your database column.)
|||

Big thanks,

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay WHEREfirst.username = second.username) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

i figured out how to whatever you call it when u have same feilds and eliminate unwanted data

sql