Drop Stored Procedure 'sp1' If Exist&"
Jun 19, 2007How can i delete a stored procedure if it exist
like "Drop stored procedure 'sp1' if exist"
ADVERTISEMENT
How To Add If ~else To A Exist Stored Procedure
Oct 15, 2007 here is my original Sql Code I add a parameters @CustId in line 13 when CustID not Null, I want to add this to the Where clause line 146 and 156 if CustId not null then where clause will add a reference like CustId = @CustIDif CustId is Null then not reference @custID how to Add a if ~else clause to my code? i tried all day.. but it doesn't work.. 1 SET QUOTED_IDENTIFIER OFF
2 GO
3 SET ANSI_NULLS OFF
4 GO
5
6
7
8 ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]
9 @DTBEG DATETIME,
10 @DTEND DATETIME,
11 @Remark INT,
12 @BankId VARCHAR (128)
13 @CustID CHAR
14
15
16 as
17
18
19
20 SELECT
21 *
22 FROM
23 (
24 SELECT
25 ZT_Master.PriKey,
26 ZT_Master.BankId,
27 ZT_Master.TDateTime,
28 ZT_Master.PNo,
29 ZT_Master.Remark,
30 ZT_Master.CustId,
31 ZT_Master.ProcStatus,
32 ZT_Customer.[Name],
33 --ZT_Customer.AccountBAK AS Account,
34 --ZT_Customer.SCAccountBAK AS SCAccount
35 (SELECT TOP 1 SUBSTRING(PCLNO, 3, 12) FROM ZT_Detail WHERE ZT_Master.PriKey = ZT_Detail.MasterKey AND ZT_Detail.TXTYPE = 'SD') AS Account,
36 (SELECT TOP 1 SUBSTRING(PCLNO, 3, 12) FROM ZT_Detail WHERE ZT_Master.PriKey = ZT_Detail.MasterKey AND ZT_Detail.TXTYPE = 'SC') AS SCAccount
37 FROM
38 ZT_Master LEFT JOIN ZT_Customer ON ZT_Master.CustId=ZT_Customer.Id
39 ) a
40 ,-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41 (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
42 CtcbBanSDTotal=ISNULL(本行代收ç†æ•¸,0),
43 CtcbBanSDTotalAMT=ISNULL(本行代收金�,0),
44 OtherBanSDTotal=ISNULL(他行代收ç†æ•¸,0),
45 OtherBanSDTotalAMT=ISNULL(他行代收金�,0),
46 CtcbBanSCTotal=ISNULL(本行代付ç†æ•¸,0),
47 CtcbBanSCTotalAMT=ISNULL(本行代付金�,0),
48 OtherBanSCTotal=ISNULL(他行代付ç†æ•¸,0),
49 OtherBanSCTotalAMT=ISNULL(他行代付金�,0),
50 GoodSDTotal=ISNULL(代收æˆ?功ç†æ•¸,0),
51 GoodSDTotalAMT=ISNULL(代收�功金�,0),
52 GoodSCTotal=ISNULL(代付æˆ?功ç†æ•¸,0),
53 GoodSCTotalAMT=ISNULL(代付�功金�,0),
54 BadSDTotal=ISNULL(代收失敗ç†æ•¸,0),
55 BadSDTotalAMT=ISNULL(代收失敗金�,0),
56 BadSCTotal=ISNULL(代付失敗ç†æ•¸,0),
57 BadSCTotalAMT=ISNULL(代付失敗金�,0)
58 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
59 本行代收ç†æ•¸,
60 本行代收金�,
61 他行代收ç†æ•¸,
62 他行代收金�,
63 本行代付ç†æ•¸,
64 本行代付金�,
65 他行代付ç†æ•¸,
66 他行代付金�,
67 代收æˆ?功ç†æ•¸,
68 代收�功金�,
69 代付æˆ?功ç†æ•¸,
70 代付�功金�
71 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
72 本行代收ç†æ•¸,
73 本行代收金�,
74 他行代收ç†æ•¸,
75 他行代收金�,
76 本行代付ç†æ•¸,
77 本行代付金�,
78 他行代付ç†æ•¸,
79 他行代付金�
80 -------------------------------------------------------------------代收æˆ?功ç†æ•¸èˆ‡ä»£æ”¶æˆ?功金é¡?--------------------------------------------------------------
81 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
82 本行代收ç†æ•¸,
83 本行代收金�,
84 他行代收ç†æ•¸,
85 他行代收金�
86 FROM (SELECT MasterKey,
87 COUNT(PriKey) AS 本行代收ç†æ•¸,
88 SUM(AMT) AS 本行代收金�
89 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) = '822' GROUP BY MasterKey) a
90 FULL JOIN (SELECT MasterKey,
91 COUNT(PriKey) AS 他行代收ç†æ•¸,
92 SUM(AMT) AS 他行代收金�
93 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) <> '822' GROUP BY MasterKey) b
94 ON a.MasterKey=b.MasterKey) a
95 -------------------------------------------------------------代付æˆ?功ç†æ•¸èˆ‡ä»£ä»˜æˆ?功金é¡?---------------------------------------------------------------
96 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
97 本行代付ç†æ•¸,
98 本行代付金�,
99 他行代付ç†æ•¸,
100 他行代付金�
101 FROM (SELECT MasterKey,
102 COUNT(PriKey) AS 本行代付ç†æ•¸,
103 SUM(AMT) AS 本行代付金�
104 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) = '822' GROUP BY MasterKey) a
105 FULL JOIN (SELECT MasterKey,
106 COUNT(PriKey) AS 他行代付ç†æ•¸,
107 SUM(AMT) AS 他行代付金�
108 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) <> '822' GROUP BY MasterKey) b
109 ON a.MasterKey=b.MasterKey) b
110 ON a.MasterKey=b.MasterKey) a
111 -----------------------------------------------------------------æˆ?功ç†æ•¸èˆ‡æˆ?功金é¡?---------------------------------------------------------------------------
112 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
113 代收æˆ?功ç†æ•¸,
114 代收�功金�,
115 代付æˆ?功ç†æ•¸,
116 代付�功金�
117 FROM (SELECT MasterKey,
118 COUNT(PriKey) AS 代收æˆ?功ç†æ•¸,
119 SUM(AMT) AS 代收�功金�
120 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE='00' OR RCODE='') GROUP BY MasterKey) a
121 FULL JOIN (SELECT MasterKey,
122 COUNT(PriKey) AS 代付æˆ?功ç†æ•¸,
123 SUM(AMT) AS 代付�功金�
124 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE='00' OR RCODE='') GROUP BY MasterKey) b
125 ON a.MasterKey=b.MasterKey) b
126 ON a.MasterKey=b.MasterKey) a
127 ---------------------------------------------------------------------失敗ç†æ•¸èˆ‡å¤±æ•—金é¡?---------------------------------------------------------------------------------
128 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),
129 代收失敗ç†æ•¸,
130 代收失敗金�,
131 代付失敗ç†æ•¸,
132 代付失敗金�
133 FROM (SELECT MasterKey,
134 COUNT(PriKey) AS 代收失敗ç†æ•¸,
135 SUM(AMT) AS 代收失敗金�
136 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE<>'00' AND RCODE<>'') GROUP BY MasterKey) a
137 FULL JOIN (SELECT MasterKey,
138 COUNT(PriKey) AS 代付失敗ç†æ•¸,
139 SUM(AMT) AS 代付失敗金�
140 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE<>'00' AND RCODE<>'') GROUP BY MasterKey) b
141 ON a.MasterKey=b.MasterKey) b
142 ON a.MasterKey=b.MasterKey) b
143
144
145
146 WHERE a.PriKey=b.MasterKey AND
147 --TDateTime BETWEEN @DTBEG AND @DTEND AND
148 TDateTime BETWEEN '2007/9/5' AND '2007/9/5' AND
149
150 (a.Remark=@Remark OR @Remark=2) AND
151 (a.BankId=@BankId OR @BankId='')
152 ORDER BY TDateTime,CustId,PNo
153
154
155
156 WHERE a.PriKey=b.MasterKey AND
157 --TDateTime BETWEEN @DTBEG AND @DTEND AND
158 TDateTime BETWEEN '2007/9/5' AND '2007/9/5' AND
159
160 (a.Remark=@Remark OR @Remark=2) AND
161 (a.BankId=@BankId OR @BankId='')
162 ORDER BY TDateTime,CustId,PNo
163
164
165 GO
166 SET QUOTED_IDENTIFIER OFF
167 GO
168 SET ANSI_NULLS ON
169 GO
170
171
Can't Drop Stored Procedure
Feb 4, 2000I'm using SQL Serv 6.5. I've got a stored procedure that is DEFINITELY not in use but i can't drop it because it's telling me it is ???
I don't want to stop and start SQL Serv. Any ideas ??
thanks
Paul
Only Run Stored Procedure If Update Records Exist?
Oct 6, 2015I am trying to put together a Stored Procedure that runs other SP's based on a records found condition. Sometimes my update table either by deleting duplicates or for other reasons is empty. Running a SP to update records on a table is not needed.
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT1')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT1_Trace_Data
END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT2')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT2_Trace_Data
END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'FULF')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_FULF_Trace_Data
END
The Stored Procedure Doesn't Exist, And Other Refresh Problems
Jan 14, 2008I'm having some very frustrating 'syncing' problems with my report (currently in dev in visual studio) and what actually exists in the database. To start, I had my report definition open, and realized I needed a new sproc. So I went over to the db and created a new sproc, lets call it "Test1". After making a new dataset in ssrs, I can't seem to get SSRS to realize the stored proc actually exists. I continually get the error:
"The stored procedure 'Test1' doesn't exist. (System.Data)
I've shut down visual studio, re-opened, etc. Same thing. It looks like visual studio has some cached version of sys.procedures that only refreshes when the bloody thing feels like it. Any ideas?? I've been doing this for awhile, and I've minded the connection string, correct sproc syntax, etc. It runs fine on SQL Management studio.
Another, similar problem - When adding in a new column to a sproc, the dataset doesn't seem to pick up on it, no matter how many times the "refresh fields" button is hit.
Delete / Drop Extended Stored Procedure In 2005
Oct 5, 2007We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
Thanks In Advance
MPM
Drop Stored Procedure Causing Dropped Tables
Aug 29, 2006Hey guys, has anyone ever seen this happen:
Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.
Three tables referenced by that stored proc have been dropped and re-created with the same table structure.
I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?
SQL Tries To Drop Tables That Don't Exist
Mar 14, 2008When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?
Thanks!
Tables Exist But Wont Drop
Dec 11, 1998I have two tables that will not drop in a Database. I'm running SQL 6.5 on a Compaq Proliant.
Symptoms: Both tables show up in sysobjects, sysindexes, and syscolumns. Both allow SELECT, sp_rename, and truncate, but just hang ISQL or the Enterprise Manager when I try to drop them. I ran a DBCC CheckTable on them with no errors reported. Any ideas??
Cannot Drop The Table '#ZipLatLong', Because It Does Not Exist Or You Do Not Have Permission...
Apr 8, 2008
I have a stored procedure that I am writing that is giving me trouble dropping a temp table in SQL Server 2005. I have to use the SQL in Query 1, marked below, to get the temp table. I can find the table but I get the error listed in this message's subject. If I try to run DROP TABLE #ZipLatLong I will get an error telling me that the table already exists but then I can run CREATE TABLE #ZipLatLong ( ZIP INT, Latitude DECIMAL(18,6), Longitude DECIMAL(18,6)) and that will work...
In short, SQL Server 2005 is not letting me drop temporary objects. I am getting return messages telling me that an object already exists in the database by the name I am trying to delete and then it will let me create a table with the same name...
Figure 1
USE TEMPDB
GO
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'tempdb'
AND TABLE_SCHEMA = USER
AND TABLE_NAME LIKE '#ZipLatLong%'
GO
DROP TABLE #ZipLatLong
GO
Error Msg 3701 : Cannot Drop The View 'x' Because It Doesn't Exist In The System Catalo
Jun 25, 2001I am work on SQL Server 6.5
When I want to drop a view then appear error message :
"Msg 3701, Level 11, State 1 Cannot drop the view 'EUL_ODBC_SCHEMAS',
because it doesn't exist in the system catalogs."
Has anybody else experienced this, and do you know what is causing the warning?
Thanks in advance for any replies.
Drop Procedure
Nov 28, 2007Hi,
I am a very new programmer hired to do stored procedures.
I want to drop stored procedures with like names..Can I do that with single command something like
Drop Proc where proc like 'XY%'
Other alternative is write a stored procedure which is a group of stored procedures.. selected from sys.objects.
Please help me ..
Thank you
Swapna
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi 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?
Drop And Create Procedure
Feb 4, 2005I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?
Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...
Thanks
If Exists DROP PROCEDURE/VIEW
May 20, 2007how can I drop a propcedure or a view without error for MS SQL 2000/2500
for a table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]
and for a procedure
IF EXISTS ???
DROP PROCEDURE [dbo].[SP_myTABLE_Count]
thank you for helping
SCPTXFR.EXE-- Problem With Drop Procedure
Oct 10, 2006hi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.
SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:HSE0607SCHEMA.sql /q/r/T
Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.
Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this??
Regards,
Thanks.
Gurpreet S. Gill
Help On CREATE PROCEDURE Delete + Insert Where Not Exist
Apr 30, 2008help on CREATE stored procedure delete and after insert where not exist
in one stored procedure
in table_B
Code Snippet
CREATE PROCEDURE [dbo].[delete_from_table_B]
@empID varchar(500)
as
DELETE FROM table_B
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0
---HELP from this ponit how to insert ? after where not exist
IF @@ROWCOUNT > 0
BEGIN
insert into
table_B
set (empID,ShiftDate,shiftType)
where not exist
select
empID,ShiftDate,shiftType
from
table_A
table_A
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
999 qqq 21/03/2008 9
222 bbb 02/05/2008 7
222 bbb 03/05/2008 8
222 bbb 04/05/2008 9
222 bbb 05/05/2008 7
222 bbb 06/05/2008 9
222 bbb 07/05/2008 3
222 bbb 08/05/2008 4
222 bbb 09/05/2008 5
333 ccc 03/04/2008 9
333 ccc 04/04/2008 2
TABLE B
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
TNX for the help
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Could Not Find Server 'drop Procedure Dbo' In Sysservers
Oct 9, 2007I have SQL SERVER 2005 with SP2 on windows XP professional.
When I execute DROP PROCEDURE manually,it works.
However when I execute inside a loop made using static cursors,
it generates following error
"Could not find server 'drop procedure dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."
OR it gives this error "Could not find stored procedure"
any idea???
Stored Proceedures - How To Create/drop Them
Oct 10, 2006Hi What is the SQL syntax to create and destroy a stored proceedure with t-sql? Is there any good tutorials?ThanksPWP
View 5 Replies View RelatedT-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?
Jan 29, 2015I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Drop Create Vs Alter In Regards To Stored Procedures
May 17, 2007Anyone have any compelling arguements for using one over the other?
Thanks
Mercy
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedSQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
How To Capture SQL Server Does Not Exist Type Errors Within Stored Procs
Nov 16, 2007Hi, I have a stored procedure running on our monitoring server to monitor the backup jobs on other sql servers running on our network. This sp loops thorugh the list of server names and connects remotely to other servers using linked servers. However, if one of the servers is down, the sp stops after raising 42000 error ("SQL Server does not exist") and fails to carry on processing the next server in the list.
Is there any way to capture this "SQL Server does not exist" error within the sp so that the sp can carry on with the processing?
I am using SQL Server 2000 SP4.
Thanks in advance for any replies.
Opal
SQL Server 2008 :: How To Fetch Drop Down Values Stored In XML Column
Aug 13, 2015Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.
<DropDown Prompt="Case Manager" Column="case_manager" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">
<Items>
<Item Id="1">ABC</Item>
<Item Id="2">DEF</Item>
<Item Id="3">GHI</Item>
<Item Id="4">JKL</Item>
<Item Id="5">MNO</Item>
[code]....
SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures
Aug 19, 2015We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).
View 17 Replies View RelatedWhere Exist Condition - Pull Out Whole ID Group Which Code Z Exist
Dec 3, 2013I have data as below: select ID,code,go,back from tableA
ID code go back
2 C US UK
2 A UK US
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
I only want to pull out whole ID group which code Z exist. I tried as below:
select ID,code,go,back from tableA A
where exists
( select ID,go,back
from tableA B with (nolock)
where A.ID = B.ID
and A.code='Z')
But it only appear:
ID code go back
1 Z UK US
How can I appear with the group as well?
ID code go back
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
Drop All Indexes In A Table, How To Drop All For User Tables In Database
Oct 9, 2006Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedSystem Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes