Stored Procedure
-
I have a stored procedure and want to know after I run it is there away to get it to reopen with out having to right click on it and telling it to execute it.
USE [IT]
GO
/****** Object: StoredProcedure [IT].[Insert WorkRequest proc] Script Date: 11/13/2012 08:45:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [IT].[Insert WorkRequest proc]
-- Add the parameters for the stored procedure here
@DeptID int,
@ITDeptID int,
@Department nvarchar(25),
@problem nvarchar(50),
@completed nchar(4),
@notes nvarchar(255)
--@StartDate [datetime],
--@EndDate [datetime]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into IT.WorkRequest
(DeptID,
ITDeptID,
Department,
problem,
completed,
notes)
Values
(
@DeptID,
@ITDeptID,
@Department,
@problem,
@completed,
@notes
)
----select * from it.WorkRequest
end
-
edward_hall76 (11/13/2012)
I have a stored procedure and want to know after I run it is there away to get it to reopen with out having to right click on it and telling it to execute it....
?????
What do you mean by "get it to reopen"? Do you want to see its code?
Right click on it and select Modify...
OR:
sp_helptext [stored proc name]
-
Is there away after it has ran get it to reopen so you can insert the information using the proc without having to right click on it and telling it to execute.
-
If you know what data you want to insert via the proc, just call the proc using the exec / execute clause
EXEC [IT].[Insert WorkRequest proc] @.... = '' , @.... = '' ............
-
sorry for not explaining very well. I have a proc
when I right click on it it brings up a execute proc I enter the information in it and hit ok it excutes. I want it to open the execute proc window again after I hit ok. is there some way to do this.
-
Could you explain your need in more detail please? 'reopen'???
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I have a proc called IT.InsertWorkRequestProc I right click on it and tell it to execute and it brings up a execute proc window for me to enter the information and I hit ok and it execute it. Is there some way to put an exec statement in there that will reopen the execute proc after it has ran so you can just enter the information again with out having to right click on the proc and telling it to execute.
-
The only way to bring the Execute Procedure screen back up is to right click the procedure and select the execute option.
Or as I have said above, you know what information you want to pass in via the parameters so just build an EXEC string up passing in the values you want
EXEC [IT].[Insert WorkRequest proc] @DeptID = '', @ITDeptID = '', @Department = '', @problem = '', @completed = '', @notes = ''
-
the issue here is GUI dependancy;
you've got to get used to using the TSQL window for your commands, instead of using the GUI tools.
if you right click on a procedure, you get a form where you can enter the values for the parameters for the given procedure;
it's much easier to simply use TSQL to do that instead.
here's an example, showing both methods at the same time.
note that after you populate the values for the parameters, if you click the "Script" icon in the upper left of the window, you can get a TSQL window built for you automatically.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ok. where I work not all people know how to write tsql code and if they mess up the tsql they do not know how to fix it is why using theymuse the proc GUI. I just though there might be away of getting the GUI to reopen after running it without having to right click on the proc
-
edward_hall76 (11/13/2012)
ok. where I work not all people know how to write tsql code and if they mess up the tsql they do not know how to fix it is why using theymuse the proc GUI. I just though there might be away of getting the GUI to reopen after running it without having to right click on the procThat is extremely bad idea to give "people who doesn't know how to write t-sql code" access to SSMS. Are you going to install it on every user workstation?
You should create some UI application which will execute stored proc. Users, don't even need to know how to do it via SSMS...
-
Is the code not in the window about the results?
eg, I just right clicked and ran this sp. Above the results window I can see
USE [Reporting1213]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[Finance_AvB_Accom_Summary]
@tomonth = N'October'
SELECT'Return Value' = @return_value
GO
If I want September, I change October to September. Is this the kind of thing you are looking for?
Bex
-
edward_hall76 (11/13/2012)
ok. where I work not all people know how to write tsql code and if they mess up the tsql they do not know how to fix it is why using theymuse the proc GUI. I just though there might be away of getting the GUI to reopen after running it without having to right click on the procfor end users, you create an application, or a web page, or some custom front end that runs the procedure on their behalf.
the GUI needs to provide them with the limited input that is appropriate for their needs...drop down menus for values, for example.
that application needs to validate data entered, and be ready to report results as well as report any errors that occurred to the end user.
there's always work involved when releasing a functionality to end users.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ok will have to refresh my self on php.
-
Eugene Elutin (11/13/2012)
edward_hall76 (11/13/2012)
ok. where I work not all people know how to write tsql code and if they mess up the tsql they do not know how to fix it is why using theymuse the proc GUI. I just though there might be away of getting the GUI to reopen after running it without having to right click on the procThat is extremely bad idea to give "people who doesn't know how to write t-sql code" access to SSMS. Are you going to install it on every user workstation?
You should create some UI application which will execute stored proc. Users, don't even need to know how to do it via SSMS...
:w00t: The above is the correct answer! I can't imagine giving end-users access to SSMS...I don't even like giving SSMS access to some of my developers!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply