Need alittle help with sql statemtn stored procedure

Here is what I am trying to do, If i type to query the stored procedure with two parameters and the two parameters are null, return all rows, else do the query based on the two parameters. Thanks in advance.

Create PROCEDURE [dbo].[Admin_RetrieveCustomerOrders] @.Yearnumint,@.monthnumintASBEGINSET NOCOUNT ON;SELECT o.intOrderId, o.vcCustomerID, o.decTotal, o.dtOrdered,u.vcFirstName, u.vcLastName,(SELECTCOUNT(d.intOrderID)FROM tblOrderDetails dWHERE d.intOrderID = o.intOrderID)AS TotalProductsFROM tblOrders oINNERJOIN tblUserInformation uON o.vcCustomerID = u.vcCustomerIDWHERE YEAR(o.dtOrdered)=@.yearnumANDMONTH(o.dtOrdered)=@.monthnumORDER BY o.dtOrderedDESC END

Thanks Again

Joshua

I think this may work

Essentially you need to modify you WHERE clause to also allow null for the parameters

WHERE (YEAR(o.dtOrdered)=@.yearnumOR @.yearnumisNULL)AND (MONTH(o.dtOrdered)=@.monthnumOR @.monthnumisNULL)

Give the above ago and let us know if it works. 

Need advice on IF...THEN...ELSE type T-SQL syntax

I don't write SQL that often, so...

What is the most "efficient" way to write an SQL statement that accomplishes:

If a record for this key exists in this table, just update it with the following information, but if a record does not exist, create one with the information.

Thanks in advance.

Look into If Exists.

|||

try this

ifexists(SELECT key_fieldfrom yourtablewhere key_field='key')BEGINUPDATE yourtableset [thefield]='your new value'WHERE key_field='key'END

|||

My understanding is that with the 2008 release of sql server, it will add the MERGE command, which is designed precisely for this purpose.

Otherwise, you'll have to use the prior example (but remember to add an "else" branch to do the insert.

Alternatively, there is a way to do it as two statements (one update, one insert) that will handle all the rows you need updated/inserted.

It just depends upon the nature of the update. If all the records to be updated need to be updated with the same info (i.e., add 5% to the salary), one update statement will update all of them.

Then, do an insert statement in the form of:

insert into destination_table (col1,col2) select colA, colB from source_table where source_table.key_column not in (select key_column from destination_table)

That will insert all the missing records.

|||

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

|||

DisturbedBuddha:

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

Duh! Much easier and probably more efficient than the exists check.

|||

http://disturbedbuddha.wordpress.com/2007/11/29/easy-sql-if-record-exists-update-it-if-not-insert-it/

Need a good start on matrix type report, please

I've got the following data I need to report in this format:
Stage P Screen RE-Screen GrdTotal
user1 1 2
3
user2 2 1
6
user3 1 2 1
4
From a sproc the data looks like:
User Type
user1 Stage
user1 RE-Screen
user1 RE-Screen
user2 P-Screen
user2 P-Screen
User2 RE-Screen
I created a report with a subreport for each type count but I haven't been
able to
figure out a way to total up the output from the Sub-Reports. In Crystal I
think I remember passing a gloval value between the report and sub. From
reading on this forum it sounds like there is no way to do this. If you know
how please let me know, then I would have it.
The other ideas is mabe I should have a different SQL and do the
accumulating before I enter the report or I'm gettign ready to write the
report with a grouping on each type. The report has 6 types however.
I haven't worked with Matrix's much and that data regions seems to fit this
need, but...what do you all think?
THANKS!!!
Robert HansenA matrix would exactly do what you want.
The User field would become a matrix row grouping (just drop the field on
row group section of the matrix). The Type field would be a matrix column
grouping with a subtotal (after dropping the field into the column group
section, right click and select "Subtotal"). For the matrix cell you would
use an expression like =Count(Fields!User.Value).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
> I've got the following data I need to report in this format:
> Stage P Screen RE-Screen GrdTotal
> user1 1 2
> 3
> user2 2 1
> 6
> user3 1 2 1
> 4
> From a sproc the data looks like:
> User Type
> user1 Stage
> user1 RE-Screen
> user1 RE-Screen
> user2 P-Screen
> user2 P-Screen
> User2 RE-Screen
> I created a report with a subreport for each type count but I haven't been
> able to
> figure out a way to total up the output from the Sub-Reports. In Crystal
> I
> think I remember passing a gloval value between the report and sub. From
> reading on this forum it sounds like there is no way to do this. If you
> know
> how please let me know, then I would have it.
> The other ideas is mabe I should have a different SQL and do the
> accumulating before I enter the report or I'm gettign ready to write the
> report with a grouping on each type. The report has 6 types however.
> I haven't worked with Matrix's much and that data regions seems to fit
> this
> need, but...what do you all think?
> THANKS!!!
> Robert Hansen|||Thanks!!! Robert
The one thing about a matrix that is throwing me for a loop, is that I need
to display all my Users even if thay have NO activity records (That is
within a data
range) I need their name to show up with blanks counts and 0 GrdTot.
I can do this so nicely with subreports and a real simple table of all users
to drive the report to make all user names show on each row.
I haven't been able to write an SQL to get the data in that form, where I've
got all Users with a row in my dataset. Is there any custom programming
way to get the totals from the subreports? I'm going to try to create a
more complex SQL to get the data I need for thre port so it can include ALL
USERS.
Thanks again for your help!!!
--
Robert Hansen
"Robert Bruckner [MSFT]" wrote:
> A matrix would exactly do what you want.
> The User field would become a matrix row grouping (just drop the field on
> row group section of the matrix). The Type field would be a matrix column
> grouping with a subtotal (after dropping the field into the column group
> section, right click and select "Subtotal"). For the matrix cell you would
> use an expression like =Count(Fields!User.Value).
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
> news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
> > I've got the following data I need to report in this format:
> >
> > Stage P Screen RE-Screen GrdTotal
> > user1 1 2
> > 3
> > user2 2 1
> > 6
> > user3 1 2 1
> > 4
> >
> > From a sproc the data looks like:
> > User Type
> > user1 Stage
> > user1 RE-Screen
> > user1 RE-Screen
> > user2 P-Screen
> > user2 P-Screen
> > User2 RE-Screen
> >
> > I created a report with a subreport for each type count but I haven't been
> > able to
> > figure out a way to total up the output from the Sub-Reports. In Crystal
> > I
> > think I remember passing a gloval value between the report and sub. From
> > reading on this forum it sounds like there is no way to do this. If you
> > know
> > how please let me know, then I would have it.
> >
> > The other ideas is mabe I should have a different SQL and do the
> > accumulating before I enter the report or I'm gettign ready to write the
> > report with a grouping on each type. The report has 6 types however.
> >
> > I haven't worked with Matrix's much and that data regions seems to fit
> > this
> > need, but...what do you all think?
> >
> > THANKS!!!
> > Robert Hansen
>
>|||If you want all users to show up, you need to use an OUTER JOIN in your SQL
query to get them into the resulting data set. Depending on the data source,
the actual SQL syntax for outer joins will vary.
You will also need to modify the expression in the matrix cell to take into
account those users without activity, but showing up in the data set.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
news:7735073D-1D95-4D72-B66A-AE56F696E2EE@.microsoft.com...
> Thanks!!! Robert
> The one thing about a matrix that is throwing me for a loop, is that I
> need
> to display all my Users even if thay have NO activity records (That is
> within a data
> range) I need their name to show up with blanks counts and 0 GrdTot.
> I can do this so nicely with subreports and a real simple table of all
> users
> to drive the report to make all user names show on each row.
> I haven't been able to write an SQL to get the data in that form, where
> I've
> got all Users with a row in my dataset. Is there any custom programming
> way to get the totals from the subreports? I'm going to try to create a
> more complex SQL to get the data I need for thre port so it can include
> ALL
> USERS.
> Thanks again for your help!!!
> --
> Robert Hansen
>
> "Robert Bruckner [MSFT]" wrote:
>> A matrix would exactly do what you want.
>> The User field would become a matrix row grouping (just drop the field on
>> row group section of the matrix). The Type field would be a matrix column
>> grouping with a subtotal (after dropping the field into the column group
>> section, right click and select "Subtotal"). For the matrix cell you
>> would
>> use an expression like =Count(Fields!User.Value).
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
>> news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
>> > I've got the following data I need to report in this format:
>> >
>> > Stage P Screen RE-Screen GrdTotal
>> > user1 1 2
>> > 3
>> > user2 2 1
>> > 6
>> > user3 1 2 1
>> > 4
>> >
>> > From a sproc the data looks like:
>> > User Type
>> > user1 Stage
>> > user1 RE-Screen
>> > user1 RE-Screen
>> > user2 P-Screen
>> > user2 P-Screen
>> > User2 RE-Screen
>> >
>> > I created a report with a subreport for each type count but I haven't
>> > been
>> > able to
>> > figure out a way to total up the output from the Sub-Reports. In
>> > Crystal
>> > I
>> > think I remember passing a gloval value between the report and sub.
>> > From
>> > reading on this forum it sounds like there is no way to do this. If
>> > you
>> > know
>> > how please let me know, then I would have it.
>> >
>> > The other ideas is mabe I should have a different SQL and do the
>> > accumulating before I enter the report or I'm gettign ready to write
>> > the
>> > report with a grouping on each type. The report has 6 types however.
>> >
>> > I haven't worked with Matrix's much and that data regions seems to fit
>> > this
>> > need, but...what do you all think?
>> >
>> > THANKS!!!
>> > Robert Hansen
>>

nchar or char or nvarchar or varchar??

Hi,
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?
Also, what really is the benefit of one over the over :confused:
ThanksOriginally posted by Joozh
Hi,

Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?

Also, what really is the benefit of one over the over :confused:

Thanks

the difference between char and varchar is that: char is a fixed length datatype meaning, if suppose u have char(8) and you store a value say 'Harsh' in this variable then it gets stored a 'Harsh___' where at the right the remaining space is padded with blanks, in short all the 8 bits are utilised.
whereas if it would have been a varchar(8) it would have saved it as 'Harsh' meaning only the exact required size is allocated which is 5 in this case.
When you want to store the data in unicode format use nchar or nvarchar .|||Thanks harshal_in :-) :)

That clarifies... One last quick question:

Is it okay to assume then that the best approach is to use nvarchar OR varchar?|||nvarchar uses double the amount of storage compared to varchar, so use varchar unless you need to store double byte data (eg japanese, chinese)|||Originally posted by Joozh
Thanks harshal_in :-) :)

That clarifies... One last quick question:

Is it okay to assume then that the best approach is to use nvarchar OR varchar?

Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads, improving overall SQL Server performance.

Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.|||There are some advantages to using the CHAR and NCHAR types, but those advantages are somewhat esoteric. If you want more information see Kalen Delaney's Inside SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=mHu18NPJNB&isbn=0735609985&itm=1) (which I see as a "must read" for a SQL geek anyway).

Basically it boils down to bookkeeping. Variable length columns require the DBE (data base engine) to compute both the start and the length of the variable columns. This adds considerable overhead at the row manager level, which makes access to all of the columns in a row with variable width columns take longer. This isn't significant in most cases, but it does matter for bulk loads and similar operations. Particularly for staging very large warehouses, it can be significant.

-PatP|||But the size of the row can be just as much a performance factor...

For example Char(255) will store all of that...now think about returning all the data, as comp[ared to what is just there...

I beleive a rule of thumb is something like 10 chars...

10 or less make char, otherwise varchar...

Pat?|||It is actually more complicated than that, but you could approximate the rule by using: If the maximum column length minus the minimum column length is more than 10 characters, then use a variable length column. I still say you should just read the book so you'll understand the gist of the rule (and 10,000 other important things) and the factors that weigh into it, but the approximation is a lot better than nothing.

-PatP