Auto Generating Stored Procedures ?
Jan 17, 2007
Hi!
Let's say users of my app can build some reports based on a current database schema and my custom-database adapter will generate some SQL to get the right data.
User can save his report template ( basically data needed to create SQL ) in some storage. Will it be better solution to generate stored procedure for such report and save stored proc to a database and their name with raport data so next time user needs this report it will use stored proc.
Jarod
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hi all,What I am trying to do is generate a stored procedure that is desiredto output XML in this type of format<Parent Device><Device><Device ID>1</DeviceID><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister></Device></Parent Device>The area of concern is the child register, the XML being generateddisregards the Device the ChildRegister belongs to and always places itas elements of the last device.<Parent Device><Device><Device ID>1</DeviceID></Device><Device><Device ID>2</DeviceID><ChildRegister><ChildRegisterID>23</ChildRegisterID></ChildRegister><ChildRegister><ChildRegisterID>22</ChildRegisterID></ChildRegister></Device></Parent Device>I am trying to produce XML like the first one I described and have yetto discover a way of associating the ChildRegister with the parentDevice in XML. I am not sure if it is a limitation of SQL Server, or ifmy implementation is incorrect. If anyone could post hints orsolutions, I would greatly appreciate it.A shortened version of the stored procedure is belowCheers :)AlvinSELECT1AS TAG,NULL AS PARENT,NULL AS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element]FROM udetails INNER JOINDetail ON udetails.ID = Detail.IDWHERE (uDetails.JobID = @ID)UNION ALLSELECT2 AS TAG,1 AS PARENT,TempTable.DeviceIDAS [Device!2!DeviceID!element],NULL AS [ChildRegister!3!RegisterID!element][color=blue]>From #Temp as TempTable INNER JOIN[/color]device ON TempTable.DeviceID = device.DeviceIDUNION ALLSELECT3 AS TAG,2 AS PARENT,NULL AS [Device!2!DeviceID!element],RegisterID AS [ChildRegister!3!RegisterID!element]FROM #Temp t INNER JOINregister ON t.DeviceID =register.DeviceIDFOR XML EXPLICIT
View 1 Replies
View Related
Apr 12, 2007
Is it possible to get SQL Server to automatically update the table/field names in your stored procedures when changing those table/field names in the database?
View 2 Replies
View Related
Jun 5, 2006
Hi All,
I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)
Is there anyway to achive this via SQL 05 Express?
Thanks,
Mal.
View 4 Replies
View Related
Sep 5, 2006
Hi There,I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.AutoRow1234 -------- reset1234 --------resetAny help would be great, many thanksStuart
View 5 Replies
View Related
Sep 22, 2004
Hi everyone,
I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.
Say i am executing this query
SELECT NAME,AGE,ADDRESS FROM MEMBER which gives
NAME AGE ADDRESS
HOLYMAC 13 MALACCA
HOLYCOW 25 USA
HOLYGOD 55 LONDON
Now how can i make it come out like this
SNO NAME AGE ADDRESS
1HOLYMAC 13 MALACCA
2HOLYCOW 25 USA
3HOLYGOD 55 LONDON
See the first column is a auto incrementing number 1,2,3.
How can I write a SQL QUERY that outputs a auto incrementing number.
Thank you
Have nice day
View 2 Replies
View Related
Aug 28, 2007
Hi guys,
I am new to programming and sqlserver 2000. i use sql2000 as my database server...
I have a problem on auto-generating medicine codes. and i hope someone would help me with my script.
as you noticed i declared two variables (@id and @code). I am having a problem in generating @code... i would just like to create an autogenerated medicines_code from another field from another table (hosp_counter.counter_id)
here is how my medicines_code should look like ('MEDS34') "where 34 is from a counter_id in hosp_counter table...
but it only creates a medicines_code = 'M' (below is the result)
34(this is from counter_id)M(this is the autogenerated code)AUTOGENERATE11111010110012110010000000018/29/2007 11:29:46 AM1/1/19001/1/1900
here is my script in autogenerating medicine code...
set xact_abort on
begin transaction
declare @Code as varchar
select @Code =
(select 'MEDS' + CAST
(Counter_ID + 1 as varchar (50))
from Hosp_Counter where table_description = 'Medicines')
declare @ID as integer
select @ID = Counter_ID + 1
from Hosp_Counter
where table_description = 'Medicines'
select @ID
INSERT INTO Hosp_Medicines
(medicines_id,
medicines_code,
medicines_description,
medicines_specification,
medicines_genericname,
medicines_drugclass,
PhicCategory,
medpreparation,
medicines_prohibited,
medicines_trade,
medicines_NoneTrade,
medicines_taxable,
medicines_PhicCovered,
medicines_AmountCovered,
medicines_Discountable,
medicines_SmallUnit,
medicines_BigUnit,
medicines_AverageCostSmall,
medicines_AverageCostBig,
medicines_WeightedAverageCost,
medicines_weight,
medicines_conversion,
medicines_LowStockLevel,
medicines_OverStockLevel,
medicines_LowSalesReference,
AllowStat,
status,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
VALUES
(@ID,
@code,
'AUTOGENERATE2',
'',
1,
1,
1,
1,
0,
1,
0,
1,
1,
0,
0,
1,
2,
1,
100,
100,
0,
0,
0,
0,
0,
0,
1,
'',
'',
'',
'8/29/2007 11:31:25 AM',
'',
'')
select @ID
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'Medicines'
commit transaction
any help is very much appreciated!
'confused'
Joel
View 3 Replies
View Related
Jul 23, 2005
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Jul 22, 2007
Is it possible to have a DateTime field in a row automatically generated when a row is created?
ie:
INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @UserId, @Title, @Explanation)
or something like that?
View 2 Replies
View Related
Aug 2, 2007
Hi...
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
View 7 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Oct 28, 1999
I wrote a stored procedure with three variables to be passed as input parameters. The stored procedure runs a select into statement into a temp table. The resulting temp table with another table was queried using right outer join to produce the desired results.
The stored procedure compiles error free.However when I ran the stored procedure with the parameters(3) in ISQL/W (SQL Server 6.5) the headers(column-names) were displayed but no records.
When runned as a query with the same parameter values, records were produced.
Help please urgent.
Regards
Olutimi Ooruntoba
View 2 Replies
View Related
Mar 3, 2007
I am trying to raise an event using sp_trace_generateevent in my activation stored proc. (dbo.ActivationSP)
EXEC sp_trace_generateevent @event_class = 82, @userinfo = N'Test Event'
There is a Service broker service listening to this event.
The problem is the event is getting fired whenever the activation SP is executed (could see in profiler) but the secondtargetqueue doesnt receive any messaages.
But if manually do a "EXEC sp_trace_generateevent", the secondtargetqueue receives a messaage.
Both the queues and sevices are in the same database.
The following are the code snippets
-- Code for queue on which the activation is working
CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION (PROCEDURE_NAME = dbo.ActivationSP,MAX_QUEUE_READERS = 5,Execute AS 'dbo') ;
Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)
-- Code for Queue listening to event
Create Queue SecondTargetQueue WITH status= ON
Create Service SecondReceiverService ON QUEUE SecondTargetQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
CREATE EVENT NOTIFICATION TestNotification
ON SERVER FOR UserConfigurable_0 TO SERVICE 'SecondReceiverService', 'current database'
View 3 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Dec 16, 2004
Hi
How Can I Generating wrapper code for SQL Server Stored Procedure ??
If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server
http://www.codeproject.com/vb/net/OracleSPWrapper.asp
my question .. where is this tools ???
and thanks with my regarding
Fraas
View 1 Replies
View Related
Apr 1, 2008
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
Thanks!
View 1 Replies
View Related
Dec 12, 2007
Hi
I was wondering if it was possible to call reporting server web service directly from my sql server stored procedure. The call that I need to make to reporting web service needs to generate the report in a PDF format.
View 1 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Feb 16, 2014
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
View 2 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Jan 23, 2004
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
View 3 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Jun 17, 2006
hi
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
looking forward for a replygayathri
View 1 Replies
View Related
Dec 15, 2006
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
View 1 Replies
View Related
Feb 24, 2007
Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.
View 4 Replies
View Related
