Re: returning @@RowCount
From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 04/03/04
- Next message: Hari: "Re: Please help me populate a table"
- Previous message: JR: "SQLSum\Help"
- In reply to: Shaul Feldman: "Re: returning @@RowCount"
- Next in thread: Shaul Feldman: "Re: returning @@RowCount"
- Reply: Shaul Feldman: "Re: returning @@RowCount"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 3 Apr 2004 09:08:33 -0500
If it is variable length, use VARCHAR. CHAR will pad the data with spaces
and this could muck up your display.
-- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ "Shaul Feldman" <sfeldman@writeme.com> wrote in message news:%23oT9q0XGEHA.3576@tk2msftngp13.phx.gbl... > Thank you for reply. > Another short one - when you use, for example, varchar(x) instead of > char(x) > the SQL server allocates physical x chars, but uses only the size of my > data, or it uses pointers? I need to know that in order to decide what is > better - char(x) or varchar(x). > Thank you. > > -- > With the best wishes, > Shaul Feldman > > "Andrew John" <aj@DELETEmistrose.com> wrote in message > news:#gOTVaXGEHA.740@tk2msftngp13.phx.gbl... >> Shaul, >> >> The main thing you are doing inefficiently is returning @cnt from the > stored >> procedure twice. Once as an output parameter - normal accepted way, >> and once as the return value, which normal usage reserves for > success/failure. >> >> You are also doubling up on return codes - as you have two failure > conditions ? >> @cnt = 0 and @cnt = -1, assuming you have some code you haven't posted > that >> makes the default of -1 meaningful. >> >> @@rowcount is set after every statement, so I very much doubt it is > implemented >> in an inefficient way. >> >> alter procedure stp_Family >> ( >> @FamilyName char(50), >> ) >> as >> select * >> from >> tblCust >> where >> cust_family like rtrim(@FamilyName) >> >> return @@rowcount >> go >> >> works, or with a spot of error checking: >> >> alter procedure stp_Family >> ( >> @FamilyName char(50), >> @cnt int = -1 output >> ) >> as >> declare @Err int >> >> select * >> from >> tblCust >> where >> cust_family like rtrim(@FamilyName) >> >> select @cnt = @@rowcount, @Err = @@error >> return @Err >> go >> >> Although in this case @Err is unlikely to be very interesting, as it only > catches non-fatal errors. >> I'm not fond of your naming convention, but thats a personal issue. Your > use of like, rtrim and char >> is not efficient. varchar which does not add trailing spaces would be a > better bet, and if trailing spaces are the >> only reason you are using like ( rather than say difference), then you >> can > make it a proper =, and >> get a better guarantee of index usage. >> >> Regards >> AJ >> >> >> "Shaul Feldman" <sfeldman@writeme.com> wrote in message > news:OTrfXSVGEHA.3908@TK2MSFTNGP12.phx.gbl... >> Hello, >> I'm trying to receive from my stored procedure a ReturnCode that will > actually represent the number of select records in >> select query. For instance, >> alter procedure stp_Family >> ( >> @FamilyName char(50), >> @cnt int = -1 output >> ) >> as >> select * >> from >> tblCust >> where >> cust_family like rtrim(@FamilyName) >> >> set @cnt = @@rowcount >> return @cnt >> go >> The question is: is there any more efficient way to achieve the same >> goal? >> >> -- >> With the best wishes, >> S. Feldman >> >> > >
- Next message: Hari: "Re: Please help me populate a table"
- Previous message: JR: "SQLSum\Help"
- In reply to: Shaul Feldman: "Re: returning @@RowCount"
- Next in thread: Shaul Feldman: "Re: returning @@RowCount"
- Reply: Shaul Feldman: "Re: returning @@RowCount"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
