is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is It possible to have a stored procedure that returns 2 values?

Is It possible to have a stored procedure that returns 2 values?
and call it from a C# webforms application.
Thanks.Do you mean two datatables in one dataset or what?|||output parameters?|||Hi, I mean 2 output values.
My Store Proc is a select query on 1 table in 1 database that I want to return 2 values from.
Thanks.|||If it is a single value, you may try ouput parameter.|||Let me repeat it again...
Hi, I mean (2 output values).
My Store Proc is a select query on 1 table in 1 database that I want to return 2 values from.
Does anyone have a exmple on how to do this..
I know how to do 1 output value but 2 I don't know how.
Thanks.|||the proc would look something like this and in your vb or c# code you would specify the parameters direction as Output or OutputInput then you can get the value out of the parameter value property I believe

CREATE PROCEDURE [OutputParamProc]
@.ItemID int,
@.OutputValue1 int output,
@.OutputValue2 int output
AS
SET NOCOUNT ON
Declare @.Rv int
SELECT @.OutputValue1 = Somecolumn1,
@.OutputValue2 = Somecolumn2
FROM SomeTable
WHERE SomeColumn = @.ItemID
SET @.Rv = @.@.Error
RETURN @.Rv

|||Thanks man that's what I needed.

Is it possible to get Multiple DataTables in a DataSet like VB.NET?

Hello

My stored procedure returns 4 recordsets. I require these recordsets as 4 different datatables in a dataset in Sql Server Reporting Services 2005, to use them on my rdl report.

I don't want to make the 4 diffrent datasets. Is it possible in SqlServer Reporting Services 2005? Please help me. Its urgent.

SSRS uses the first resultset returned from a stored procedure only.|||Thanx for the reply. But is there some way I can get all these recordsets. I have heard about Custom Data Extensions. Do these extensions help me in my problem. Or any other solution, other than data extension, if possible.|||

Well, I think nobody here is interested to answer this one. Do anyone suggest me any good forum for Reporting services queries.

Looking.

|||I won't suggest you write a custom data extension just for this. Instead, if possible, I will consider wrapping up the stored procedure inside another stored procedure which will return the requested dataset.sql

Is it possible to get Multiple DataTables in a DataSet like VB.NET?

Hello

My stored procedure returns 4 recordsets. I require these recordsets as 4 different datatables in a dataset in Sql Server Reporting Services 2005, to use them on my rdl report.

I don't want to make the 4 diffrent datasets. Is it possible in SqlServer Reporting Services 2005? Please help me. Its urgent.

SSRS uses the first resultset returned from a stored procedure only.|||Thanx for the reply. But is there some way I can get all these recordsets. I have heard about Custom Data Extensions. Do these extensions help me in my problem. Or any other solution, other than data extension, if possible.|||

Well, I think nobody here is interested to answer this one. Do anyone suggest me any good forum for Reporting services queries.

Looking.

|||I won't suggest you write a custom data extension just for this. Instead, if possible, I will consider wrapping up the stored procedure inside another stored procedure which will return the requested dataset.

Is it possible to dynamically create columns in a table in SSRS

Hi,

I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

any help is appreciated..

Regards

Karen

Hi,

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

If there's no record in one of your row, you can use =IsNothing(Fields!productname.Value) filter your record. But if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Thanks.

|||

Jin,

Thanks for your response.. what do u mean by remove the column from the recordset... cause i am populating the Reports using a stored procedure and sometimes... there may be some data or not..

Can u pls give me code snipet or an example

Regards

Karen

|||

Hi,

if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Here's the sample code, suppose you have two fields, Sp and Hd. If there's no data in Hd field of your return set, then only Sp field would been selected.

DECLARE @.NULLCOUNTINTSELECT @.NULLCOUNT =COUNT(*)FROM MatrixCapitalWHERE MatrixCapital.Spisnot nullif @.NULLCOUNT=0BEGIN SELECT MatrixCapital.HdFROM MatrixCapitalEND ELSEBEGIN SELECT MatrixCapital.Hd,MatrixCapital.SpFROM MatrixCapitalEND

Thanks.|||

Jin,

Thanks a lot for your answer so this mean that if i have more 5 - 6 columns NULL... i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

I have another question too.. if i have a table variable like

Declare @.tbl table

(

tblid int indentity(1,1),

Col 1,

Col 2,

..,

Col n

)

Is it possible to have a variable column size depending on the number of entries in my select column like for example.. my if i have 10 columns in my select statement and in that 5 are null... so can just insert 5 rows to the table and then remove the remaining columns out.

Regards

Karen

|||

Hi,

i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

Yes, that's right.

Is it possible to have a variable column size depending on the number of entries in my select column like for example..

Based on my knowledge, another way i can see is to use CASE WHEN clause in your SQL, but it still requires you to give differrent sql statments accoring to the "isNUll" result of a column.

Thanks.

sql

Is it possible to create an IIF function for SQL Server?

Hello!

I tried the following code:

create function dbo.iif
(
@.Expression bit,
@.TruePart sql_variant,
@.FalsePart sql_variant
)
returns sql_variant
as
begin
declare @.ReturnValue sql_variant

if @.Expression=1
begin
set @.ReturnValue=@.TruePart
end
else
begin
set @.ReturnValue=@.FalsePart
end

return @.ReturnValue
end

It works fine with statements like this:
select dbo.iif(1,'True','False')

However, when trying a "real" expression, an error appears:
select dbo.iif((1=0),'True','False')
Line 1: Incorrect syntax near '='.

How can I work around this?

Thank you very much in advance.define a variant, and set the value to be the expression. Use the variant in your iif function instead.|||would CASE serve the purpose?

is it possible to create a Master Details Report?

is it possible to create a sort iof Master Details Report?

mean one dataset returning the master data while the other dataiset returns the details

and then relate this two datasets on a common key and show the report .

You can use drillthrough or subreports to do this. The dataset in the main report would return the master data, which is displayed in the main report. In the drillthrough link or subreport, you can then pass the key through parameters.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_interactive_v1_38tn.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp

|||

hi thanks for your reply, i understand that we can do this using drill down reports, what i want to know is is it possible that in a same report i.e. RDL file can i have master and detail datasets and relate this two and report them in a table.

Example:

MasterDataset has following records

EmpNo. FirstName Last Name.

1 john Smith

2 Tom Knight

DetailsDataSet

EmpNo. DepNo DeptName

1 1 prodcution

1 2 Accounts

2 1 production

2 2 Accounts

I want this two datsets to be related on empno and then display them on the table, such that each employees is shown on a new page, with its department details.

Thanks.

|||

You would really be better off creating a subreport with the details. Then link the master report to the subreport based on a paramater. Then you can put a pagebreak at the end of the first report, thereby putting the details on the next page.

So, you'll have two .rdl files but the user will only see one report.

Is it necessary to order again?

I have a function that returns a table:

CREATE FUNCTION dbo.Example(@.Param int)
RETURNS @.Tbl TABLE (
Field1 int,
Field2 int) AS
BEGIN
INSERT @.Tbl (Field1,Field2)
SELECT FieldA,FieldB FROM DataTable
WHERE FieldC = @.Param
ORDER BY FieldA
RETURN
END

The statement that populates the table orders the data. In order
to ensure the results are ordered that way, should the call to the
function include an ordering? I.e., is this sufficient

SELECT * FROM dbo.Example(17)

or is this necessary? --

SELECT * FROM dbo.Example(17) ORDER BY Field1

Thanks!"Jim Geissman" <jim_geissman@.countrywide.com> wrote in message
news:b84bf9dc.0408051457.6ae418c0@.posting.google.c om...
> I have a function that returns a table:
> CREATE FUNCTION dbo.Example(@.Param int)
> RETURNS @.Tbl TABLE (
> Field1 int,
> Field2 int) AS
> BEGIN
> INSERT @.Tbl (Field1,Field2)
> SELECT FieldA,FieldB FROM DataTable
> WHERE FieldC = @.Param
> ORDER BY FieldA
> RETURN
> END
> The statement that populates the table orders the data.

No, it doesn't. Tables are sets of data. They have no order.

Now, your statement may put the data into the table in order... but there's
no guarantee that SQL Server will store it in that order.

> In order
> to ensure the results are ordered that way, should the call to the
> function include an ordering? I.e., is this sufficient
> SELECT * FROM dbo.Example(17)
No

> or is this necessary? --
> SELECT * FROM dbo.Example(17) ORDER BY Field1

Yes.

> Thanks!

Is it a bug of SQL Server 2000 SP4?

Database backup file:
http://www.keepmyfile.com/download/c58b2a565144
Environment:
SQL Server 2000 SP4
Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102
This problem wasn't found in SQL Server 2000 original version and SQL Server
2005.
Any help is appreciated!Well, seems like a bug.
You can fix it by rearranginf the FROM clause in the function in the
following manner.
FROM action_room_req2 arr
JOIN flow_action fa ON arr.flow_id = fa.flow_id
JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
JOIN customer c ON ca.customer_id = c.id
LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"hghua" <hghua@.discussions.microsoft.com> wrote in message
news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
> Database backup file:
> http://www.keepmyfile.com/download/c58b2a565144
> Environment:
> SQL Server 2000 SP4
> Problem:
> The following two statements returns different number of records:
> Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0
> SELECT *
> FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
> WHERE flow_id = 102
> This problem wasn't found in SQL Server 2000 original version and SQL
> Server
> 2005.
> Any help is appreciated!|||Thanks a lot! That works!
Hope Microsoft will solve the problem.
"Roji. P. Thomas" wrote:

> Well, seems like a bug.
> You can fix it by rearranginf the FROM clause in the function in the
> following manner.
> FROM action_room_req2 arr
> JOIN flow_action fa ON arr.flow_id = fa.flow_id
> JOIN cust_action ca ON ca.valid = 0 AND fa.action_id = ca.id
> JOIN action_room ar ON ar.action_id = ca.id AND ar.code = 0
> JOIN customer c ON ca.customer_id = c.id
> LEFT JOIN turn_rule tr ON arr.req_type = 'Mall' and arr.ref_id = tr.id
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "hghua" <hghua@.discussions.microsoft.com> wrote in message
> news:A9FB112B-AD45-463A-A167-CFAF77E20B5D@.microsoft.com...
>
>

Is it a bug of SQL Server 2000 SP4?

Database backup file: 
http://www.keepmyfile.com/download/c58b2a565144

Environment:
SQL Server 2000 SP4

Problem:
The following two statements returns different number of records:
Exec GenPeriodical1 102, null, '20050601', '20050630', null, null, 0 

SELECT *
FROM dbo.OtherFee (null, '20050601', '20050630', null, null, 0)
WHERE flow_id = 102

This problem wasn't found in SQL Server 2000 original version and SQL Server 
2005.
Any help is appreciated!

Is it possible to see text for dbo.OtherFee and GenPeriodical1?|||

Thanks for your reply!

I've got the answer from the newsgroup. The replyer said it seems a bug of SP4 and gave a work-around. If you are interested in this issue, you can download the backup file, it's just 1.18MB.

The store proc and function call other functions, thus not convenient to paste them here.

Is Cursor Best Way To Go?

I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next... Into.
This way, I only have to call the complex SQL once instead of twice.
This seems like the best way to go. However, I've always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn't scroll.
Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?
Thanks.Hi Neil
I'd need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.|||here is a guess without seeing your code.
declare @.v1 int, @.v2 int
select @.v1=[col1], @.v2=[colx]
from (
-- your complex query
) as derived_table
-oj
"Neil" <nospam@.nospam.net> wrote in message
news:vhAne.4180$s64.2269@.newsread1.news.pas.earthlink.net...
>I need to get two values from a complex SQL statement which returns a
>single record and use those two values to update a single record in a
>table. In order to assign those two values to variables and then use those
>variables in the UPDATE statement, I created a cursor and used Fetch
>Next... Into. This way, I only have to call the complex SQL once instead
>of twice.
> This seems like the best way to go. However, I've always used cursors for
> scrolling through resultsets. In this case, though, there is just a single
> record being returned, and the cursor doesn't scroll.
> Is that the most efficient way to go, or is there a better way to be able
> to use both values from the SQL statement without having to call it twice?
> Thanks.
>|||SQL Server doesn't support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:
UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */

> I've always used cursors for
> scrolling through resultsets
Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.
David Portas
SQL Server MVP
--|||> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */
Yes, that was what I was looking for (though I needed to use UPDATE T1
SET... From T1, (Select foo...) As T2...)
Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:
UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@.VALUE) AS T2
WHERE T1.ID=@.VALUE
Do you see any problem with that?

> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.
I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.
Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.
Thanks for your help!
Neil

> --
> David Portas
> SQL Server MVP
> --
>|||Re-arranging order based on a column (pos):
UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos
Update different columns based on the length of a string value:
UPDATE YourTable
SET col1 =
CASE
WHEN LEN(x+y)<=10
THEN a ELSE b END,
col2 =
CASE
WHEN LEN(x+y)>10
THEN a ELSE b END
WHERE ...
David Portas
SQL Server MVP
--
UPDATE|||David Portas Jun 2, 7:07 am show options
Newsgroups: comp.databases.ms-sqlserver,
microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.o=ADrg> - Find
messages by this author
Date: 2 Jun 2005 04:07:17 -0700
Local: Thurs,Jun 2 2005 7:07 am
Subject: Re: Is Cursor Best Way To Go?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
Re-arranging order based on a column (pos):
UPDATE foo
SET pos =3D CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos;
Very neat! I always did a monster CASE expression with extra WHEN
clauses based on (old_pos ' newpos).|||Hi, David.
Here's another one for you. I have an sp that takes various input parameters
for a customer, and processes the data using various case statements. I now
want to run this sp for all customers on a nightly basis. My immediate
reaction, as previously, would be to use a cursor to loop through all the
customers, get the input parameters for the sp from the Customer table, and
call the sp once for each customer. Is there a way to do this without a
cursor?
Thanks,
Neil
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117710437.665131.255080@.g47g2000cwa.googlegroups.com...
> Re-arranging order based on a column (pos):
> UPDATE foo
> SET pos = CASE pos
> WHEN @.old_pos
> THEN @.new_pos
> ELSE pos + SIGN(@.old_pos - @.new_pos)
> END
> WHERE pos BETWEEN @.old_pos AND @.new_pos
> OR pos BETWEEN @.new_pos AND @.old_pos
> Update different columns based on the length of a string value:
> UPDATE YourTable
> SET col1 =
> CASE
> WHEN LEN(x+y)<=10
> THEN a ELSE b END,
> col2 =
> CASE
> WHEN LEN(x+y)>10
> THEN a ELSE b END
> WHERE ...
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> UPDATE
>|||Neil (nospam@.nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?
Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @.@.spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.
Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.
Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.
We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Everything Erland has said. This is where it pays to have a good design
pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
UI you may typically want to pass parameters for a single row. For
procs that implement other business logic however, you should generally
design with a set-based approach in mind. Unfortunately, programmers
used to other languages too often try to encapsulate all logic in procs
that act like scalar functions - a sure route to cursor hell!
David Portas
SQL Server MVP
--

Is Cursor Best Way To Go?

I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next... Into.
This way, I only have to call the complex SQL once instead of twice.

This seems like the best way to go. However, I've always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn't scroll.

Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?

Thanks.Hi Neil

I'd need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.|||here is a guess without seeing your code.

declare @.v1 int, @.v2 int
select @.v1=[col1], @.v2=[colx]
from (
-- your complex query
) as derived_table

--
-oj

"Neil" <nospam@.nospam.net> wrote in message
news:vhAne.4180$s64.2269@.newsread1.news.pas.earthl ink.net...
>I need to get two values from a complex SQL statement which returns a
>single record and use those two values to update a single record in a
>table. In order to assign those two values to variables and then use those
>variables in the UPDATE statement, I created a cursor and used Fetch
>Next... Into. This way, I only have to call the complex SQL once instead
>of twice.
> This seems like the best way to go. However, I've always used cursors for
> scrolling through resultsets. In this case, though, there is just a single
> record being returned, and the cursor doesn't scroll.
> Is that the most efficient way to go, or is there a better way to be able
> to use both values from the SQL statement without having to call it twice?
> Thanks.|||SQL Server doesn't support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:

UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */

> I've always used cursors for
> scrolling through resultsets

Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.

--
David Portas
SQL Server MVP
--|||> SQL Server doesn't support the standard SQL syntax for this but it does
> have a proprietary syntax to do the same job:
> UPDATE T1
> SET x = foo,
> y = bar
> FROM
> (SELECT foo, bar /* your query here */
> FROM ... ) AS T2
> WHERE T2.key_col = T1.key_col
> /* join condition should yield a single row from T2 for each row in
> T1 */

Yes, that was what I was looking for (though I needed to use UPDATE T1
SET... From T1, (Select foo...) As T2...)

Also, since I'm only updating a single row in T1, and since T2 only returns
a single row with values, I eliminated the WHERE T2.keycol=T1.keycol. My SQL
looks like:

UPDATE T1
SET X = T2.FOO, Y=T2.BAR
FROM T1, (SELECT FOO, BAR FROM MYQUERY WHERE ID=@.VALUE) AS T2
WHERE T1.ID=@.VALUE

Do you see any problem with that?

>> I've always used cursors for
>> scrolling through resultsets
> Really? For what purpose? Cursors should be the rare exception rather
> than the rule. Usually there are better set-based solutions.

I guess one of the main areas where I've used them is in order-rearranging
functions -- such as where there are a set of items in a table, each with a
value in a field that specifies the order. The user clicks, say, an up arrow
in the interface, and the current item needs to move up one in order --
decrement it's field value by one, and increment the preceding item's by
one.

Another time I used a cursor was in a procedure in which the length of two
fields combined needed to be compared to a value and then, based on the
length of the combined fields, different values would be placed in a certain
field. I suppose that could have just been done with a set-based solution;
but the cursor seemed more straightforward. It was also only dealing with
one record at a time.

Thanks for your help!

Neil

> --
> David Portas
> SQL Server MVP
> --|||Re-arranging order based on a column (pos):

UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos

Update different columns based on the length of a string value:

UPDATE YourTable
SET col1 =
CASE
WHEN LEN(x+y)<=10
THEN a ELSE b END,
col2 =
CASE
WHEN LEN(x+y)>10
THEN a ELSE b END
WHERE ...

--
David Portas
SQL Server MVP
--

UPDATE|||David Portas Jun 2, 7:07 am show options

Newsgroups: comp.databases.ms-sqlserver,
microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.o*rg> - Find
messages by this author
Date: 2 Jun 2005 04:07:17 -0700
Local: Thurs,Jun 2 2005 7:07 am
Subject: Re: Is Cursor Best Way To Go?
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

Re-arranging order based on a column (pos):

UPDATE foo
SET pos = CASE pos
WHEN @.old_pos
THEN @.new_pos
ELSE pos + SIGN(@.old_pos - @.new_pos)
END
WHERE pos BETWEEN @.old_pos AND @.new_pos
OR pos BETWEEN @.new_pos AND @.old_pos;

Very neat! I always did a monster CASE expression with extra WHEN
clauses based on (old_pos ?? newpos).|||Hi, David.

Here's another one for you. I have an sp that takes various input parameters
for a customer, and processes the data using various case statements. I now
want to run this sp for all customers on a nightly basis. My immediate
reaction, as previously, would be to use a cursor to loop through all the
customers, get the input parameters for the sp from the Customer table, and
call the sp once for each customer. Is there a way to do this without a
cursor?

Thanks,

Neil

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117710437.665131.255080@.g47g2000cwa.googlegr oups.com...
> Re-arranging order based on a column (pos):
> UPDATE foo
> SET pos = CASE pos
> WHEN @.old_pos
> THEN @.new_pos
> ELSE pos + SIGN(@.old_pos - @.new_pos)
> END
> WHERE pos BETWEEN @.old_pos AND @.new_pos
> OR pos BETWEEN @.new_pos AND @.old_pos
> Update different columns based on the length of a string value:
> UPDATE YourTable
> SET col1 =
> CASE
> WHEN LEN(x+y)<=10
> THEN a ELSE b END,
> col2 =
> CASE
> WHEN LEN(x+y)>10
> THEN a ELSE b END
> WHERE ...
> --
> David Portas
> SQL Server MVP
> --
>
>
>
> UPDATE|||Neil (nospam@.nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?

Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @.@.spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.

Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.

Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.

We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Everything Erland has said. This is where it pays to have a good design
pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
UI you may typically want to pass parameters for a single row. For
procs that implement other business logic however, you should generally
design with a set-based approach in mind. Unfortunately, programmers
used to other languages too often try to encapsulate all logic in procs
that act like scalar functions - a sure route to cursor hell!

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Everything Erland has said. This is where it pays to have a good design
> pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
> UI you may typically want to pass parameters for a single row. For
> procs that implement other business logic however, you should generally
> design with a set-based approach in mind. Unfortunately, programmers
> used to other languages too often try to encapsulate all logic in procs
> that act like scalar functions - a sure route to cursor hell!

Permit me to expand a bit on what I touched in my previous post.

In many cases it is reasonable to write a procedure that operates
on a scalar set of values. It cannot be denied that writing such a
procedure is simpler, and thus cuts development costs at that stage.

Passing data in tables is actually quite messy. Let's look at the options:
1) Use a temp table. The caller must create the temp table, and the callee
trust the caller. If the procedure is called from many places, many
callers must create the table. This can be address with an include-
file, if you have the luxury of a preprocessor. We have that, but it's
not a standard feature.
And if even you get by all this, the callee is recompiled for each
new instance of the caller. This can be expensive.
2) A permanent table, typically spid-keyed. We use this technique for the
really heavy-duty stuff. If you make this routine, you get lots of
these tables. Note also that the tables are typically stored disjunct
in the version-control system, which means that procedure and
"parameter list" are in two places.
3) Clients can't use any of 1 or 2, but they can pass comma-separated
lists or XML-documents. But if A_SP calls B_SP, it would be a bad
idea if A_SP built an XML document from its data, only to be able
to call B_SP. What you can to is to have a wrapper that accepts
the XML document, and unpacks that into the temp table or spid-
keyed table. If the client is mainly interested in single-row
operations, it probably needs a scalar wrapper as well. Else, it
will be a lot extra development overhead to build XML documents.

So, clearly, if you at point A in your devleopment cycle only have a need
for a procedure that operates on scalar parameters, you write a procedure
that works with scalar procedure only, because that is what you are paid
for.

If you later at point B need to do the same operation on many rows,
you have to make a judicious choice between:

1) Write a cursor loop.
2) Just forget about the old procedure, and write a new set-based.
3) Replace the old procedure.

If the logic of the procedure is trivial, like "IF NOT EXISTS INSERT ELSE
UPDATE" you should pick #2. But say that the logic is non-trivial, for
instance includes updates to dependent tables in some unnormalised
scenario, then at some point #2 becomes completely impermissible. At
this point #1 can very well be the best pick. Say that you know that
it will be rare that the cursor will comprise as much as 100 rows. If
the procedure takes 100 ms to run, it may be very difficult to motivate
to rewrite the old procedure, if this would take 100 hours.

There is also another issue here that is worth mentioning. Say that your
procedure performs some sort of INSERT operation (in a couple of tables),
and the data comes from some less trustable source, which thus may
supply non-conformant data. If you have a scalar procedure, error
handling is fairly simple. You can do explicit checks on anticipated
errors, but you can be fairly relaxed, because if some data violates a
constraint or trigger check, the operation will fail.

This because a lot more complex if you accept input data in a table.
Because if you apply the same strategy, 1000 rows could fail to insert
when there is an error in a single one. This could very likely be
entirely unacceptable. Thus in case, you will need to duplicate all
constraint and trigger checks in your code, so you can mark which rows
that are illegal.

So while it is easy to say "replace cursor loops with set-based
statments", one should realise that in complex cases, this is far from
trivial.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp