Creating an "ALL" Parameter Value

I am trying to create an "All" parameter. I created a stored procedure that says:

Code Snippet

CREATE PROCEDURE dbo.Testing123

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD
Order by ID
GO

Then I createded a report parameter and set the default to All

I also created a filter that sets the textbox vaule to the report parameter.

In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?

In the query that returns the data for the report, I suspect you're doing something like:

WHERE id = @.ID

What you would need to do is:

WHERE id = @.ID OR @.ID IS NULL

I think that you should be setting your default value to NULL instead of "ALL", that would remove the need for your filter.

|||

Is this what you mean?

Code Snippet

CREATE PROCEDURE dbo.Testing123

@.id char

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD

Where ID = @.id or @.id is NULL

Order by ID

Should this make it work?

I am also getting this error: "The report parameter 'pid' has a DefaultValue or ValidValue that depends on the report parameter "pid" Forward dependencies are not valid."

|||

No, I meant for you to put it in the query that is returning the data for your report, not for the parameter.

I am assuming that what you have currently is a parameter as a drop down box where they select the id from the list generated by the query that you have posted above. Then, the user presses "view Report" and a report for that id is executed with data filled in by some other query, that currently has

Where ID = @.id

to return just the data for the id you have selected.

If you add

or @.id is NULL

to the where clause, it will detect if the user has selected ALL and not filter the results.

Maybe I have misunderstood what you're trying to do?

|||You were right! Thank you so much for your help with this

Creating A View in a Stored Procedure

Hi Everyone

Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

CREATE PROC upProcName AS

DECLARE @.Variable varchar(50)

CREATE VIEW vwName AS

SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @.Variable

GO

Any Thoughts ideas would be great

Cheersyou can not create a view in a stored procedure.
Create view should be the first statement of the batch.|||I thought that may be the case. Is there a way to change the view or pass it parameters?|||I thought that may be the case. Is there a way to change the view or pass it parameters?|||Execute a sp_dboption YourDB, "DDL in tran", true|||why would you want to create a view within a store procedure??

I alway thought of views are used for
1. quick query
2. row level permission control

in what other situations would a view be used for?? for the reasons above i don't see why you would need to dynamically create a view.

cheers
James :)|||What about this one?

drop view test2
drop proc test
go
create proc test
as
exec('create view test2 as select getdate() as now')
select * from test2|||Originally posted by snail
What about this one?

drop view test2
drop proc test
go
create proc test
as
exec('create view test2 as select getdate() as now')
select * from test2

YUP!

But I like the question of why you need to do this though?

Creating a View from a Stored procedure

Hello

Newbie here.

Is there a way of creating a VIEW...using a stored procedure. I am
basically trying to create a view to return some data that I am
getting using a stored procedure.

I have created the procedure and when I execute this its working ok.
The stored procedure uses a datefrom and dateTo which I have set up by
tweaking the getdate() and getdate()-2.

In other words can you create a view like this

CREATE VIEW view_test
AS
exec proc_test
GO

Any help will be greatly appreciated.

RemmyA view is a single SELECT statement. If you can write the stored
procedure as a single SELECT you can make that SELECT into a view.

Otherwise, you might look into Table Valued Functions.

Roy Harvey
Beacon Falls, CT

On Wed, 19 Sep 2007 14:43:04 -0000, apothecary <wamweri@.gmail.com>
wrote:

Quote:

Originally Posted by

>Hello
>
>Newbie here.
>
>Is there a way of creating a VIEW...using a stored procedure. I am
>basically trying to create a view to return some data that I am
>getting using a stored procedure.
>
>I have created the procedure and when I execute this its working ok.
>The stored procedure uses a datefrom and dateTo which I have set up by
>tweaking the getdate() and getdate()-2.
>
>In other words can you create a view like this
>
>CREATE VIEW view_test
>AS
>exec proc_test
>GO
>
>Any help will be greatly appreciated.
>
>Remmy

sql

Creating a VB.net event handler for a Stored Procedure

Is it possible to create an event handler in a VB.net application to run whenever a Stored Procedure is run.

My application has a scheduled task which is created and scheduled by users of the application, whenever this scheduled task is run I would like it to contact the application to kick off a sequence of tasks. I would appreciate if anybody could point me in the right direction.You can script a custom trace that captures the execution of this task that can instantiate a COM object which in turn can do whatever you want it to do.|||I'm new to developement is there anywhere I can learn to do this.|||I don't know of any way to call code in a VB app running on another machine, but you can create a simple COM object and then use sp_OACreate (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp) and the related procedures to launch your COM object on the server.

Will this do what you want?

-PatP|||There both running on the same machine so with a bit of luck I'll be able to get it working.

Thanks

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATEPROCEDURE test

AS

BEGIN

print'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATE PROCEDURE test

AS

BEGIN

print 'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Hello -
I'm hoping some T-SQL expert could help me figure out the code
necessary to implement a stored procedure that will truncate and
shrink the log files when run.
Let me give you a little background first. We are a small company
with an adequate, but not overwhelming amount of disk space. But,
with running SQL Server 2000 we do like the Recovery model set to
Full. Each week on back to back evenings (and on separate tapes) we
do a full backup with incremental backups throughout the week. After
the 1st full backup, I would like to run a procedure that would look
at each database and then truncate & shrink the log file. Then for
the 2nd full backup, I'd have much smaller logs obviously with the
ability to recover back to my original(s) via my 1st backup.
The code that works on individual databases that I now run manually
and change the parameters (@.database) accordingly is:
use master
BEGIN
BACKUP LOG @.database WITH TRUNCATE_ONLY
GO
USE @.database
DBCC SHRINKFILE (@.database + '_log')
I would like to have this placed in a stored procedure that would go
through each user database automatically. Unfortunately, I am not at
all familiar with the nuances of automatically changing databases nor
with working with stored procedures.
If anyone would/could be so kind as to provide me with the script that
would allow this to work as desired, I would be very very
appreciative. Thanks in advance for any and all help!
RichScull,
I am a little confused as to what your actually doing. You say you do a
FULL backup and then incremental but do not specify log backups at all other
than the truncate only. If you don't actually do log backups why do you
want to be in FULL recovery mode? What good does shrinking the logs do
anyway? When you backup a log (or a database) the size of the backup file
is proportional to the amount of data in the file not the size of the file
itself. So shrinking a log file will not give smaller backups. Shrinking
it and of itself is a dangerous act anyway for the reasons you mention. If
you have limited space and you shrink the file, which will certainly grow
back again, what will happen when you need more space and don't have it? It
is better to allocate the space in need (now and in the future) up front and
don't ever shrink the files. That way you ensure you always have the room
when you need it. If I misinterpreted what your doing please let me know so
we can work thru this.
Andrew J. Kelly
SQL Server MVP
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Scull
Perhaps you want to use WITH INIT (after full backup database) to ovewrite
the log file.
Also,look at INFORMATION_SCHEMA.SCHEMATA in BOL.
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||First off, I want to thank you for your input- it's geratly appreciated!
I'm sorry I wasn't clear in terms of how we perform our backups. We use
a separate backup application for backing up all the SQL Server
databases and their respective transaction logs. We do FULL backups on
Friday and Saturday evening, and incremental backups throughout the
week.
My space issues arose when I converted all of my 7.0 databases to 2000
about 4 months ago. Rather quickly after converting the db's, I noticed
we were running out of disk space. In reviewing one of the converted
databases that was affecting my disk space - I noticed the MDF file with
a size of about 1 GIG and the transaction log with a size of 17 GIG.
Obviously, I was like "Yikes."
Anyway, at this point in time I'd would rather keep the recovery mode
set to full so that we could perform point-in-time recoveries. I figure
the best way, given my disk space concerns, for me to insure possible
point-in-time recovery is to take advantage of the fact that 2 full
backups are done each week. If after the 1st full backup, I can
truncate and shrink the transaction log before the 2nd full backup - I
will have a choice of restore points in the event something should go
awry.
Now while I know this is not necessarily the optimal solution, I do not
know of a better one at this point. I am obviously wide open to
suggestions/recommendations.
If no strategic process changes can be realized, I think I need to find
the answer to my T-SQL question concerning establishing a stored
procedure that I can schedule after the Friday backup but before the
Saturday backup - that will truncate and shrink the transaction log(s)
for each of the user databases automatically.
I will also check out the WITH INIT, etc. in the BOL.
Thanks in advance for any more feedback.
Rich
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Rich,
Your solution is to issue regular Log backups and forget about truncating
and shrinking the logs. If you want point in time recovery you have to be
in FULL mode. If your in FULL mode the log file will keep growing and
growing since it needs a log backup to properly backup the committed trans
and truncate them. If you don't issue log backups what will happen if your
disk array dies and you must restore from scratch? You will only have up to
your last full and incremental backup. You have lost your ability to
restore up to the minute or point in time. If you issue regular log backups
your tran log itself will stay at a size that it needs over a point it time
and should never be shrunk once it reaches that quiescent state. Of coarse
you log backup files will now contain the data that used to be in your log
so you need a place to store those and hopefully it's not on the same array
as the db. That should solve your disk space issue and alleviate the need
to schedule a log truncation and shrinking. Just schedule regular log
backups. The interval is up to you. Every 15 minutes or 1/2 hour should
keep your log file from growing too much and keep you relatively safe as
well. If your concerned about room to place your backup files you might
consider using a product called SQL LiteSpeed. It will reduce the time for
a backup and give you much smaller files to boot.
Andrew J. Kelly
SQL Server MVP
"Rich S" <myscullyfamily@.yahoo.com> wrote in message
news:O1Kiq8d8DHA.3112@.tk2msftngp13.phx.gbl...
> First off, I want to thank you for your input- it's geratly appreciated!
> I'm sorry I wasn't clear in terms of how we perform our backups. We use
> a separate backup application for backing up all the SQL Server
> databases and their respective transaction logs. We do FULL backups on
> Friday and Saturday evening, and incremental backups throughout the
> week.
> My space issues arose when I converted all of my 7.0 databases to 2000
> about 4 months ago. Rather quickly after converting the db's, I noticed
> we were running out of disk space. In reviewing one of the converted
> databases that was affecting my disk space - I noticed the MDF file with
> a size of about 1 GIG and the transaction log with a size of 17 GIG.
> Obviously, I was like "Yikes."
> Anyway, at this point in time I'd would rather keep the recovery mode
> set to full so that we could perform point-in-time recoveries. I figure
> the best way, given my disk space concerns, for me to insure possible
> point-in-time recovery is to take advantage of the fact that 2 full
> backups are done each week. If after the 1st full backup, I can
> truncate and shrink the transaction log before the 2nd full backup - I
> will have a choice of restore points in the event something should go
> awry.
> Now while I know this is not necessarily the optimal solution, I do not
> know of a better one at this point. I am obviously wide open to
> suggestions/recommendations.
> If no strategic process changes can be realized, I think I need to find
> the answer to my T-SQL question concerning establishing a stored
> procedure that I can schedule after the Friday backup but before the
> Saturday backup - that will truncate and shrink the transaction log(s)
> for each of the user databases automatically.
> I will also check out the WITH INIT, etc. in the BOL.
> Thanks in advance for any more feedback.
> Rich
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Rich
I suggest you setup a SQL 2000 Agent Job executing the following TSQL
command:
DBCC SHRINKFILE(2, TRUNCATEONLY)
on the DB(s) in question. Set it to run off hours, the "2" should be
for the
Log(LDF) file, a "1" should be the for MDF file.
I'm still waiting myself to see a script that will parse thru the DBs
on a server and build the DBCC above for every DB.
good luck
Steven|||You could do it vb6 or .net using
Public oSQLServer As New SQLDMO.SQLServer
For Each oDatabase In oSQLServer.Databases
'If odatabase.Status <> SQLOLEDBStat_Inaccessible Then
form1.List_Databases.AddItem oDatabase.Name
'frmExecVB.lstCat.AddItem oDatabase.Name
'End If
Next oDatabase
Then you can build up a string using filesystem object to write your
script for you.
Have a look at SQLDMO Object, it rocks !
I've even got some code to look round your network and come back with a
list of sql servers...
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Thanks for your reply. I have still been working on a solution and have
not had the opportunity to write back. I work for a relatively small
company where I were a number of 'hats' of which being the DBA is just
one of them.
Anyway, I setup transaction log backups hourly throughout the day (I
don't have that many transactions - but do have a lot of databases) and
that is working fine. In tracking the transaction log size, I was able
to summize that it grew immensely on late Saturday evenings - which is
the time I kick off my database maintenance plan which includes
rebuilding indexes and optimizing the databases.
For example, one particular database who MDF is 540 mb and had a
transaction log of 52 mb throughout the week - had it's transaction log
grow to 468 mb after running the maintenance plan.
I am thinking that I should then 1) backup the transaction log with
truncate only, then 2) run the SHRINKFILE command to reduce the
transaction log to say... 50 mb. 3) Then backup the entire database.
If that works, I would need to incorporate that into all of my db plans.
Any thoughts/concerns? Thanks again for everyone's assitance!!!
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||try the following to truncate TLogs:
--drop procedure xxx_SQL_Logspace_truncate_all
--CREATE procedure xxx_SQL_Logspace_truncate_all as
USE Master
DECLARE @.logname varchar(20)
DECLARE @.query varchar(255)
DECLARE LogList_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
ORDER BY name
OPEN LogList_cursor
FETCH NEXT FROM LogList_cursor INTO @.logname
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- add DB's you want to skip to the following list
IF RTRIM(@.logname) = "master" or
RTRIM(@.logname) = "model" or
RTRIM(@.logname) = "msdb" or
RTRIM(@.logname) = "pubs" or
RTRIM(@.logname) = "Northwind"
print @.logname + ' skipped'
ELSE
begin
Set @.query = "DBCC SHRINKFILE("+ RTRIM(@.logname) + ".LDF, TRUNCATEONLY)"
select @.query
end
FETCH NEXT FROM LogList_cursor into @.logname
END
-- following will list all DB's Logspace stats
DBCC SQLPERF(LOGSPACE)
CLOSE LogList_cursor
DEALLOCATE LogList_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Hello -
I'm hoping some T-SQL expert could help me figure out the code
necessary to implement a stored procedure that will truncate and
shrink the log files when run.
Let me give you a little background first. We are a small company
with an adequate, but not overwhelming amount of disk space. But,
with running SQL Server 2000 we do like the Recovery model set to
Full. Each week on back to back evenings (and on separate tapes) we
do a full backup with incremental backups throughout the week. After
the 1st full backup, I would like to run a procedure that would look
at each database and then truncate & shrink the log file. Then for
the 2nd full backup, I'd have much smaller logs obviously with the
ability to recover back to my original(s) via my 1st backup.
The code that works on individual databases that I now run manually
and change the parameters (@.database) accordingly is:
use master
BEGIN
BACKUP LOG @.database WITH TRUNCATE_ONLY
GO
USE @.database
DBCC SHRINKFILE (@.database + '_log')
I would like to have this placed in a stored procedure that would go
through each user database automatically. Unfortunately, I am not at
all familiar with the nuances of automatically changing databases nor
with working with stored procedures.
If anyone would/could be so kind as to provide me with the script that
would allow this to work as desired, I would be very very
appreciative. Thanks in advance for any and all help!
RichScull,
I am a little confused as to what your actually doing. You say you do a
FULL backup and then incremental but do not specify log backups at all other
than the truncate only. If you don't actually do log backups why do you
want to be in FULL recovery mode? What good does shrinking the logs do
anyway? When you backup a log (or a database) the size of the backup file
is proportional to the amount of data in the file not the size of the file
itself. So shrinking a log file will not give smaller backups. Shrinking
it and of itself is a dangerous act anyway for the reasons you mention. If
you have limited space and you shrink the file, which will certainly grow
back again, what will happen when you need more space and don't have it? It
is better to allocate the space in need (now and in the future) up front and
don't ever shrink the files. That way you ensure you always have the room
when you need it. If I misinterpreted what your doing please let me know so
we can work thru this.
--
Andrew J. Kelly
SQL Server MVP
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Scull
Perhaps you want to use WITH INIT (after full backup database) to ovewrite
the log file.
Also,look at INFORMATION_SCHEMA.SCHEMATA in BOL.
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Rich
I suggest you setup a SQL 2000 Agent Job executing the following TSQL
command:
DBCC SHRINKFILE(2, TRUNCATEONLY)
on the DB(s) in question. Set it to run off hours, the "2" should be
for the
Log(LDF) file, a "1" should be the for MDF file.
I'm still waiting myself to see a script that will parse thru the DBs
on a server and build the DBCC above for every DB.
good luck
Steven

Creating a table inside a stored procedure

I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.Is that like the type of sex you want...is this mail order?

Anyway, you need to take out the GO...that's a scope terminator

And are you sure you want a GLOBAL Temp table instead of a local one?

Try this

CREATE PROCEDURE [dbo].[Test2]
AS
BEGIN
CREATE TABLE ##OPTIONSEX (
OPTION_PLAN VARCHAR(50)
, TOT_OPTIONS_EXCHANGED FLOAT NULL
)

INSERT ##OPTIONSEX
SELECT B.COMPONENT
, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A
INNER JOIN TBLCOMPONENT B
ON B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

DROP TABLE ##OPTIONSEX
GO|||Thanks for the reply. Worked fine only after I removed the BEGIN line. For some reason using BEGIN returned a syntax error.

Thanks again Brett.|||dooooooh

I forgot the END

BEGIN
.....some code
END

And you'll need those constructs if you do an control of flow logic

IF some condition
BEGIN
.........some code line 1
.........some code line 2
END

WHILE some Cond
BEGIN
.........some code line 1
.........some code line 2
END

Good luck

Creating a table from .NET

I need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @.tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.

Here is my stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@.tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END

What went wrong?

Thank you.

DECLARE @.SQL VARCHAR(500)

SET @.SQL = 'CREATE TABLE ' + @.TableName + ' (bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)'

EXEC @.Sql

-- It should be noted that you SHOULD SANITIZE your parameter and make sure that a user does not put anything non-alphanumeric and _ because a malicious user could potentially execute an sql injection if you did not.

If you search for sp_execsql I believe you will find tons of postings.

|||

To be honest, I wouldn't expect the SProc you've listed to work the way you've described in SQL Server either. What you're looking for is dynamic SQL, which has been discussed a number of times in the past weeks in this forum. I suggest you read the information at http://www.sommarskog.se/dynamic_sql.html before you proceed to ensure that you understand the security implications of using SPs with dynamic SQL before implement it.

If the possibility of SQL Injection is not an issue for you, or you've figure out how to mediate it, that same document also has some recomendations on possible implementations.

Mike

|||Thank you both, marcD and Mike.|||Hi,

you should better use the SMO classes which expose an interface (.NET API) for a developer to manage SQL Server objects. You don′t need to care about syntax or semantics, as SMO is object oriented and can be easily used within C# and Visual Studio (with Intellisense) to produce a SQL-injectionfree code (if used the right way :-) ) The API is the successor of the DMO classes, formerly used in SQL Server 2000 and below.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
>[StartDate] [datetime] NULL ,
>[EndDate] [datetime] NULL ,
>[Quantity] [int] NULL ,
>[Bar_Code] [varchar] (10) NULL ,
>[Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))
|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
sql

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row an
d
updating the Comment field with the reasons why test failed where appropriat
e.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update commen
t
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:

>Hi all,
>I need to perform a validation test on fields in stored data for each row a
nd
>updating the Comment field with the reasons why test failed where appropria
te.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comme
nt
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
<
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>

Creating a string - Cursor or Case

Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment = CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
--
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via SQLMonster.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via SQLMonster.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment => CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Creating a Stored Procedure with a Stored Procedure

Hi all,
I was wonder if any one knows if its possible to create run and drop
a stored procedure from within another stored procedure. You see i want
to have a table which has serveral stored procedures stored in it as
strings then use a stored procedure to extract one of these and create
a stored procedure with it, run it and drop it when its done. Is this
possible, and if so how bad a performance can i expect (i'm expecting
pretty bad).I think you can do this. but you can't create sps in other db from another
dbs stored procedure|||Hi DigitalDiamond ,
Prehaps this link solve your problem
http://blog.csdn.net/lihonggen0/arc...8/15/75447.aspx
with warm regards
Jatinder Singh|||Think about the reasons for trying to achieve something like this. Generally
there are several better ways to do it.
1) create all needed procedures in advance, then use the first procedure to
select the appropriate one;
2) use the first procedure to dynamically build a query to execute;
3) if you're trying to hide your code from the rest of the database users,
use encryption.
What exactly are you trying to do?
ML|||On 26 Jul 2005 04:35:12 -0700, DigitalDiamond wrote:

>Hi all,
> I was wonder if any one knows if its possible to create run and drop
>a stored procedure from within another stored procedure. You see i want
>to have a table which has serveral stored procedures stored in it as
>strings then use a stored procedure to extract one of these and create
>a stored procedure with it, run it and drop it when its done. Is this
>possible, and if so how bad a performance can i expect (i'm expecting
>pretty bad).
Hi DigitalDiamond,
Why would you even *want* to do this? Why not just create all the stored
procedures, then execute the one you need?
Your idea is terribly bad. Not because of performance (that would
probably not be as bad as you expect), but because of security. It is
like going on vacation and leaving the door of your house not only
unlocked, but even wide open. And placing a big sign in the yard: "Hey,
burglars - the door is open and I'm away until the end of the month, so
please help yoourself!"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sure, you can always write horrible code in nay language! Have you
ever had a course in basic Software Engineering? Please take one. Pay
attentation to the idea of a module of code, coupling and cohesion.|||You can. You can create temporary stored procedures too. Should you?
Probably not. A better solution is to use sp_executesql to execute dynamic
queries. sp_executesql accepts both input and output parameters (see
kb:262499). It also doesn't mess around with the system tables.
"DigitalDiamond" <chris.diamond@.ncumbria.nhs.uk> wrote in message
news:1122377712.694590.77220@.g47g2000cwa.googlegroups.com...
> Hi all,
> I was wonder if any one knows if its possible to create run and drop
> a stored procedure from within another stored procedure. You see i want
> to have a table which has serveral stored procedures stored in it as
> strings then use a stored procedure to extract one of these and create
> a stored procedure with it, run it and drop it when its done. Is this
> possible, and if so how bad a performance can i expect (i'm expecting
> pretty bad).
>|||It doesnt matter now i found a better way of doing it via restructing
the database set up i had. And also i have a degree in software
enginerring Celko and if you want to be a arse dont do it of groups
designed to help people|||Why would anyone allow 100,000 lines of codes but not 1000 stored
procedures? Having a good practice in place can help manage and
maintain the systems. It would be hard to manage something that
virtually does not exist!!!
Perhaps there might be programs that would drop other programs on the
fly out there. To the extent that without any trail and therefore,
without our knowledge. This makes things really complicated.

Creating a stored procedure to insert data?

Hello all,

I am having a lot of trouble with stored procedures. Could anyone help me out.

I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today.

I can select the values, and I can insert the values.

But how do I store the values so that i can pass the results of the select to the insert?

Im also having a lot of trouble with storing date values.

ANy help would be greatly appreciated.

Regards,

Padraic Hickey

if exists (select * from dbo.sysobjects where id = object_id(N'[table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table2]
SELECT *
INTO table2
FROM table1
WHERE convert(varchar,table1.meetingdate,112)=convert(varchar,@.mydate,112)
|||

Cheers Motley,

I was having a lot of trouble with that one.

If ever i can return the favour.

Padraic

Creating a Stored Procedure that will summarize data in a table into a table reflecting period d

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours

Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40

Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20

Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35

Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40

Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40

Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year

Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007

Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007

Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007

Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008

Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,

Mike Misera

Check out the PIVOT operator

|||

Mike, check out this sample script. You would need a case statement for each month, this is just using one for each month supplied in the example.

Code Snippet

CREATE TABLE #currentTable (

Project NVARCHAR(10),

Task NVARcHAR(10),

Category NVARcHAR(10),

FiscalYear INT,

FiscalMonth INT,

TotalHours INT

)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 1', 2007, 01, 40)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 2', 2007, 02, 20)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 3', 2007, 03, 35)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 1', 2008, 01, 40)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 2', 2008, 02, 40)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 3', 2008, 03, 40)

SELECT Project, Task, Category, FiscalYear,

SUM(CASE FiscalMonth WHEN 1 THEN TotalHours ELSE 0 END) AS '01',

SUM(CASE FiscalMonth WHEN 2 THEN TotalHours ELSE 0 END) AS '02',

SUM(CASE FiscalMonth WHEN 3 THEN TotalHours ELSE 0 END) AS '03'

FROM #currentTable

GROUP BY Project, FiscalYear, Task, Caetegory

DROP TABLE #currentTable

|||

ShawnNSF,

When I run this statement the results in query analyzer it pulls back the results that I want to see on the bottom of the results pane based off of the the Selection statement looking into X_PJLABDIS(view).

My problem with this script is the INSERT step. It adds the values in the parantheses and I need it to add the values returned from the SELECT Statement below that.

Mike

CREATE TABLE PIVOT (

Project NVARCHAR(10),
pjt_entity NVARcHAR(10),
acct NVARcHAR(10),
Fiscalno_year INT,
Fiscalno_month INT,
total_hrs INT

)

INSERT INTO PIVOT VALUES ('Project', 'Pjt_entity', 'acct', 2007, 01, 40)

SELECT Project, pjt_entity, acct, Fiscalno_year,

SUM(CASE Fiscalno_month WHEN 1 THEN total_hrs ELSE 0 END) AS 'Month 01',
SUM(CASE Fiscalno_month WHEN 2 THEN total_hrs ELSE 0 END) AS 'Month 02',
SUM(CASE Fiscalno_month WHEN 3 THEN total_hrs ELSE 0 END) AS 'Month 03',
SUM(CASE Fiscalno_month WHEN 4 THEN total_hrs ELSE 0 END) AS 'Month 04',
SUM(CASE Fiscalno_month WHEN 5 THEN total_hrs ELSE 0 END) AS 'Month 05',
SUM(CASE Fiscalno_month WHEN 6 THEN total_hrs ELSE 0 END) AS 'Month 06',
SUM(CASE Fiscalno_month WHEN 7 THEN total_hrs ELSE 0 END) AS 'Month 07',
SUM(CASE Fiscalno_month WHEN 8 THEN total_hrs ELSE 0 END) AS 'Month 08',
SUM(CASE Fiscalno_month WHEN 9 THEN total_hrs ELSE 0 END) AS 'Month 09',
SUM(CASE Fiscalno_month WHEN 10 THEN total_hrs ELSE 0 END) AS 'Month 10',
SUM(CASE Fiscalno_month WHEN 11 THEN total_hrs ELSE 0 END) AS 'Month 11',
SUM(CASE Fiscalno_month WHEN 12 THEN total_hrs ELSE 0 END) AS 'Month 12'

FROM X_PJLABDIS

GROUP BY Project, Fiscalno_year, pjt_entity, acct

|||

Mike,

Those values are just for example. Do not use that part of the script. You seem to have gotten the idea by modifying the select statement (the part that was for you). I should have explained a little better that the other part of my snippet was just me building test data to show you the example query.

You can just run a query with the same idea as what I posted against the table you have with the data already populated in it.

|||

Shawn,

As you can probably tell right now, I am have very limited knowledge of and using pivot tables and stored procedures. As you said I am going down the right path with this code.

How would I go about achieving returning the results into a report or table? Need all the help I can get right now and you are helping a lot and very much appreciate it.

Thanks,

Mike

|||

FYI, using pivot with SS 2005

Code Snippet

Create Table #Project (

Project nvarchar(10),

Task nvarchar(10),

Category nvarchar(10),

FisicalYear int,

FisicalMonth int,

totalhrs Int

);

--Sample Data

Insert Into #Project Values('Project 1','Task 1', 'Cat 1', '2007', '1', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 2', '2007', '2', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 3', '2007', '3', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 4', '2007', '4', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 5', '2007', '5', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 6', '2007', '6', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 7', '2007', '7', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 8', '2007', '8', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 9', '2007', '9', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 10', '2007', '10', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 11', '2007', '11', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 12', '2007', '12', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 1', '2008', '01', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 2', '2008', '02', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 3', '2008', '03', '40' );

--Pivot Query

SELECT Project,Task, Category, FisicalYear,

ISNULL([1],0) AS 'Month 01',ISNULL([2],0) AS 'Month 02',ISNULL([3],0) AS 'Month 03',ISNULL([4],0) AS 'Month 04',

ISNULL([5],0) AS 'Month 05',ISNULL([6],0) AS 'Month 06',ISNULL([7],0) AS 'Month 07',ISNULL([8],0) AS 'Month 08',

ISNULL([9],0) AS 'Month 09',ISNULL([10],0) AS 'Month 10',ISNULL([11],0) AS 'Month 11',ISNULL([12],0) AS 'Month 12'

FROM (SELECT Project, Task, Category, FisicalYear,FisicalMonth,totalhrs,

Row_NUmber() OVER (partition by FisicalYear Order by FisicalMonth) as RowNum

FROM #Project) p

PIVOT

(SUM(totalhrs) FOR FisicalMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))

AS pvt

ORDER BY FisicalYear, RowNum

--

--SELECT Project,Task, Category, FisicalYear,

--SUM(CASE FisicalMonth WHEN 1 THEN totalhrs ELSE 0 END) AS 'Month 01',

--SUM(CASE FisicalMonth WHEN 2 THEN totalhrs ELSE 0 END) AS 'Month 02',

--SUM(CASE FisicalMonth WHEN 3 THEN totalhrs ELSE 0 END) AS 'Month 03',

--SUM(CASE FisicalMonth WHEN 4 THEN totalhrs ELSE 0 END) AS 'Month 04',

--SUM(CASE FisicalMonth WHEN 5 THEN totalhrs ELSE 0 END) AS 'Month 05',

--SUM(CASE FisicalMonth WHEN 6 THEN totalhrs ELSE 0 END) AS 'Month 06',

--SUM(CASE FisicalMonth WHEN 7 THEN totalhrs ELSE 0 END) AS 'Month 07',

--SUM(CASE FisicalMonth WHEN 8 THEN totalhrs ELSE 0 END) AS 'Month 08',

--SUM(CASE FisicalMonth WHEN 9 THEN totalhrs ELSE 0 END) AS 'Month 09',

--SUM(CASE FisicalMonth WHEN 10 THEN totalhrs ELSE 0 END) AS 'Month 10',

--SUM(CASE FisicalMonth WHEN 11 THEN totalhrs ELSE 0 END) AS 'Month 11',

--SUM(CASE FisicalMonth WHEN 12 THEN totalhrs ELSE 0 END) AS 'Month 12'

--

--FROM #Project

--

--GROUP BY Project,Task, Category, FisicalYear

--cleanup

drop table #Project

Creating a Stored Procedure that will summarize data in a table into a table reflecting period d

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours

Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40

Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20

Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35

Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40

Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40

Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year

Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007

Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007

Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007

Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008

Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,

Mike Misera

Check out the PIVOT operator

|||

Mike, check out this sample script. You would need a case statement for each month, this is just using one for each month supplied in the example.

Code Snippet

CREATE TABLE #currentTable (

Project NVARCHAR(10),

Task NVARcHAR(10),

Category NVARcHAR(10),

FiscalYear INT,

FiscalMonth INT,

TotalHours INT

)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 1', 2007, 01, 40)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 2', 2007, 02, 20)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 3', 2007, 03, 35)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 1', 2008, 01, 40)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 2', 2008, 02, 40)

INSERT INTO #currentTable VALUES ('Proj 1', 'Task 1', 'Cat 3', 2008, 03, 40)

SELECT Project, Task, Category, FiscalYear,

SUM(CASE FiscalMonth WHEN 1 THEN TotalHours ELSE 0 END) AS '01',

SUM(CASE FiscalMonth WHEN 2 THEN TotalHours ELSE 0 END) AS '02',

SUM(CASE FiscalMonth WHEN 3 THEN TotalHours ELSE 0 END) AS '03'

FROM #currentTable

GROUP BY Project, FiscalYear, Task, Caetegory

DROP TABLE #currentTable

|||

ShawnNSF,

When I run this statement the results in query analyzer it pulls back the results that I want to see on the bottom of the results pane based off of the the Selection statement looking into X_PJLABDIS(view).

My problem with this script is the INSERT step. It adds the values in the parantheses and I need it to add the values returned from the SELECT Statement below that.

Mike

CREATE TABLE PIVOT (

Project NVARCHAR(10),
pjt_entity NVARcHAR(10),
acct NVARcHAR(10),
Fiscalno_year INT,
Fiscalno_month INT,
total_hrs INT

)

INSERT INTO PIVOT VALUES ('Project', 'Pjt_entity', 'acct', 2007, 01, 40)

SELECT Project, pjt_entity, acct, Fiscalno_year,

SUM(CASE Fiscalno_month WHEN 1 THEN total_hrs ELSE 0 END) AS 'Month 01',
SUM(CASE Fiscalno_month WHEN 2 THEN total_hrs ELSE 0 END) AS 'Month 02',
SUM(CASE Fiscalno_month WHEN 3 THEN total_hrs ELSE 0 END) AS 'Month 03',
SUM(CASE Fiscalno_month WHEN 4 THEN total_hrs ELSE 0 END) AS 'Month 04',
SUM(CASE Fiscalno_month WHEN 5 THEN total_hrs ELSE 0 END) AS 'Month 05',
SUM(CASE Fiscalno_month WHEN 6 THEN total_hrs ELSE 0 END) AS 'Month 06',
SUM(CASE Fiscalno_month WHEN 7 THEN total_hrs ELSE 0 END) AS 'Month 07',
SUM(CASE Fiscalno_month WHEN 8 THEN total_hrs ELSE 0 END) AS 'Month 08',
SUM(CASE Fiscalno_month WHEN 9 THEN total_hrs ELSE 0 END) AS 'Month 09',
SUM(CASE Fiscalno_month WHEN 10 THEN total_hrs ELSE 0 END) AS 'Month 10',
SUM(CASE Fiscalno_month WHEN 11 THEN total_hrs ELSE 0 END) AS 'Month 11',
SUM(CASE Fiscalno_month WHEN 12 THEN total_hrs ELSE 0 END) AS 'Month 12'

FROM X_PJLABDIS

GROUP BY Project, Fiscalno_year, pjt_entity, acct

|||

Mike,

Those values are just for example. Do not use that part of the script. You seem to have gotten the idea by modifying the select statement (the part that was for you). I should have explained a little better that the other part of my snippet was just me building test data to show you the example query.

You can just run a query with the same idea as what I posted against the table you have with the data already populated in it.

|||

Shawn,

As you can probably tell right now, I am have very limited knowledge of and using pivot tables and stored procedures. As you said I am going down the right path with this code.

How would I go about achieving returning the results into a report or table? Need all the help I can get right now and you are helping a lot and very much appreciate it.

Thanks,

Mike

|||

FYI, using pivot with SS 2005

Code Snippet

Create Table #Project (

Project nvarchar(10),

Task nvarchar(10),

Category nvarchar(10),

FisicalYear int,

FisicalMonth int,

totalhrs Int

);

--Sample Data

Insert Into #Project Values('Project 1','Task 1', 'Cat 1', '2007', '1', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 2', '2007', '2', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 3', '2007', '3', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 4', '2007', '4', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 5', '2007', '5', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 6', '2007', '6', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 7', '2007', '7', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 8', '2007', '8', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 9', '2007', '9', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 10', '2007', '10', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 11', '2007', '11', '20' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 12', '2007', '12', '35' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 1', '2008', '01', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 2', '2008', '02', '40' );

Insert Into #Project Values('Project 1','Task 1', 'Cat 3', '2008', '03', '40' );

--Pivot Query

SELECT Project,Task, Category, FisicalYear,

ISNULL([1],0) AS 'Month 01',ISNULL([2],0) AS 'Month 02',ISNULL([3],0) AS 'Month 03',ISNULL([4],0) AS 'Month 04',

ISNULL([5],0) AS 'Month 05',ISNULL([6],0) AS 'Month 06',ISNULL([7],0) AS 'Month 07',ISNULL([8],0) AS 'Month 08',

ISNULL([9],0) AS 'Month 09',ISNULL([10],0) AS 'Month 10',ISNULL([11],0) AS 'Month 11',ISNULL([12],0) AS 'Month 12'

FROM (SELECT Project, Task, Category, FisicalYear,FisicalMonth,totalhrs,

Row_NUmber() OVER (partition by FisicalYear Order by FisicalMonth) as RowNum

FROM #Project) p

PIVOT

(SUM(totalhrs) FOR FisicalMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))

AS pvt

ORDER BY FisicalYear, RowNum

--

--SELECT Project,Task, Category, FisicalYear,

--SUM(CASE FisicalMonth WHEN 1 THEN totalhrs ELSE 0 END) AS 'Month 01',

--SUM(CASE FisicalMonth WHEN 2 THEN totalhrs ELSE 0 END) AS 'Month 02',

--SUM(CASE FisicalMonth WHEN 3 THEN totalhrs ELSE 0 END) AS 'Month 03',

--SUM(CASE FisicalMonth WHEN 4 THEN totalhrs ELSE 0 END) AS 'Month 04',

--SUM(CASE FisicalMonth WHEN 5 THEN totalhrs ELSE 0 END) AS 'Month 05',

--SUM(CASE FisicalMonth WHEN 6 THEN totalhrs ELSE 0 END) AS 'Month 06',

--SUM(CASE FisicalMonth WHEN 7 THEN totalhrs ELSE 0 END) AS 'Month 07',

--SUM(CASE FisicalMonth WHEN 8 THEN totalhrs ELSE 0 END) AS 'Month 08',

--SUM(CASE FisicalMonth WHEN 9 THEN totalhrs ELSE 0 END) AS 'Month 09',

--SUM(CASE FisicalMonth WHEN 10 THEN totalhrs ELSE 0 END) AS 'Month 10',

--SUM(CASE FisicalMonth WHEN 11 THEN totalhrs ELSE 0 END) AS 'Month 11',

--SUM(CASE FisicalMonth WHEN 12 THEN totalhrs ELSE 0 END) AS 'Month 12'

--

--FROM #Project

--

--GROUP BY Project,Task, Category, FisicalYear

--cleanup

drop table #Project

Creating a stored procedure in SQL Server Management Studio

I have a database that I want to add stored procedures but when I go to save the stored procedure it asked me to save it as a .sql file and doesn;t add it to the database.

This is what I'm doing:

right click on database > programability > stored procedures

select "new Stored Procedure"

writting the procedure

then save - this is when i get the save dialogue rather than seeing the sp added to the database.

There must be something very simple that I'm doing wrong but I just can't figure it out, any help would be greatly appreciated.

Damien

You need to execute this command (that begins with CREATE PROCEDURE) instead of saving to disk.

So, you need to press F5 or press the Execute Button from program bar.

|||Thanks

creating a stored procedure-- help

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure
$y=1;
$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
while ($row = mysql_fetch_array($result))
{
$UpdateQuery = "Update tblNews
Set aOrder= $y
Where ID=".$row["ID"];
mysql_query($UpdateQuery,$db_connection)
;
$y++;
}
}
basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed
Can anyone help me please, is it possible
thanks in advance
SteveIf all values in column [aOrder] are diff, then you can try,
update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)
AMB
"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
> $y=1;
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
> while ($row = mysql_fetch_array($result))
> {
> $UpdateQuery = "Update tblNews
> Set aOrder= $y
> Where ID=".$row["ID"];
> mysql_query($UpdateQuery,$db_connectio
n);
> $y++;
> }
> }
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
> Can anyone help me please, is it possible
> thanks in advance
> Steve
>|||
Steve,
You could use something along the lines of this... (Un-Tested)
Create Proc TestProcedure
As Begin
Declare @.ID Integer
Declare @.NewOrder Integer
Set @.NewOrder = 1
Declare OrderCursor Cursor For
Select ID From tblNews
Order By aOrder
Open OrderCursor
Fetch Next From OrderCursor Into @.ID
While @.@.Fetch_Status = 0
Begin
Update tblNews
Set aOrder = @.NewOrder
Where ID = @.ID
Set @.NewOrder = @.NewOrder + 1
Fetch Next From OrderCursor Into @.ID
End
Close OrderCursor
Deallocate OrderCursor
End
Go
Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.
Hope this helps
Barry|||Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.
Just needed to use the correct field names and rename Interger to Int,
proc to procedure!
Now i can finish my job
Its only for a small amount of data, 10-20 records
Awesome
Steve :)
Barry wrote:

> Steve,
>
> You could use something along the lines of this... (Un-Tested)
>
> Create Proc TestProcedure
> As Begin
>
> Declare @.ID Integer
> Declare @.NewOrder Integer
> Set @.NewOrder = 1
>
> Declare OrderCursor Cursor For
> Select ID From tblNews
> Order By aOrder
>
> Open OrderCursor
> Fetch Next From OrderCursor Into @.ID
>
> While @.@.Fetch_Status = 0
> Begin
>
> Update tblNews
> Set aOrder = @.NewOrder
> Where ID = @.ID
> Set @.NewOrder = @.NewOrder + 1
> Fetch Next From OrderCursor Into @.ID
> End
> Close OrderCursor
> Deallocate OrderCursor
>
> End
> Go
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
> Hope this helps
> Barry
>

Creating a Stored Procedure from 3 queries

Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.StageNameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GOHi

A few points:

Check out http://www.aspfaq.com/show.asp?id=2319 for multiple record sets.

All three queries look like they should be combined into one, if not, you
will need to use a cursor.

Your second query is trying to set variables and also return a result set,
which is not allowed.

It is not good to use select * in production code.

I think you should also check for errors and add a SET NOCOUNT ON.

John

"Satvic" <fuck@.spammers.com> wrote in message
news:bl64ub$6de$1@.ins22.netins.net...
Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times
DB gets access from 1 asp page. The result procedure only works 1/2 way
(does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the
number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's
info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted
manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted,
ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID =
ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.Stage
NameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GO|||Satvic (fuck@.spammers.com) writes:
> CREATE PROCEDURE csp_AuthorAccountInfo
> @.CandidateID int,
> AS
> DECLARE @.ScriptsNo int, @.ManuscriptID int
> SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
> CandidateID = @.CandidateID

It would probably be better to return the count as an output parameter:

CREATE PROCEDURE csp_AuthorAccountInfo @.CandidateID int,
@.ScriptsNo int OUTPUT AS

DECLARE @.ManuscriptID int

SELECT @.ScriptsNo = count(*) as ScriptsNo
FROM Manuscripts WITH (NOLOCK)
WHERE CandidateID = @.CandidateID

And don't use NOLOCK unless you understand exactly what you are doing.

> /* this is where it stops all the time :(
> Theoretically speaking, next SELECT will only return 1 row with
> Candidate's info*/
> SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State,
> cn.Country
> FROM Candidates c INNER JOIN
> Manuscripts m ON
> c.CandidateID = m.CandidateID INNER JOIN
> Locations l ON
> c.LocationID = l.LocationID INNER JOIN
> cn ON
> l.CountryCode = cn.CountryCode
> WHERE c.CandidateID = @.CandidateID

As John Bell pointed out, you cannot assigned variables and return
result sets in the same query.

And your comment that the SELECT would only return one seems a bit funny.
I don't know your tables, but if this row returns 1 row, then the
SELECT COUNT(*) always returns 1.

I don't know about your tables, but I would guess that a Candidate can
zero or more Manuscripts, in which case the above could return 0 or
more rows.

Since I don't know the data you are accessing, it is difficult to give
precise advice, but you should probably investigate the use of temp
tables and table variables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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