How Does One Save A Stored Procedure
Dec 20, 2005
In Microsoft SQL Server Management Studio Express you can right click on "Stored Procedures" under a database in the object explorer and the resulting Context Menu offers a selection called "New Stored Procedure".
If you select "New Stored Procedure", a Stored Procedure Template Document is added for editing. It has the suffix ".sql"; I can save this document to the file system after I edit it, but I cannot figure out how to add it to the database as a Stored Procedure.
Can someone please tell me how to do this.
Thank you.
View 7 Replies
ADVERTISEMENT
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
Nov 17, 2006
Hi!
I'm using VS2005 and trying to save my very simple stored procedure.The error message "Invalid object name 'dbo.xxxxx' just pop's up.
The SP is written through the Server Explorer > Stored Procedures > Add new.... etc.Using SQL server 2000.
Don't think this has to do with the SP-code but here it is:
ALTER PROCEDURE dbo.KontrollUnikPersNr@PersNr nvarchar(50) = null OUTPUTASSELECT @PersNr = PersNr FROM User WHERE PersNr = @PersNrRETURN @@ROWCOUNT
Thanx i advance for any help!
View 4 Replies
View Related
Feb 8, 2007
I am running Sql Server 2005, When I create a new stored procedure and try to save it, I'm prompted with the 'save file as dialog'. Is'nt the stored procedure suppose to be saved within the database?
View 2 Replies
View Related
Mar 12, 2008
Hi, I have created a store procedure and want to save them. this is wha ti did.
I clicked on the Northwind--> Programm--> Stored Procedure-- > right clicked --> new stored procedure
after writing my stored procedure I saved it. But when I am saving I want to save it under the Stored Procedure folder
of the NorthWind database. I guess this is what it should do. When I use to use the sql 2000, i could always send my stored procedure here , i guess it was by default. But even if i save my sql stored procedure at any other location than at the run time, how the applicaiton will find my store procedure.
Well so thinking of that I have to save my stored procedure under the database for which table it is created however, i can not save it there. I tried to figure out the directly but I couldnt.
So any suggestion????
View 1 Replies
View Related
Jul 30, 2007
Hi,
i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio.
But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp.
Thanks
tartuffe
View 3 Replies
View Related
Jun 19, 2008
Hi,
Recently, I started to move more databases from SQL Server 2000 to 2005, well the Stored Procedures in 2005 is hard to handle.
First of all, I created the new SP in Object Explorer, the "New Stored Procedure" query just created a new name under the Stored Procedures folder, then I have to re-open it to put in TSQL code.
After I put in the code in the new SP, if I click "Save", the File
Manager comes out to let me to save in a physical location (C: or D: or Network place...). But after I close and open the new SP, the code I put in there is not really in the new SP.
I know that I have to "Execute" the SP in order to save the change. But the problem is I don't want to run the code at this step, the SP is to get a lot of data transactions, I'll setup a "Job" to do this.
So, the question is: Is this the way to create and save a new SP? Is there any other way to save the code changes in the SP WITHOUT "Execute" it?
Please give me any advise, article or links to read.
Thanks.
View 6 Replies
View Related
Nov 14, 2006
I am null in Transact-SQL
Hello , I ve made a SQM request I would provide it result via stored stored procedure how can I do this ?
this is the request
SELECT DISTINCT Account.Name AS exp, Campaign.New_FormationIdName
FROM Email INNER JOIN
CampaignActivity ON Email.RegardingObjectId = CampaignActivity.ActivityId INNER JOIN
Account ON Email.ToRecipients = Account.EMailAddress1 INNER JOIN
Campaign ON CampaignActivity.RegardingObjectId = Campaign.CampaignId CROSS JOIN
New_Formation CROSS JOIN
New_formationparticipation MINUS
SELECT New_exportateurIdName, New_formationIdName
FROM new_formationparticipation
-An other ponit: How can I add a parameter to this stored procedure?
View 1 Replies
View Related
Jul 15, 2004
I am trying to save an uploaded image and its associated info to sql server database using a stored procedure but keep getting trouble. When trying to save, the RowAffected always return -1. but when i debug it, I dont' see problem both from stored procedure server
explore and codebehind. it looks to me every input param contains correct value(such as the uploaded image file name, contentType and etc). well, for the imgbin its input param value returns something like "byte[] imgbin={Length=516}". Below is my code, could anyone help to point out what did I do wrong?
Thank you.
================================================
CREATE PROCEDURE [dbo].[sp_SaveInfo]
(
@UserID varchar(12),
@Image_FileName nvarchar(50),
@Image_ContentType nvarchar(50),
@Image_ImageData image,
@Create_DateTime datetime)
AS
set nocount on
insert ExpertImage(UserID, Image_FileName, Image_ContentType, Image_ImageData, Image_ReceiveDateTime)
values(@UserID, @Image_FileName, @Image_ContentType, @Image_ImageData, @Create_DateTime)
GO
private void Submit1_ServerClick(object sender, System.EventArgs e)
{
if(Page.IsValid)
{
Stream imgStream = File1.PostedFile.InputStream;
int imgLen=File1.PostedFile.ContentLength;
string imgContentType = File1.PostedFile.ContentType;
string imgName = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") + 1);
byte[] imgBinaryData = new byte[imgLen];
int n=imgStream.Read(imgBinaryData, 0, imgLen);
int RowsAffected = SaveInfo(imgName,imgBinaryData, imgContentType);
if(RowsAffected > 0)
{
..
}
else
{
..
}
}
}
public int SaveInfo(string imgName, byte[] imgbin, string imgcontenttype)
{
SqlConnection objConn = new DSConnection().DbConn;
SqlCommand objCMD = new SqlCommand("sp_SaveInfo", objConn);
objCMD.CommandType = CommandType.StoredProcedure;
objCMD.Parameters.Add("@UserID", SqlDbType.VarChar, 12);
objCMD.Parameters["@UserID"].Value = txtMemberID.Text.ToString();
objCMD.Parameters["@UserID"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Create_DateTime", SqlDbType.DateTime);
objCMD.Parameters["@Create_DateTime"].Value = DateTime.Now.ToLongTimeString();
objCMD.Parameters["@Create_DateTime"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Image_FileName", SqlDbType.NVarChar, 50);
objCMD.Parameters["@Image_FileName"].Value = imgName;
objCMD.Parameters["@Image_FileName"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Image_ContentType", SqlDbType.NVarChar, 50);
objCMD.Parameters["@Image_ContentType"].Value = imgcontenttype;
objCMD.Parameters["@Image_ContentType"].Direction = ParameterDirection.Input;
objCMD.Parameters.Add("@Image_ImageData", SqlDbType.Image);
objCMD.Parameters["@Image_ImageData"].Value = imgbin;
objCMD.Parameters["@Image_ImageData"].Direction = ParameterDirection.Input;
int numRowsAffected = objCMD.ExecuteNonQuery();
return numRowsAffected;
}
View 1 Replies
View Related
Aug 17, 2015
We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..
In sql 2014 can we archive the same..sample format currently we are getting the client is like this is
Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.
View 6 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
Jul 15, 2004
I would like to know , is there a way to save query results in word format.
Liek we use sp_makewebtask to save query output in html format.
Thanks
View 2 Replies
View Related
May 16, 2000
Hi,
I was trying to save the Stored Procedures; however, SQL Server would not save it and gave me the "Error 259: (SQL Server) Ad-hoc updates to system catalogs not enabled. System Administrator must reconfigure system to allow this."
Does anyone know what to do with this error? I went search the error in the Microsoft TechNet Website and did not get any resolution.
Many Thanks in advance!
Choco
View 2 Replies
View Related
Sep 7, 2007
In SQL Server 2000 when we use to create a new procedure it was saved in Management Consule, but when I create a new procedure and wants to save it SQL Server 2005 (Management Console) ... it saved like a query on my local drive ... I am confused what to do ... if i save it on local drive then how can i call it when required.
Thanks in Advance ...
View 9 Replies
View Related
Feb 12, 2008
When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?
I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.
How do you get around this?
Thanks
View 13 Replies
View Related
Mar 3, 2008
Hi 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.
View 11 Replies
View Related
Jul 7, 2006
Is there a way to run query, make file out of query (txt,csv or xls -for example) and save it as a blob into the table column typeImage/Text?Any thoughts would be greatly appreciated.Thanks!
View 5 Replies
View Related
Jul 14, 2007
Hi Guys
I have visual web developer and sqlexpress 2005 installed on my windows XP pro.
I am creating stored procedures through VWD and works fine for me. However today I realize I do not know how to create stored procedures through sqlexpress server managment.
When I try it it wants to save it as file. And if I do that I am not able to see them until manually open each .sql file.
so, could you enlighten me little please.
thanks
Cemal
View 2 Replies
View Related
Feb 11, 2006
Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View 4 Replies
View Related
Nov 14, 2014
I 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.
View 1 Replies
View Related
Jan 29, 2015
I 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] ....
View 9 Replies
View Related
Sep 19, 2006
I 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???
View 1 Replies
View Related
Dec 28, 2005
I 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 Related
Sep 26, 2014
I 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
View 3 Replies
View Related
Jun 26, 2007
I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.
I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?
Thanks,
Nanci
View 2 Replies
View Related
Mar 28, 2007
I 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
View 9 Replies
View Related
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
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Oct 10, 2006
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies
View Related
Nov 15, 2006
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
Thanks,
Kevin
View 3 Replies
View Related
May 18, 2007
This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/2/2007 4:52:19 PM ******/
CREATE PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
IF (@i_WildCardFlag=0)BEGIN
SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
Please help me regarding the same.
Thanks in advance.
Sandeep Kumar
View 2 Replies
View Related
Apr 29, 2008
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks
View 10 Replies
View Related
