Stored Procedure - Security

Dec 22, 2006

Is it a safe way to use a paramter (which fetch values from querystring) in the "where-part" of my stored procedure? Or is it an securityrisk because I dont know what the user is writing in the url-field?   

 I got the following sqldatasource which grab the value (from querystring) into the my parameter.

<asp:SqlDataSource ID="SQLDataSource"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

runat="server"

SelectCommand="My_StoredProcedure"

SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:QueryStringParameter QueryStringField="Myparameter" Name="City" Type="string" />

</SelectParameters>

</asp:SqlDataSource>

 

View 4 Replies


ADVERTISEMENT

Stored Procedure Security?

Oct 31, 2007

Hi all,

this should be a easy question, but I can't really seem to find anything on it...

Here's the scenerio:

n-tier web app, with asp/iis/sql... All database calls are done via stored procedures with the same user (lets call the user: webuser)

webuser has NO access to the db in question, but it is granted EXEC on all stored procedures.

My question is, when a user tells the web app to say delete a record, the application server (iis) makes a call to the database with the webuser security cred's... It says execute the delete stored proc.

webuser has the ability to do this, so it happens. However, in what context (this may not be the right word) does the stored procedure execute?

ie: which user does the stored proc exeucte as. It can't be webuser can it? Because webuser does not have access to the base tables.

Does the stored proc execute as the user that created it?

I'm confused...

thx all!

View 4 Replies View Related

Stored Procedure Security

Mar 19, 2008

Hi,

I have a Stored Procedure in one database that grabs data from another database. I don't want the user to be able to read data from the tables that the stored procedure Selects from but I would like the user to be able to run the stored procedure. Pretty standard request I think.

What I have done is to give the user in question a login then assigned them the Execute Permissions on the stored procedure. Unfortunately they are still unable to run the stored procedure from my web app. I have "allow anonymous access" turned on but I am still getting an error when the user tries to execute the stored procedure.

Am I missing something here or could there be a bigger issue?

Thanks,
Patrick

View 5 Replies View Related

Stored Procedure Security

Sep 4, 2007



Hi,

I have a stored procedure spoc_CreateNewUser.

I have a role called 'creator'. I want that no one else other than creator should be able to execute this procedure through an asp.net application or directly.If anyone attempts, it should be logged.

HOw do I do this. Please explain the answer. I am new to this.

Ron

View 1 Replies View Related

CLR Stored Procedure Security

Jan 11, 2008

Hi,


I created a CLR stored procedure, and added a web service reference, am using the generated proxy class

to call the web service, currently am facing the following security issues. I think am missing

something, what are the possible patterns to call a web service from the CLR Stored procedure.



Thank you



---------------------------------------

The following is the error that is happening each

time I call the stored procedure


A .NET Framework error occurred during execution of

user defined routine or aggregate

'MyStoredProcedure':
System.Security.SecurityException: Request for the

permission of type 'System.Net.WebPermission, System,

Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check

(Object demand, StackCrawlMark& stackMark, Boolean

isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.HttpWebRequest..ctor(Uri uri,

ServicePoint servicePoint)
at System.Net.HttpRequestCreator.Create(Uri Uri)
at System.Net.WebRequest.Create(Uri requestUri,

Boolean useUriBase)
at System.Net.WebRequest.Create(Uri requestUri)
at

System.Web.Services.Protocols.WebClientProtocol.GetWe

bRequest(Uri uri)
at

System.Web.Services.Protocols.HttpWebClientProtocol.G

etWebRequest(Uri uri)
at

System.Web.Services.Protocols.SoapHttpClientProtocol.

GetWebRequest(Uri uri)
at

System.Web.Services.Protocols.SoapHttpClientProtocol.

Invoke(String methodName, Object[] parameters)
at MyWebServiceProxy.HelloWorld()
at MyStoredProcedure()

View 3 Replies View Related

Stored Procedure Security Ideas??

Jan 21, 2004

Does anyone know how you can distribute Stored Procedures that work with a third party application but keep the contents / code of those Stored Procedures from being viewed and edited by the end users who have purchased the application as well as own and operate the SQL Server??

Thanks

View 4 Replies View Related

Stored Procedure Migration And Security

Apr 30, 2008

I have an ASP page which is supposed to populate a dropdown based on values returned from a stored procedure. This ASP page is working on our old server. I recently moved all the pages, code, ect over to our new server including migrated to SQL Server 2005 (from 2000 I think), as you can tell, somthing broke...

I assume this is security related however, all the relevant security is set correctly as far as I can tell. I am granting "Execute" access for the stored procedure and "Select" access for all the referenced tables. Am I missing something? Is there another security setting which needs to be set?

View 1 Replies View Related

Security Hole In Stored Procedure

Jul 20, 2005

here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@pswd varchar(20),@no_go int OUTPUTASSET NOCOUNT ONSELECT user_id FROM profileWHERE user_id=@id AND pswd=@pswdIF @@ROWCOUNT = 0BEGINSET @no_go = 1ENDELSEBEGINSELECT date,date_mod FROM ansWHERE user_id=@idSET @no_go = 0ENDUsing the PERL odbc_more_results function I can retrieve the data inthe second select statement whether the rowcount is 0 or not. Anysuggestions how to stop this

View 3 Replies View Related

Stored Procedure Security Question

Jul 20, 2005

Dear GroupI'm having two stored procedures, sp_a and sp_bContent of stored procedure A:CREATE PROCEDURE dbo.sp_aSELECT * FROM aGOContent of stored procedure B:CREATE PROCEDURE dbo.sp_bSELECT * FROM bGOI have created a user that has execute permissions for both procedures.When I run procedure A, all works fine but when running procedure B I'mgetting an error saying that the user must have SELECT permissions on tableB.Both tables are owned by dbo, and the security role for the user doesn't hasany SELECT permission on table a and b.I'd be grateful if anyone could point me in a direction why this error mightcome up for procedure B but not for A,with a possible solution without giving the user SELECT permissions.Thanks very much for your help!Martin

View 1 Replies View Related

Stored Procedure Only Works In When App Uses Trusted Security

Dec 23, 2005

Hi,I have a .NET application that connects to a SQL 2000 database usingtrusted security. It eventually calls a stored procedure that receives3 parameters - nothing special.If I simply change the connection string to use a valid Userid andPassword it still connects to the DB w/o problems but when it executesthe SP I get the following:System.Data.SqlClient.SqlException: Invalid length parameter passed tothe substring function.I change nothing but the login. Same store procedure, same parameters.Any ideas?

View 6 Replies View Related

More Info: Stored Procedure Security Question

Jul 20, 2005

Dear GroupI have found that table A had SELECT permissions for 'Public' but not tableB.Giving 'Public' SELECT permissions on table B did the trick.HOWEVER, I don't want anyone to be able to do a direct SELECT on table A orB but only give them access to the data by using the stored procedures. Isthere any way this can be set up?Thanks for your efforts!Have a nice day!Martin"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message news:...[color=blue]> Dear Group>> I'm having two stored procedures, sp_a and sp_b>> Content of stored procedure A:> CREATE PROCEDURE dbo.sp_a> SELECT * FROM a> GO>> Content of stored procedure B:> CREATE PROCEDURE dbo.sp_b> SELECT * FROM b> GO>> I have created a user that has execute permissions for both procedures.> When I run procedure A, all works fine but when running procedure B I'm> getting an error saying that the user must have SELECT permissions on> table B.>> Both tables are owned by dbo, and the security role for the user doesn't> has any SELECT permission on table a and b.> I'd be grateful if anyone could point me in a direction why this error> might come up for procedure B but not for A,> with a possible solution without giving the user SELECT permissions.>> Thanks very much for your help!>> Martin>[/color]

View 6 Replies View Related

Security - Stored Procedure - View - Table

Oct 20, 2007

Hi all .
I am going to consult you about a security concept .
In my database I have a set of tables (eg. City , Country , ...)
and I have a corresponding View for each Table (eg. V_City , V_Country , ...)
and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App.

Thanks in advance.

View 9 Replies View Related

Dynamic Security Stored Procedure Repeatedly Called

Jan 26, 2007

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?

View 20 Replies View Related

Question On Internal Activation Stored Procedure Security Context

Jun 25, 2006



CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + """ + AsmName + """ +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

View 13 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

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

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

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

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

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

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

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

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

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

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

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

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

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

System Stored Procedure Call From Within My Database Stored Procedure

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

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

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

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

Need Security Advice On Xp_cmdshell, Bcp, Xml Procedure

Nov 10, 2005

I have a stored procedure that creates an xml file. It executes a SELECTstatement with the FOR XML clause and then writes the xml file using bcp andxp_cmdshell. I am calling this procedure by passing it a parameter via ADO.I have configured the SQL Server Agent with a proxy account so non-SysAdmincan execute xp_cmdshell.I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm alsoconcerned about having to maintain the password on my proxy account whenthat users' password changes.Is there a better, more secure way to generate this xml file.Thanks

View 7 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

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

Security On Stored Procs On Dev Db

Mar 25, 2002

I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?

can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?

this is sql7, sp3, development box.

thanks,

View 1 Replies View Related

Security Stored Procedures

Feb 26, 2004

Hello, everyone:

How to security the stored procedures? I want to prevent the stored procedures to be changed accidently. Thanks.

ZYT

View 5 Replies View Related

Stored Procedures/security

Jul 23, 2005

First off I am a rookie at Sql Server. Ok let's give this a try. Mycompany has bought a new software package called Viewpoint. It's OnSql Server and written in VP. We do not have access to the code.There is a option in the software package called "ApplicationSecurity". When this is clicked you are unable to access the ViewpointDatabase from an outside application. Since I can't get to the code Ihave no idea how this feature works.I would like to keep the "Application Securty" ON so no one can accessthe Viewpoint Database from an outside application but I would like towrite my own outside application where I can access the Viewpointapplication tables while the Application Security in ON. Does anyoneknow what I am talking about? I know it is not very clear and I amworking with limited information, but any help would be appreciated.Thanks

View 1 Replies View Related

Where Is Security Info Stored?

Apr 7, 2008

How does SQL Server store Security Information. Accounts, Users, etc... Is there a particular table, like Master Table?

View 4 Replies View Related

Calling Stored Procedure Fromanother Stored Procedure

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

Use Resultset Returned From A Stored Procedure In Another Stored Procedure

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

SQL Stored Procedure Issue - Search Stored Procedure

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







Copyrights 2005-15 www.BigResource.com, All rights reserved