Showing posts with label adding. Show all posts
Showing posts with label adding. Show all posts

Friday, March 30, 2012

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

Wednesday, March 28, 2012

Need help adding 3rd table to a Join

Hi can someone help me I'm brainless, I need to add a field from a third database to this view

CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

The field I need to add is COLLECTION, with Left Outer Join

mCollection
ITEM_ID
COLLECTIONAnd COLLECTION is a member of which table?|||I need the COLLECTION field from the mCollection Table to be included in the mProduct_Search view|||Nevermind my brain came back to life, I have answered my own question...
CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH], B.COLLECTIONS as [COLLECTIONS],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

Left Outer Join (Select * from mCollections) B

on mProduct.ITEM_ID = B.ITEM_ID

Monday, March 12, 2012

Need advice on expanding to Direct Attached Storage

We are currently using an application which uses SQL Server 2005. Both sit on the same box.

This month we're adding another piece of software to our network on a new box which also depends on SQL Server. Since we don't have a directed attached storage device to store (as recommended by Microsoft), we're contemplating installing SQL Server again just for this application. Of course this means that we have to maintain two separate SQL Server databases.

Any thoughts on what we should do?

Thanks

Hi,

You can install and run two or more instances of SQL Server on a server. But this means increase in number of services and maintenance (like SP upgrades, etc). But if you have different instances, you can restart an instance without affecting the other.

If you think you may have such situations and your users (or customers) are different (so why they will bother for someone else's application), you can install as a seperate instance.

But you can configure your SQL Server security to give service to two different applications.

Eralper

http://www.kodyaz.com