Can't Use Stored Procedure In Query Where Clause???
Jan 17, 2008
Hi,
By reading answers on the web I have found out that I can't use a stored procedure in a where clause of my query, but I can use a User defined function. This almost fits my needs but not quite. The function would work great if it could insert the results of its query into our cache table but you can't insert stuff into external tables to the function.
The problem is that our stored procedure/function does looping to find parent objects way back up the tree to find out permissions for certain records. Since the stored procedure and function do so much querying to find the root most object that has permissions set there is a lot of reads in our call. We would like to cache this process so that next time they look for permissions it only does one read first. But in order for our caching to work the function needs to insert the results it found in our cache table which it can't do and the stored procedure can't be used in a where clause so that doesn't work. Any suggestions?
Query looks like this, the query is built on the fly through code.
select Title, Descriptions FROM defects df WHERE dbo.fnHasProjectRights(df.ProjectID);
and that function first checks the cache table to see if it has ran before for that projectID and if not then starts doing all its logic to get permissions.
Any suggestions how to approach this? I just wish functions could insert and or stored procedures could be used in the where clause since they can insert.
I'm trying to create a stored procedure which has the 'TOP' clause, in SQL Server 2000.The syntax is
CREATE PROCEDURE SPGetRemainingRecordsB @Remain int
AS
exec('SELECT TOP' + @remain + 'logdetailid FROM boxdetail WHERE logdetailid in (SELECT TOP' + @remain + 'logdetailid FROM boxdetail ORDER BY logdetailid Desc) ORDER BY logdetailid ASC') GO
Syntax check is ok,but i get an error "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified
I am doing something like this: idlist is the list of id's(intergers) create proc spTest(@idlist varchar(1000))asbeginselect * from stuwhere id in (@idlist)end exec spTest '1,2,3' But I am getting an error saying that cannot convert a varchar to int. I think its just some syntax that I am missing. Any clues on doing this??
hi i'm new to this so if i'm missing something please go easy on me!! i'm using access97 and sql server 7 i have a stored procedure that i want to pull back a list of details, to do this i have constructed a sql statement which uses the in clause ie select * from tblx where tblx.strname in (xxxxx) i have created and declared a variable called strName so my statement now reads .... select * from tblx where tblx.strname in (@strName) ....
can i pass accross many values in the @strName variable?? - there might be one value there might be twenty - i know using vba how to put the values into my pass through query (which calls the sp), but i can't get the syntax right for sql server to accept this as more than one value (it works fine with a single value)
can any one help - if not i might have to go back to linked tables again which i was trying to escape from thanks mike
I'm trying to write a stored procedure that uses a second stored procedure in its where clause. I have a stored procedure that accepts two parameters and outputs a float. What I'd like to do is have a stored procedure that accepts one parameter and has a select statement such as: Select * from table WHERE STOREDPROCEDURE(@param1,table.field)>5
If anyone can give me some advice I'd apprectaite it. Thanks
I'm trying to write a stored procedure that uses a second stored procedure in its where clause. I have a stored procedure that accepts two parameters and outputs a float. What I'd like to do is have a stored procedure that accepts one parameter and has a select statement such as: Select * from table WHERE STOREDPROCEDURE(@param1,table.field)>5
If anyone can give me some advice I'd apprectaite it. Thanks
I have an existing query from MS Access that I want to convert it toSQL Server Stored Proc. My problem is on how to convert the WHEREclause.This is the query from MS Access:SELECT SchYrSemCourseJoin.SchYrSemCourseID, Students.IDNo, [LastName]& ", " & [FirstName] & " " & [MiddleName] AS Name,Program.ProgramTitle, Program.ProgramDesc, SchYrSem.SchYr,SchYrSem.Sem, SchYrSem.Year, SchYrSem.Section AS Section1,Major.Major, Course.CourseCode, Course.CourseTitle, Course.Unit,SchYrSemCourseJoin.Final, SchYrSem.SchYrSemIDFROM (Program INNER JOIN Students ON Program.ProgramID =Students.ProgramID) INNER JOIN ((Major INNER JOIN SchYrSem ONMajor.MajorID = SchYrSem.MajorID) INNER JOIN (Course INNER JOINSchYrSemCourseJoin ON Course.CourseID = SchYrSemCourseJoin.CourseID)ON SchYrSem.SchYrSemID = SchYrSemCourseJoin.SchYrSemID) ONStudents.IDNo = SchYrSem.IDNoWHERE ((([LastName] & ", " & [FirstName] & " " &[MiddleName])=[Forms]![Rating Report Dialog]![SubName]) AND((SchYrSem.Year) Like IIf(IsNull([Enter Value]),"*",[Enter Value])));This is a stored proc that I have currently created:CREATE PROCEDURE dbo.Rating@LastName nvarchar(50)AS SELECT SchYrSemCourseJoin.SchYrSemCourseID, Students.IDNo,[LastName] + ', ' + [FirstName] + ' ' + [MiddleName] AS Name,Program.ProgramTitle, Program.ProgramDesc, SchYrSem.SchYr,SchYrSem.Sem, SchYrSem.Year, SchYrSem.Section AS Section1,Major.Major, Course.CourseCode, Course.CourseTitle, Course.Unit,SchYrSemCourseJoin.Final, SchYrSem.SchYrSemIDFROM (Program INNER JOIN Students ON Program.ProgramID =Students.ProgramID) INNER JOIN ((Major INNER JOIN SchYrSem ONMajor.MajorID = SchYrSem.MajorID) INNER JOIN (Course INNER JOINSchYrSemCourseJoin ON Course.CourseID = SchYrSemCourseJoin.CourseID)ON SchYrSem.SchYrSemID = SchYrSemCourseJoin.SchYrSemID) ONStudents.IDNo = SchYrSem.IDNoWHERE ((([LastName] + ', ' + [FirstName] + ' ' +[MiddleName])=@LastName)) ReturnGOMy problem is on how can I add the second criteria which is the FieldYear on my stored proc. The query above (MS Access) returns all therecords if the Parameter Enter Value is null.Anyone know how to do this in stored proc? I want to create a storedproc that will have the same results as the query above.Thanks in advance.
Hi,I have an sp with the following WHERE clause@myqarep varchar(50)SELECT tblCase.qarep FROM dbo.tblCaseWHERE dbo.tblCase.qarep = CASE @myqarep WHEN '<All>' THENdbo.tblCase.qarep ELSE @myqarep@myqarep is returned from a combo box (ms access)...the user eitherpicks a qarep from the combo box or they leave the default which is'<All>'they problem i'm having is that if the record's value fordbo.tblCase.qarep is null...the record does not show up in theresults...but i need it toany help is appreciated.thanksPaul
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005'))
This doesn't:
WHERE WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears))
@strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?
select * from tableName where firstName like '%' + @keywords + '%' (assuming @keywords is declared with varchar)
when I use QA, it runs perfect and returns something that has words in between for matching up firstName, but when I use with the following code (Data access layer) it wouldn't return.. it will only return the matched text.. (ex. if i input 'ke', it suppose return kelvin, kelly, okey something like that, but somehow it only retunrs the whole words that's matched)
Is there something wrong? The code for DAL is as follows.
Public Function GetOrderList(ByVal keywords As String) As DataSet Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlDataAdapter = New SqlDataAdapter("sp_GetList", myConn)
Dim paramKeywords As SqlParameter = New SqlParameter("@keywords", SqlDbType.NVarChar) paramKeywords.Value = keywords myCommand.SelectCommand.Parameters.Add(paramKeywords)
Dim myDS As New DataSet myConn.Open() myCommand.Fill(myDS) myConn.Close()
Hi all! I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:
SELECT fldID, fldName FROM tblUsers WHERE @crit ----------------------------------------------------
Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.
Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @Path, @Status, @Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.
What i want is to add by parameter a Where clause and i can not find how to do it!CREATE PROCEDURE [ProcNavigate]( @id as int, @whereClause as char(100))ASSelect field1, field2 from table1 Where fieldId = @id /*and @WhereClause */GOany suggestion?
I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run.
at this point i keep getting errors in the section bolded below it never seems to recognize anything after the if @myColours <> 'SelectAll'
CREATE Procedure PG_getAdvWheelSearchResults3 ( @SearchDiameter NVarchar( 20 ), @SearchWidth NVarchar( 20 ), @minOffset int , @maxOffset int , @boltpattern1 NVarchar( 20 ), @VendorName NVarchar( 40 ), @myColours NVarchar( 40 ) ) As BEGIN TRANSACTION SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image, dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2 FROM WHEEL_CHARACTERISTICS INNER JOIN dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y') AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039') AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter) AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth) AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1)
if @myColours <> 'SelectAll' and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours end if
AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset)
ORDER BY CLIENT_WHEEL_PRICES5.Price asc COMMIT TRANSACTION GO
Anyone know how i should word the if...statements? I have not found anything that works yet. Thanks
Hello, I want to execute a sproc where the query statement goes something along these lines: SELECT * FROM myTable WHERE @aVarCharVariable @aVarCharVariable contains column names and their possible values How do I achieve this? Cheers! /Eskil
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
I have a situation where I'll need to get results from tables based on totally arbitrary filters. The user can select the field to compare against, the value, the comparison operator, and the boolean operator, so each bit in brackets would be configurable:[field] [>] [value] [and]The user can specify an arbitrary number of these, including zero of them. I like the coalesce function for situations that are a little more structured, but I think I'm stuck generating a dynamic query for this -- please correct e if I'm wrong!
If I do this with a function and multiple inline sql statements, I could probably do it much easier, but here at work, sprocs are required, and I can't seem to stretch my knowledge and Google searches far enough to find the answer. Plus, I don't really think that creating 4 separate sProcs is the most efficient way of doing this I need to select and return 8 columns from a table, but the problem is I need to feed the sProc parameters in such a way, that I can use different criteria in the Where Clause. for instance, I need to combine these 4 select statements into one:1. Select (fields) from (table) Where TechID=@TechID and Status=@Status)2. Select (fields) from (table) Where TechID=@TechID3. Select (fields) from (table) Where OrdNum=@OrdNum3. Select (fields) from (table) Where CustNum=@CustNum In all instances, the fields and the table are the same - how can I combine all these possible Where clauses (if/then - Select Case?) so that it's only one Stored Procedure? (or, is this even possible?)
Hi, I need to include two input variables in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me to do so and gives an Error 1008. How can i solve this problem?
I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.
I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.
If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.
I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:
KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)
I'm trying to build a Where clause in a stored procedure based on the information that is passed into the stored procedure. Because I don't know how many items will be passed into the stored procedure, I'm having to split the string on a specific character and build the Where clause based on how many strings are found.
However, when I try to execute the Where clause it throws an error.
Code Snippet
ALTER PROCEDURE getUsersAddress -- Add the parameters for the stored procedure here @LName varchar(1000) @City varchar(1000), @State varchar(1000), @License varchar(1000)
AS
declare @Count as int declare @x as int declare @wLName as varchar(2000) declare @wCityas varchar(2000) declare @wStateas varchar(2000) declare @wLicense as varchar(2000)
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
if right(rtrim(@LName),1) <> ';' begin set @LName = @LName + ';' end
set @Count = PATINDEX('%;%',@LName)
set @wLName = '(tblUsers.LName = ''' while @Count <> 0 begin set @wLName = @wLName + left(@LName, @Count - 1)
set @LName = stuff(@LName, 1, @Count, '') set @Count = PATINDEX('%;%',@LName);
if @Count <> 0 begin set @wLName = @wLName + ''') OR (tblUsers.LName = ''' end else begin set @wLName = @wLName + ''')' end end
print cast(@wLName as varchar(5000))
-- Insert statements for procedure here SELECT tblUsers.FullName, tblUsers.Addy1, tblUsers.Addy2, tblUsers.City, tblState.StateAbbr, tblUsers.Zip, tblUsers.Zip4 FROM tblUsers INNER JOIN tblState ON tblUsers.FK_StateID = tblState.StateID INNER JOIN tblUserDetails ON tblUsers.UserID = tblUserDetails.FK_UserID WHERE (@wLName) OR (tblUsers.City = @City) OR (tblState.StateAbbr = @State) OR (tblUserDetails.CDLType = @License) END So when I print do an exec getUsersAddress 'Johnson;Smith', 'City;Test City', 'TX;OK', 'Class A;Class B'
@wLName comes out as (tblUsers.LName = 'Johnson') OR (tblUsers.LName = 'Smith')
However, I can't save the procedure as it gives me an error of: An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.
When I copy and paste the @wLName value in place of @wLName in the Where clause it works, so how can I get the @wLName variable to work in that Where clause?
I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria.
WhereClause from UI: whereClause="where DefectImpact='High'"
SQL Query in SP: SELECT @sql='select * from tablename'
Exec(@sql + @whereClause )
Here i am not able to get the results based on the search criteria. Instead i am getting all the results.
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.
Thanks for the help.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP. I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int." Thanks Neil CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int) AS Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000) CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int ) SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName' SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId) SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId FROM vw_GetPersonsByCategoryAndType ' EXEC (@SelectClause + @WhereClause +@OrderByClause)
111 212 313 421 522 623 725 817 932 1044 I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2.
Im going crazy trying to find a simple solution for this. Please advise.
How to pass value for the “in� clause in the SQL server stored procedure? Let us consider the following code.
declare @Country varchar(200) set @CountryIds='1,11' select CountryName from COUNTRY where CountryId in (@CountryIds) GO
In the above code, the lists of country ids are needed to feed from the front end. In the above example, we need to select countries for 1 and 11. So, how to pass this from front end and what will be the data type of the parameter. I am using front end as .Net. Please help in this regard. The stored procedure what I wrote was.
CREATE PROCEDURE [cmp].[Events_By_Country] @CountryIds varchar(200) AS select CountryName from COUNTRY where CountryId in (@CountryIds) GO
I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:
CREATE PROC sp_test @in_company_code nvarchar(1024) AS
select company_code, name, description from member_company where company_code in (@in_company_code)
However, I tried the following :
exec sp_test 'abc', 'rrd', 'bbc'
Procedure or function sp_test has too many arguments specified.
and SQLServer doesn't like it.
Did I specify this stored procedure correct? If so, how can I can pass multiple values to the stored procedure then to the sql statement? If not, is it possible to specify a stored procedure like this?
I'm sending a stored procedure a number of parameters, including an int for a foreign key field. I'm trying to dynamically build the Where Clause of the query.
I'm getting the error:
Syntax error converting the varchar value 'wo_dutfk = ' to a column of data type int.
NOTE: wo_dutfk is an int field within the WO table.
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField FROM myTable WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.
I want this login to be able to execute the stored procedure, sp_helplogins.
The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.
I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.
What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.
Therefore, what I am attempting is the following:
I create the login, SecAdmin, and assign it to the securityadmin server role.
Then I right click sp_helplogins and select 'Modify'.
I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.
I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.
Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.
Line 73 is blank, but the next few lines read:
if (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
select @RetCode = 1
goto label_86return
end
What am I doing wrong? Is their a better alternative to accomplish what I want to do?
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
