Depending On The Selection Of The User Of The Asp.net Web-form How Can I Do Changes In The Stored Procedure

May 11, 2007

Hi frdz,  I have created the following stored procedure in sql server 2005. In my database i have one option for the payment mode which can be done thru cash or credit(cheque).   I have created my web-application in asp.net with C# 2005. There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.but if user selects the option as cash then the cheque details become invisible.Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure...   i can write the condition likeif paymentmode=cash then ..........else.............but where and how can it be written ...pls tell methanxs in adv...u can go thru my below SP     ALTER PROCEDURE MiscellaneousStoredProcedure


@miscid int output,
@storename varchar(20),--store name to storeid
@accountname varchar(20),
@groupname varchar(20),
@paymentdt datetime,
@payeename varchar(30),
@paymode varchar(20),
@bankname varchar(50),
@chqdt datetime,
@chqno varchar(20),
@amt numeric(10, 2),
@bal numeric(10, 2),
@remarks varchar(50)

as

declare

@storeid int,
@accountid int,
@groupid int



begin
set nocount on
select @miscid = isnull(max(@miscid),0) + 1 from miscellaneourpay

if exists (select * from storemaster where storename = @storename)
select @storeid = storeid from storemaster where storename = @storename

if exists (select * from accountmaster where accountname =@accountname)
select @accountid = accountid from accountmaster where accountname =@accountname

if exists (select * from accountgroupmaster where groupname=@groupname)
select @groupid=groupid from accountgroupmaster where groupname=@groupname




begin transaction
insert into miscellaneourpay
(
miscid,
storeid,
accountid,
groupid,
paymentdt,
paymode,
payeename,
bankname,
chqdt,
chqno,
amt,
bal,
remarks

)
values
(

@miscid,
@storeid,
@accountid,
@groupid,
@paymentdt,
@paymode,
@payeename,
@bankname,
@chqdt,
@chqno,
@amt,
@bal,
@remarks

)
commit transaction

end 

View 2 Replies


ADVERTISEMENT

One Form To Update Different SQL Tables Based Upon Form Selection

Apr 16, 2008

Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris

View 3 Replies View Related

Table Visiblity Depending On Filter Selection

Apr 21, 2008

H, need help please!

I have two tables on a reports, each having it's own dataset.

I want to set the visibility of the table depending on the filter selection.

So if the user selects: show open then show only the table with the open dataset.

I don't now how to this using the expression in the visibility expression.

Please Assist!

Regards

View 2 Replies View Related

Depending On The Drop Down Selection I Need To Show Another RDL File.

Dec 12, 2007

Hi all,

i have a requirement that user only sees drop down list and a button. when he selects an item from drop down and click button then i need to load rdl dynamically depending on the selection from the DDL.

i have created 3 rdl files, each one contains different content. I need to show the user, the selected rdl from the drop down.

is it possible in SSRS?

if you didn't understand well, feel free to ask.

please help me out.

thanks
-Praveen.

View 5 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

Dynamic Column Selection In Stored Procedure

Feb 10, 2005

Hey Guys,

Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.

DECLARE @querycolumn varchar(20),
@queryvalue varchar(35)

SET @querycolumn = ''
SET @queryvalue = ''

IF(@sub_assembly = NULL)
BEGIN
IF(@part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
SET @querycolumn = 'Part'
SET @queryvalue = @part
END
END
ELSE
BEGIN
SET @querycolumn = 'SubAssembly'
SET @queryvalue = @sub_assembly
END

SELECT SubAssembly, Part, COUNT(RecordID)
FROM Table
WHERE @querycolumn = @queryvalue
GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part

The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?

View 14 Replies View Related

How To Display Data Using Stored Procedure In My Asp.net Form

Jul 29, 2004

I want to display the data in datagrid using the stored procedure,

Can you please tell me, how i can create the stored procedure for the following:
using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)

I want to display the stored procedure data in my Datagrid.

Thank you very much for the help.
the following is complete inline code on my webform.


Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
Dim objCmd As New SqlCommand
Dim dataAdapter As SqlDataAdapter

objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"

objConn.Open()

dataAdapter = New SqlDataAdapter
dataAdapter.TableMappings.Add("Table", "Orders")
dataAdapter.SelectCommand = objCmd

dataSet = New DataSet("Orders")
dataAdapter.Fill(dataSet)
dtgOrders.DataSource = dataSet
dtgOrders.DataBind()

objConn.Dispose()

View 1 Replies View Related

Problem Useing Stored Procedure Form Vb.net

Nov 13, 2006

i have created a stored prcedure but is always give error : and please check the ways is correct using stored procedure

"Procedure or Function 'Add_Cb_Entry' expects parameter '@Date', which was not supplied."

Dim SqlPrm As SqlParameter

Dim SqlCmd As New SqlCommand

With SqlCmd

.Connection = SqlConnection 'this is my connection setting

.CommandText = "Dbo.Add_Cb_Entry"

.CommandType = CommandType.StoredProcedure

End With

SqlPrm = SqlCmd.Parameters.Add("@Cashbook_Id", Nothing)

SqlPrm.Direction = ParameterDirection.Output

SqlPrm = SqlCmd.Parameters.Add("@Date", SqlDbType.DateTime, 8, TxtDate.Text)

SqlPrm.Direction = ParameterDirection.Input

SqlPrm = SqlCmd.Parameters.Add("@Entry_ID", SqlDbType.Int, 4, "4")

SqlPrm = SqlCmd.Parameters.Add("@Entry_Name", SqlDbType.VarChar, 75, "Irfan Imdad Memon")

SqlPrm = SqlCmd.Parameters.Add("@Description", SqlDbType.VarChar, 100, "Chk")

SqlPrm = SqlCmd.Parameters.Add("@Amount", SqlDbType.Money, 13, "1000.20")

SqlPrm = SqlCmd.Parameters.Add("@Type", SqlDbType.NChar, 2, "DB")

SqlPrm = SqlCmd.Parameters.Add("@Entry_Status", SqlDbType.VarChar, 20, "Account")

SqlPrm = SqlCmd.Parameters.Add("@Ref_No", SqlDbType.Int, 4, "1")

SqlPrm = SqlCmd.Parameters.Add("@Ref_Status", SqlDbType.VarChar, 20, "MeterialPurchase")

OpenConnection() 'this is my connection setting

SqlCmd.ExecuteNonQuery()

CloseConnection() 'this is my connection setting

TxtInvoiceNo.Text = SqlCmd.Parameters("@Cashbook_Id").Value

View 1 Replies View Related

Changing Field In A Stored Procedure To Match Name In A Form

May 15, 2008

If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
 There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
 
My stored procedure is the following:
 CREATE PROCEDURE UsersSelectCommand/* (  @parameter1 datatype = default value,  @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName
GO
 The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
 
 
 

View 2 Replies View Related

How To Change View Code Or Table Form Stored Procedure + -

Jun 1, 2008

need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1




Code Snippet

SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)

AS new_unit

FROM dbo.empList




my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1


like create stored procedure name "plus" + so if i run this name stored procedure name "plus"

the stored procedure go to the viewor table and change the code view or table value

so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1

it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value

the important thing is that i can forward +1 or backward -1

evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1

TABLE dbo.empList

empid ShiftType unit_date unit

----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3

TNX for the help

View 7 Replies View Related

How To Get The Null Values In My Stored Procedure I Am Getting Error Dbnullvalue On Front End Aspx Form

Nov 2, 2004

I have the following stored proc. which i am using on the front end to get all the record from table:

if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.


CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO



Thank you very much.

View 2 Replies View Related

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

Feb 4, 2008

Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d    

View 3 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

"Help" File For User Stored Procedure

Mar 18, 2002

I've been administering SQL Server for quite a while, but have managed until now to stay out of the development side. I find myself finally writing some complex SProcs. What I want to do is to create a "switch", i.e., a parameter that will output a few lines of text characterizing the SProc, with such information as required & optional parameters, potential "gotchas", and a handful of examples of usage. I want this to be able to work like this:

EXEC spMySProc '?'
or
EXEC spMySproc 'help'

This has to work even if there are multiple required parameters.

Thanks for your help!

View 1 Replies View Related

Prompt User From Stored Procedure?

Feb 7, 2006

I have a stored procedure that moves specific data from several tables from a database on one server to several others (using a bunch of openrowset queries). I don't have an app to call this procedure - it's usually runs from the query analyzer and the parameter values for the procedure are passed in from there.

The procedure currently skips over recipient tables that are already populated with the data that's being moved. I want the ability to either skip the table or delete the existing records from the recpient table data based on the user's response. Therefore, I somehow need to prompt the user to get a response when the data already exists in the existing table.

Is there any way to prompt the user from a stored procedure, or do I have to re-develop the procedure in DTS or write and application?

View 2 Replies View Related

SQL 2012 :: How To Execute One Query From SP Based On User Selection

Dec 22, 2014

I have a SSRS report with 6 columns each column containing count of total# of applicants meeting certain criteria.
User want to click on each column and see the basic information and also want to get the ability to export the data into excel.

I know that I can create 6 drillthrough reports with basic information of applicants and link it to the count from each column respectively but I was wondering if it is possible to write a Stored procedure with all 6 select queries and execute only 1 select query based on the column that user clicks on main report ?

View 0 Replies View Related

Getting Totals From A User Stored Procedure To Asp.net Code

Dec 1, 2003

Hi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code?
---------------------
Here is part of the cursor I have created.
IF @@FETCH_STATUS = 0
BEGIN
IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1
IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1
IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1

FETCH curs_Briefs INTO @txtPrintSuit
END
------------
How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?

THanks in advance

View 4 Replies View Related

Add User Assembly In Managed Stored Procedure

May 4, 2006

I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??

View 2 Replies View Related

Stored Procedure (DB User/login) Rights

Dec 29, 2004

Does anyone have a SP which will output all users and their permissions (dbo), (read), (write) for each database for SQL 2k? I'm trying to find a way to simply automate this so I can output the data on demand for auditors. I'm currently checking each users permissions on each database through security/logins :(

View 3 Replies View Related

User-Defined-Function With-in Stored-Procedure??

Apr 14, 2008

Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help.


Code:


CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2))
RETURNS VARCHAR(2)
AS
BEGIN
IF (LEN(@sValue) < 2)
SET @sValue = '0' + @sValue

RETURN @sValue
END



Thanks...

View 4 Replies View Related

Execute User Stored Procedure In Master

Nov 26, 2007

I need some clarification regarding the security inside of the master db.

I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.

Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?

Thanks in advance.

View 6 Replies View Related

SQL 2012 :: Executing Stored Procedure As Different User

Nov 26, 2014

I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.

[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] ....

If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.

Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?

If so, how to continue.

In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.

View 2 Replies View Related

Stored Procedure Vs User Defined Functions

Apr 4, 2008

Hi All,

My question is :

Why we are using udf inside stored procedures ?
Will it make any performance faster for the stored procedure to execute ?

awaiting your reply.

Thanks
Renjith

View 6 Replies View Related

Stored Procedure And User-Defined Functions

Sep 25, 2006

lokesh writes "1) What are the differences between "Stored Procedure" and "User-Defined Functions"?

2) Places where we use/don't use Stored Procedure/User-Defined Functions."

View 2 Replies View Related

Stored Procedure To List Out User Access

Jan 19, 2008

Is there a built in stored procedure that would allow me to list outthe database permissions assigned to a particular user or role?

View 2 Replies View Related

Stored Procedure - User Defined Function.

Jul 20, 2005

Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn

View 1 Replies View Related

How To Use User Defined Function In Stored Procedure?

Mar 14, 2006

Hello friends,

I want to use my user defined function in a stored procedure.

I have used it like ,

select statement where id = dbo.getid(1,1,'abc')

//dbo.getid is a user defined function.



procedure is created successfully but when i run it by exec procedurename parameter



I get error that says

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getid", or the name is ambiguous."


Can any body help me?



Rgds,

Kiran.

View 3 Replies View Related

Problems Calling A Stored Procedures Depending On Parameters

Dec 10, 2007

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.
I have a table in SQL that has a list of Storedprocedures:  Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).
On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:
ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS
SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE
Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem.
ThanksPhil
 

View 8 Replies View Related

How To Restrict User To Tab (no Mouse) Selection -question Transferred To VB Forms

Jan 16, 2007

Sorry.

View 1 Replies View Related

Create User Form

Oct 7, 2007

hello there,

Can i create user forms for user to select options and then present them with data based on their selection.i want to be able to use check boxs and combo box.thanks in advance

cheers
zolf

View 1 Replies View Related

Create User Form

Oct 7, 2007

hello there,

Can i create user forms for user to select options and then present them with data based on their selection.i want to be able to use check boxs and combo box.thanks in advance

cheers
zolf

View 5 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

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

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

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

ON SQL SERVER: User-Defined Function

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

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

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

</FLOW WITH CODE AND FUNCTIONS :>

View 5 Replies View Related

SQL Server 2008 :: Allow User Only Alter Stored Procedure

Aug 10, 2015

I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.

I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.

View 1 Replies View Related







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