Creating DB from scripts: procedure

Showing posts with label procedure. Show all posts

Thursday, March 29, 2012

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

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

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

Tuesday, March 27, 2012

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

BEGIN

print'a'

END

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

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

BEGIN

print 'a'

END

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

Thanks..

Sunday, March 25, 2012

Creating a unique temp table.

Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go

Alter Procedure spReport_SomeFooReport
@.SearchFromThisDate datetime = null, @.SearchToThisDate datetime = null

As
Declare @.TableUniqueIdentifier varchar(80), @.SQLString varchar(5000)

set @.TableUniqueIdentifier = newid()
set @.TableUniqueIdentifier = 'Report_SomeFooReport' + @.TableUniqueIdentifier
set @.TableUniqueIdentifier = replace(@.TableUniqueIdentifier, '-', '7')
set @.SQLString = 'Create Table ' + @.TableUniqueIdentifier + ' (xxx varchar(40))'
exec @.SQLString

Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go

--------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.

So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00This looks like Sql Server. If that's the case, try using

EXEC (@.SQLString)|||RedNeckGeek is right, the EXEC without the parentheses means you're calling a stored procedure.|||also, it's bad form to create permanent tables on the fly from sprocs.

If your database schema is well designed, there would be no need for this. Perhaps use a temp table instead?

Or use a single permanent table for all your reports since they all have exactly the same structure, with just a single column, xxx varchar(40). You could add another column to identify the report instance.|||I would like to know howyou plan to reference that table in the future|||I would like to know howyou plan to reference that table in the future

the only possible way I know of would be to return the table name as an out param from the sproc.

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

Thursday, March 22, 2012

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 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

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

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