Creating User Defined Function for Rolling Calendar Year

Has anyone ever created a UDF for a rolling calendar year? I would like to
use that instead of writing this kind of stuff all the time:
if(datepart(month,@.CurrentDate) =12)
Begin
set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
end
if(datepart(month,@.CurrentDate) !=12)
Begin
set @.PastDate = dateadd(month,1,@.PastDate)
End
set @.CurrentDate = dateadd(month,1,@.Currentdate)
if(datepart(day, @.CurrentDate) <= 30)
Begin
set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
Begin
set @.currentDate = @.CurrentDateStep
end
if datepart(month, @.CurrentDate) = 3
Begin
set @.CurrentDate = dateadd(day,2,@.CurrentDate)
end
end
Thanks in advance for any assistance.
Thanks,
SPHow about a calendar table?
http://www.aspfaq.com/2519
"SP" <SP@.discussions.microsoft.com> wrote in message
news:8E1056B9-3011-4523-894A-1087FE936B1A@.microsoft.com...
> Has anyone ever created a UDF for a rolling calendar year? I would like to
> use that instead of writing this kind of stuff all the time:
> if(datepart(month,@.CurrentDate) =12)
> Begin
> set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
> end
> if(datepart(month,@.CurrentDate) !=12)
> Begin
> set @.PastDate = dateadd(month,1,@.PastDate)
> End
> set @.CurrentDate = dateadd(month,1,@.Currentdate)
> if(datepart(day, @.CurrentDate) <= 30)
> Begin
> set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
> if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
> Begin
> set @.currentDate = @.CurrentDateStep
> end
> if datepart(month, @.CurrentDate) = 3
> Begin
> set @.CurrentDate = dateadd(day,2,@.CurrentDate)
> end
> end
> Thanks in advance for any assistance.
> Thanks,
> SP
>|||Hi,
Pardon me for my ignorance, can you please elucidate with an example what
you are trying to achieve?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sure. Sorry for the sparse information.
At the company that I work for, they are always interested in a rolling
calendar year. When I write reports with Reporting Services, they may run
them for say 2006. The end date would always change depending on what month
the report is run. For example, if the report is run today, they would want
the results to show 6/1/2005 to 5/31/2006. If it gets run next month,
7/1/2005 to 6/30/2006, etc.
I always have to wrote some funky code to consider February, etc.
I just thought if someone had written a UDF, that might help. Or send me in
the right direction.
I hope this clarifies things a little and I will definitely take a look at
the Date table. I have done similiar things with an Access database, but
didn't think about it for work.
Thanks to all for looking.
SP
"Omnibuzz" wrote:

> Hi,
> Pardon me for my ignorance, can you please elucidate with an example wha
t
> you are trying to achieve?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Maybe, this should work.. If I understood it wrong.. correct me..
declare @.a datetime
set @.a = getdate() -- Date on which you are running the report
select
dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of th
e month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS 'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS 'End_Dt'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message news:30BC1C01-180E-442C-8D
BE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
>
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
>
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>|||I think it would be cleaner to use the next month's start instead of 3 ms
earlier, and use < on the end_dt instead of <= (and obviously, don't use
between).
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of
the month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
'End_Dt'
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Agreed.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhBC5SIlGHA.984@.TK2MSFTNGP05.phx.gbl...
>I think it would be cleaner to use the next month's start instead of 3 ms
>earlier, and use < on the end_dt instead of <= (and obviously, don't use
>between).
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
> As written, the End_dt is midnight on the last day of the month,
> effectively leaving out all entries for that day.
> I would add one more dateadd step to include ALL times on the last day of
> the month (up to 23:59:59.997)
> DECLARE @.Today datetime
> SET @.Today = getdate() -- Date on which you are running the report
> SELECT
> dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
> 'Begin_Dt'
> , dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
> 'End_Dt'
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
>sql

Creating Output in columns instead of Rows

Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).

Right now my output would look like this:
PartNum__Yr-Mnth__Qty
Part123___Jan03____88
Part123___Feb03____33
Part123___Mar03____06

What I would like to output is:

PartNum___Jan03__Feb03__Mar03
Part123_____88_____33_____06

Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
From Table1
where
Date >= To_Date('01/01/2003','mm/dd/yyyy') and
Date <= To_Date('01/31/2004','mm/dd/yyyy')
Order by PartNum, Date

(Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)

I have seen an example of PIVOT, but could not get that to work.

Any suggestions?

Thanks!select Distinct PartNum,
cast(0 as decimal(15,0)) as Jan03,
cast(0 as decimal(15,0)) as Feb03,
cast(0 as decimal(15,0)) as Mar03,
into #temp
from Table1

Then from there run your regular query, put in a temp table.

Then from there you can update the columns in the above table.

Its ugly but works.

You can also do subselects.

Hope this gets your mind rolling|||Thanks for the reply. Guess I should not have made my example so simple!

The date actually comes from (part of select statement):
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"

So the "field" Yr-Mnth is not a table field, but created through the select statement.

The Qty comes from:
(dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
There is only 1 value per month for each value.

In this example, the output would have 13 columns of demand data, 1 listed for each month.

As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.

Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)

select
pm.HostPartID,
pm.partcustom1,
lt.loctypename,
lm.loccustom5,
lm.HostLocID,
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
dh.historyamount,
dh.historyschamount
from
DEMAND_HISTORY dh,
PART_MASTER pm,
LOCATION_MASTER lm,
LOC_TYPE lt
where
pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
(dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
order by
pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId

(I don't have to use (+) in my table joins as there will always be a match)

Guess placing the data into columns instead of rows is not as simple as I had hoped!?!|||well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.

Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.

You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.

Let me know Monday if you have not figured it out, I am leaving the office.|||If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

select col1, col2,
sum(case when month(datecol1)=1 then value1 else 0 end) month1,
...
sum(case when month(datecol1)=12 then value1 else 0 end) month12
from table1 ....
where ...
group by col1, col2

creating multiple tables?

Hello,
I need to create around 1500 similar tables.

Does anyone know how to create them all at once instead of one-by-one?


thanks

If these tables currently reside in another RDBMS then you MAY be able to generate sql for them. I am a pure MS SQL SERVER geek so I am unsure of, but regardless you would have to convert the scripts to use TSQL's create table statement.

If your tables are similiar enough and you must create these fresh you do some thing like the following (use dynamic sql):

declare @.SQL nvarchar(1000)

declare @.i int

select @.i = 0, @.SQL = ''

while @.i <= 1499

begin

set @.SQL = 'CREATE TABLE ' + [table name algorithm goes here] + [table definition goes here

exec sp_executesql @.SQL]

set @.i = @.i + 1

end

TSQL Create Table Syntax:

CREATE TABLE table_name

( { < column_definition > | < table_constraint > } [ ,...n ]

)

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ]

]

} ]

[ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ PRIMARY KEY | UNIQUE ]

| REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) }

]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}|||thank you very much

I will post again in this thread if I have any troubles|||

Hi I'm having some trouble, I made this query:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL =

WHILE @.i <= 32228

begin

set @.SQL = 'CREATE TABLE' + tbl_i_quotes + (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)

exec sp_executesql @.SQL

set @.i = @.i + 1

end

and i got this as an error message:

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'WHILE'.

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near 'nchar'.

thanks again

|||

this works:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL = ''

WHILE @.i <= 32228

BEGIN

set @.SQL = 'CREATE TABLE' + '[tbl_' + CONVERT(nvarchar(5),@.i) + '_quotes] (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)'

exec sp_executesql @.SQL

set @.i = @.i + 1

end

|||it works! thank you so much!


I'd hate to bother you more, but you seem so informative...

do you know much about bulk inserting a folder full of flat files into select tables?
maybe using integration services? i've tried BCP and have had pretty negative results.


thanks again, yer a life saver.|||your welcome :) glad it helped. I used DTS in sql2000 ALOT, but have had almost 0 exp. with SSIS in 2005 as of yet. However, SSIS (like DTS) has always been a great tool for importing and exporting data and I would look at leveraging it for loading a flat file. BCP/BULK INSERT is good to.

Creating Diffent Account for replication

Hello there
I've tried relication many times before. It seems that it didn't always
worked ,because I still used System account instead of replacing it with
another account.
Now when i'm trying to give it another account, the agent become unuseable.
What i need to do to create another user account that i can use it by sql
server agent?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
Roy,
set the sql server agent to use a domain account. Have this account in the
local adminsand in the sysadmin role on the publisher . The easiest method
is to use the same account with the same setup on both the
publisher/distributor and the Subscriber. If not, then refer to BOL for more
granular approaches
(http://msdn.microsoft.com/library/de...plsec_8dix.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Replication will never work under the system account - what exactly do you
mean by the agent has become unusable? Is it generating an error message?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I've tried relication many times before. It seems that it didn't always
> worked ,because I still used System account instead of replacing it with
> another account.
> Now when i'm trying to give it another account, the agent become
> unuseable.
> What i need to do to create another user account that i can use it by sql
> server agent?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary
After i changed the system account to my username as account, i couldn't run
the sql server agent again, until i return it to use the system account
The error i got: 1609 Sql server agent service could not run dut to logon
failure
How can i solve it?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Pp01C5$FHA.1676@.TK2MSFTNGP09.phx.gbl...
> Replication will never work under the system account - what exactly do you
> mean by the agent has become unusable? Is it generating an error message?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:OYGqdp0$FHA.2652@.TK2MSFTNGP09.phx.gbl...
>

Creating Cursor from Stored Procedure

Hi guys!
i want to create one cursor in the t-sql. the problem is i want to use
stored procedure instead of select command in cursor.

can anyone tell me how can i use stored procedure's o/p to create
cursor?

i'm using sql 2000 and .net 2.0

thanks,

LuckyFirst, try to rewrite your app so you don't use cursors.

Second, if you must use a cursor, you can create a temp table to hold
the output of your stored procedure, and then build a cursor from that;
e.g.:

CREATE TABLE #splat (columnlist)

INSERT INTO #splat
exec myproc

DROP TABLE #splat

Stu

Lucky wrote:
> Hi guys!
> i want to create one cursor in the t-sql. the problem is i want to use
> stored procedure instead of select command in cursor.
> can anyone tell me how can i use stored procedure's o/p to create
> cursor?
> i'm using sql 2000 and .net 2.0
> thanks,
> Lucky|||Post your exact requirement. There can be better method of what you are
trying to do now

Madhivanan

Lucky wrote:
> Hi guys!
> i want to create one cursor in the t-sql. the problem is i want to use
> stored procedure instead of select command in cursor.
> can anyone tell me how can i use stored procedure's o/p to create
> cursor?
> i'm using sql 2000 and .net 2.0
> thanks,
> Lucky|||Stu wrote:
> First, try to rewrite your app so you don't use cursors.

Second, Seriously. Try to rewrite your app so you don't use cursors.

You might also consider dropping the guts of your stored procedure into
a User Defined Function that returns a table. Then you can use that
UDF for both the Stored Procedure and your sketchy thing that uses
Cursors.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/|||Hi ,
Here i'm pasting the sql code that i want to run. the code is ment to
fetch datbase list and update each database with some specific business
logic.

DECLARE authors_cursor CURSOR FOR
SELECT sp_databases

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @.Database_name, @.database_size, @.Remarks

WHILE @.@.FETCH_STATUS = 0
BEGIN

print 'database : ' + @.Database_name
print 'some business logic'

FETCH NEXT FROM authors_cursor
INTO @.au_id, @.au_fname, @.au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

NOTE:

please notice the use of procedure to get list of the database in the
select statement of the DECLARING CURSOR. i want to use stored
procedure's o/p to iterate through the rows returned by the procedure.

Please let me know if you know how to do this.

thanks

Madhivanan wrote:
> Post your exact requirement. There can be better method of what you are
> trying to do now
> Madhivanan
>
> Lucky wrote:
> > Hi guys!
> > i want to create one cursor in the t-sql. the problem is i want to use
> > stored procedure instead of select command in cursor.
> > can anyone tell me how can i use stored procedure's o/p to create
> > cursor?
> > i'm using sql 2000 and .net 2.0
> > thanks,
> > Lucky|||Lucky (tushar.n.patel@.gmail.com) writes:
> Here i'm pasting the sql code that i want to run. the code is ment to
> fetch datbase list and update each database with some specific business
> logic.
> DECLARE authors_cursor CURSOR FOR
> SELECT sp_databases

There is no table?

> OPEN authors_cursor
> FETCH NEXT FROM authors_cursor
> INTO @.Database_name, @.database_size, @.Remarks
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print 'database : ' + @.Database_name
> print 'some business logic'
>
> FETCH NEXT FROM authors_cursor
> INTO @.au_id, @.au_fname, @.au_lname
> END
> CLOSE authors_cursor
> DEALLOCATE authors_cursor
> NOTE:
> please notice the use of procedure to get list of the database in the
> select statement of the DECLARING CURSOR. i want to use stored
> procedure's o/p to iterate through the rows returned by the procedure.

What does "o/p" mean?

It would be interesting to know what "some business logic" contains.

It's possible that you could use sp_MSforeachdb:

EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'

This procedure is undocumented and not supported from Microsoft, so
you would have to look into the source code for the gory details on
how it works. But basically it iterates over all databases, and
run as the SQL statement once for each database. ? works as placeholder
for the database name.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yeah there is no table. its just a database list that i need. and the
business logic is very very big to paste here. i need some different
tables in different DB and the idea of using the foreachloop is
interesting but i dont know wether it will work with more than 250
lines of PL SQL code.

the o/p mean OUTPUT.

let me know if you want to know anything else.

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > Here i'm pasting the sql code that i want to run. the code is ment to
> > fetch datbase list and update each database with some specific business
> > logic.
> > DECLARE authors_cursor CURSOR FOR
> > SELECT sp_databases
> There is no table?
> > OPEN authors_cursor
> > FETCH NEXT FROM authors_cursor
> > INTO @.Database_name, @.database_size, @.Remarks
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > print 'database : ' + @.Database_name
> > print 'some business logic'
> > FETCH NEXT FROM authors_cursor
> > INTO @.au_id, @.au_fname, @.au_lname
> > END
> > CLOSE authors_cursor
> > DEALLOCATE authors_cursor
> > NOTE:
> > please notice the use of procedure to get list of the database in the
> > select statement of the DECLARING CURSOR. i want to use stored
> > procedure's o/p to iterate through the rows returned by the procedure.
> What does "o/p" mean?
> It would be interesting to know what "some business logic" contains.
> It's possible that you could use sp_MSforeachdb:
> EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'
> This procedure is undocumented and not supported from Microsoft, so
> you would have to look into the source code for the gory details on
> how it works. But basically it iterates over all databases, and
> run as the SQL statement once for each database. ? works as placeholder
> for the database name.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lucky (tushar.n.patel@.gmail.com) writes:
> yeah there is no table. its just a database list that i need.

So how does the actual cursor declaration look like? The code you
posted was incorrect, as it referred to a non-existing column. It's
very difficult to assist when I don't really know what you are trying
to do.

> and the business logic is very very big to paste here. i need some
> different tables in different DB and the idea of using the foreachloop
> is interesting but i dont know wether it will work with more than 250
> lines of PL SQL code.

PL/SQL? What are you using? MS SQL Server or Oracle?

To me it sounds very funny of wanting to run 250 lines of business logic
in multiple databases. I can envision situations where this may be
necessary, but I can also see this as a result of a poor design.

If you explained what your are actually trying to achieve in business
terms, it may be easier to suggest a good solution.

For a general discussion on multiple databases, this section in my
article on dynamic SQL may give some ideas:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm quite dissappointed with your Questions. what i wanted to do so far
is to use output of the stored procedure in the select statement of the
Declaring CURSOR. but you have diverted the conversation on the
different track.

-- first i clearly said in my first post that i'm using MS SQL Server
2000 and .NET 2.0

-- for your convinience i gave you expamle of declaring cursor that i
had copied form the ms sql help but instead of understanding the
problem you complained about the syntaxt though that example was for to
understand the problem but you missed the target.

-- PL SQL is of course in Oracle to write some custom business logic.
the same way you can do in SQL Server the name used in here is T-SQL.
it shouldn't be hard for you to understand.

-- As far as i know, nobody ever asked me what kind of business logic i
want to use. we always disscus problems here and asked for the
solution.

-- what kind of businees logic i'm using and why i'm using and what
should be the size of the logic. these all depends on the requirements
and the scererios. i didn't ask your opinion on that.

We have streached the conversation to far and i dont want to continue
it further more.

thanks for nothing. and by the way i found what i was looking for.

Lucky

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > yeah there is no table. its just a database list that i need.
> So how does the actual cursor declaration look like? The code you
> posted was incorrect, as it referred to a non-existing column. It's
> very difficult to assist when I don't really know what you are trying
> to do.
> > and the business logic is very very big to paste here. i need some
> > different tables in different DB and the idea of using the foreachloop
> > is interesting but i dont know wether it will work with more than 250
> > lines of PL SQL code.
> PL/SQL? What are you using? MS SQL Server or Oracle?
> To me it sounds very funny of wanting to run 250 lines of business logic
> in multiple databases. I can envision situations where this may be
> necessary, but I can also see this as a result of a poor design.
> If you explained what your are actually trying to achieve in business
> terms, it may be easier to suggest a good solution.
> For a general discussion on multiple databases, this section in my
> article on dynamic SQL may give some ideas:
> http://www.sommarskog.se/dynamic_sql.html#Dyn_DB.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lucky (tushar.n.patel@.gmail.com) writes:
> I'm quite dissappointed with your Questions. what i wanted to do so far
> is to use output of the stored procedure in the select statement of the
> Declaring CURSOR. but you have diverted the conversation on the
> different track.

Yes, I want to help you to solve the real problem.

I've been following technical newsgroups on Usenet for many years, and I
early made the observation that when people asked "funny questions" was
that they were trying to get from A to B, but instead they were asking
of how to get from C ro D, because they the way from A to C and from
D to B and now they were standing at deep ravine and not being able to
cross. While there in fact there was a straight motorway from A to B,
which was easy to point to, once the real problem had been uncovered.

> -- for your convinience i gave you expamle of declaring cursor that i
> had copied form the ms sql help but instead of understanding the
> problem you complained about the syntaxt though that example was for to
> understand the problem but you missed the target.

I'm afraid that those are the rules. If you cannot make yourself clear
what you are asking for, then you will not get very good answers. I'm
sorry, but while I'm good at SQL, I am not good reading other people's
thoughts.

> -- PL SQL is of course in Oracle to write some custom business logic.
> the same way you can do in SQL Server the name used in here is T-SQL.
> it shouldn't be hard for you to understand.

It happens frequently enough that people who use Oracle, MySQL or some
other engine post to this newsgroup, that I felt obliged to rule out this
possibility.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i didn't get you. what do you mean by make myself clear? didn't i gave
you the example? check my post before. and i clearly said what i wanted
to know. in the same post. but instead of targeting the problem you
said there is a syntaxt mistake in the code.

The problem is quite simple to understand. and tha is HOW TO USE OUTPUT
OF THE PROCEDURE TO CREATE CURSOR.

is it very hard to understand? i didnt know the syntaxt and all i
wanted to know was the syntaxt.

but instead telling me that, you asked me what kind of business logic i
want to use. do it really matter to know how the cursor can be created
from the output of the procedure?

and if you are member of the group for years than you should at least
be experienced by now to understand what one is asking.

as far as i know. the example i've posted was of MS SQL SERVER wasn't
from Oracle. but you cared to know wether i want to use PL/SQL or
T-SQL? i didn't asked to optimize some code.

ALL I ASKED IS JUST ONE DEFINATION OF CREATING CURSOR.

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > I'm quite dissappointed with your Questions. what i wanted to do so far
> > is to use output of the stored procedure in the select statement of the
> > Declaring CURSOR. but you have diverted the conversation on the
> > different track.
> Yes, I want to help you to solve the real problem.
> I've been following technical newsgroups on Usenet for many years, and I
> early made the observation that when people asked "funny questions" was
> that they were trying to get from A to B, but instead they were asking
> of how to get from C ro D, because they the way from A to C and from
> D to B and now they were standing at deep ravine and not being able to
> cross. While there in fact there was a straight motorway from A to B,
> which was easy to point to, once the real problem had been uncovered.
> > -- for your convinience i gave you expamle of declaring cursor that i
> > had copied form the ms sql help but instead of understanding the
> > problem you complained about the syntaxt though that example was for to
> > understand the problem but you missed the target.
> I'm afraid that those are the rules. If you cannot make yourself clear
> what you are asking for, then you will not get very good answers. I'm
> sorry, but while I'm good at SQL, I am not good reading other people's
> thoughts.
> > -- PL SQL is of course in Oracle to write some custom business logic.
> > the same way you can do in SQL Server the name used in here is T-SQL.
> > it shouldn't be hard for you to understand.
> It happens frequently enough that people who use Oracle, MySQL or some
> other engine post to this newsgroup, that I felt obliged to rule out this
> possibility.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lucky (tushar.n.patel@.gmail.com) writes:
> i didn't get you. what do you mean by make myself clear?

That I did not understand what you was looking for. And I am sorry,
to that end I am the sole judge. You may know what you were looking
for, but that does not mean that you manage to convey that message.

> The problem is quite simple to understand. and tha is HOW TO USE OUTPUT
> OF THE PROCEDURE TO CREATE CURSOR.

And that is a such a strange thing to, thar there is all reason to ask
what you want really want to do. In fact, any question that involves a
cursor will be met with the suspicion that the cursor may not be needed.

But there is also one more reason to ask what you really want to do:
there may be several options, and which is the best one, depends on
your actual business problem.

Finally, please remember that on Usenet you never get less help than
you pay for.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm agree with you on avoiding CURSORs and i always welcome suggestions
on doing things other ways.
the foreach loop you suggested me was a good suggestion. i already
tried that that to avoid cursor but the problem was with the bunch of
line to modify tables,procedures,views and all i wanted to do is to
write some logic to update all database rather than doing it manually.

i found the way out and i did it.

but you know when you are on job you have immense pressure on you and
that time you can't wait to explain everything. if it would be
something that i needed for more then 1 time then i would have
discussed the problem in more detail and of course also might welcomed
your suggestions.

i trully appriciate the help i get from groups and that is why i always
prefer groups then tutorials and books. Learning from others experience
is always better then anything.

Erland Sommarskog wrote:
> Lucky (tushar.n.patel@.gmail.com) writes:
> > i didn't get you. what do you mean by make myself clear?
> That I did not understand what you was looking for. And I am sorry,
> to that end I am the sole judge. You may know what you were looking
> for, but that does not mean that you manage to convey that message.
> > The problem is quite simple to understand. and tha is HOW TO USE OUTPUT
> > OF THE PROCEDURE TO CREATE CURSOR.
> And that is a such a strange thing to, thar there is all reason to ask
> what you want really want to do. In fact, any question that involves a
> cursor will be met with the suspicion that the cursor may not be needed.
> But there is also one more reason to ask what you really want to do:
> there may be several options, and which is the best one, depends on
> your actual business problem.
> Finally, please remember that on Usenet you never get less help than
> you pay for.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx