Stored Procedure With Optional/dynamic Where Clause

Apr 21, 2006

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=@TechID
3. Select (fields) from (table) Where OrdNum=@OrdNum
3. 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?)

View 4 Replies


ADVERTISEMENT

Dynamic WHERE Clause To Stored Procedure

May 25, 2004

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:

---------------------------------------------------
@crit varchar(100)

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.

cheers!
pelle

View 2 Replies View Related

Dynamic Where Clause In Stored Procedure

Jul 23, 2004

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.

View 1 Replies View Related

Help With Dynamic Where Clause In Stored Procedure

Aug 20, 2007

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

View 2 Replies View Related

Highly Dynamic Where Clause In A Stored Procedure

Apr 23, 2008

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! 

View 1 Replies View Related

Dynamic Built Where Clause - Error In Stored Procedure

Mar 2, 2005

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.

Here's the code:

CREATE PROCEDURE dbo.ewo_sp_WOLookup
(
@WODUTFK int=NULL,
@WOENDDATE datetime = NULL,
@WOSTATUS char(10) = NULL,
@DEBUG int=0
)

AS

Declare @intErrorCode int,
@chvQuery varchar (8000),
@chvWhere varchar (8000)

select @intErrorCode = @@ERROR,
@chvQuery='SET QUOTED_IDENTIFIER OFF select * from WO',
@chvWhere=''


-- DUT is specified
If @intErrorCode = 0 and @WODUTFK is not NULL
Begin
set @chvWhere = @chvWhere + 'wo_dutfk = '+@WODUTFK
End



IF @intErrorCode=0 and Len(@chvWhere)>0
Begin
set @chvQuery = @chvQuery + ' WHERE ' + @chvWhere
select @intErrorCode=@@ERROR
END

IF @DEBUG<>0
select @chvQuery Query

IF @intErrorCode=0
Begin
exec (@chvQuery)
select @intErrorCode=@@ERROR
End

return @intErrorCode

GO
---

Any suggestions on how to fix?

Thanks,

peter

View 10 Replies View Related

Optional Paranmeters In Stored Procedure

Jun 17, 2008

Hi I have a textbox, and a dropdown list, What i am trying to do is give the user the option of searching based on both (textbox and dropdownlist) or one or the other(textbox or dropdownlist), What i am experiencing though is that when i enter a word into the textbox and select a value from a dropdownlist i get no search results even though the word is "like" the text in the database, it will only work when i type out the full word. What is wrong with my stored procedure, here it is;
 
SELECT S.storeID, S.storeName
FROM dbo.Stores AS SINNER JOIN dbo.storeCategories AS SC
ON S.storeID = SC.storeID left JOIN dbo.SubCategories AS SU
ON SC.subCategoryID = SU.subCategoryID
WHERE S.storeName like ISNULL(@storeName + '%', S.storeName)
AND SU.subCategoryID = ISNULL( @subCategoryID, SU.subCategoryID)

View 5 Replies View Related

Can A Stored Procedure Parameter Be Optional

Mar 7, 2006

I want the procedure to check for the existence of a paramter and if it isthere, it will process these instructions, otherwise it will process theseinstructions. Any ideas? Thanks for your advice.Regards,CK

View 7 Replies View Related

Optional Parameters For CLR Stored Procedure

Oct 2, 2007

How do I specify optional parameters when writing CLR based Stored Procedures in C#?

View 16 Replies View Related

Optional Parameters In A Stored Procedure

Jan 7, 2008

Hi all,

Can anyone please tell me what is the best way to handle optional parameters in a stored procedure which will ensure a good execution plan? I ask this as I have to create a stored procedure with six optional parameters and I'm getting a little concerned that, apart from the parameter issue, I'm going to have a lot of if else statements. Is there an easy way of doing all this?

Thanks

View 5 Replies View Related

SqlDataSource And Optional Stored Procedure Parameters

Oct 12, 2006

Everyone,I have an application that pulls a list of employees into a web form.  I use the same stored procedure to search for employees by various demographic characteristics, and have set all of these search parameters to be optional in the stored procedure.Everything works fine when I pull the entire list (without declaring any select parameters).  However, on my Search page, I have declared a number of SelectParameter (ControlParameters, to be specific) objects and mapped each one to its respective control (text boxes and a checkbox).  When I attempt a search, the SqlDataSource does not select any records unless I specify an argument for ALL of the search parameters.  If I omit even a single search value, the page just posts back and does not pull any information from the database.  I have verified with SQL Profiler that it is not attempting to hit the database.I have experimented with various declarations in the ControlParameter object, including the DefaultValue and ConvertEmptyStringToNull items.  If I specify a non-empty string as a DefaultValue for all of the parameters, it will perform the query, but it does not query if I use an empty string as a default value - unless my user specifies a value in the form. Any ideas?ThanksTim

View 1 Replies View Related

Stored Procedure Optional Search Parameters

May 18, 2008

Hi I want to give the user the ability to search based on a number of criteria, however i do not know how to build my statement properly this is what i have so far;
ALTER PROCEDURE [dbo].[stream_StoreFind]
-- Add the parameters for the stored procedure here
@StoreName varchar(250),@subCategoryID INT
AS
SELECT Stores.StoreName ,StoreCategories.storeIDFROM Stores INNER JOIN
StoreCategoriesON
Stores.storeID = StoreCategories.storeID INNER JOIN
SubCategories ON
StoreCategories.subCategoryID = SubCategories.subCategoryID WHERE
 
My problem is how will i pass the parameters into the statement, taking into fact that sometimes they may be optional. Thank you 

View 12 Replies View Related

Optional Parameters Passed To Stored Procedure

Jul 23, 2005

In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Report Based On Optional Parameters From Stored Procedure

Jan 12, 2008



I have the following stored procedure:



Code Block
CREATE PROCEDURE udsp_td_queryowner
@state varchar(10) = NULL,
@businesstype varchar(20) = NULL,
@size int = NULL,
@sortorder varchar(20) = 'state'
AS
SELECT gl_t.state AS [State],
gl_t.business_type AS [Business Type],
gl_t.lowsize AS [Low Size],
gl_t.highsize AS [High Size],
e.InternetAddress AS [Owner]
FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber
WHERE state = COALESCE(@state, state) AND
business_type = COALESCE(@businesstype, business_type) AND
COALESCE(@size, lowsize, highsize) between lowsize AND highsize
ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state
WHEN @sortorder = 'business type' THEN gl_t.business_type
WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END ASC,
CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize
WHEN @sortorder = 'high size' THEN gl_t.highsize
END ASC,
CASE WHEN @sortorder = 'statedesc' THEN gl_t.state
WHEN @sortorder = 'business typedesc' THEN gl_t.business_type
WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END DESC,
CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize
WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize
END DESC





What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.


If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.


Any ideas?

View 12 Replies View Related

Data Access :: JDBC Stored Procedure Optional Inputs

Oct 4, 2015

When using JDBC is it possible to ignore input parameters in a stored procedure if they have default values?

I can create a string and execute a PreparedStatement but I'd like to use a Callable Statement.

View 7 Replies View Related

Optional Where Clause

Apr 17, 2008

Hi, I'm having a problem trying to do optional items in where clause. I'd appreciate some help, heres the code.

select DFD.Col1 as 'Col1',CMD.Col2 as 'Col2'
from Control as DFD,MetaData as
CMD,Columns as DC,Tables as DT
where DFD.GroupID in
({0}) and

(CMD.MetaDataId = DFD.PrimaryID --
or CMD.MetaDataId = DFD.ForeignID -- These are the problem 3 lines
or CMD.MetaDataId=DFD.HumanColumn) --

and DFD.Disabled='N' and CMD.Disabled='N' and DC.ColumnsId=CMD.ColumnId
and DC.Disabled='N' and DT.TablesId=DC.TableID and DT.Disabled='N'



-- For the problem rows I need to be able to optionally search if they're = to MetaDataID. Right now this query will return only rows that have metadataid = PrimaryID or Foreign, Human. I need to be able to return all rows with the optional dependancies.

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

Dynamic Optional Parameters

Feb 13, 2008

Good day,

I have an issue on constructing dynamic WHERE conditions that use OPTIONAL parameters.

SP_SOMETHING (
1) @DateFrom datetime,
@DateTo datetime,
2) @Param1 char(8),
3) @Param2 char(3),
4) @Param3 tinyint
)

I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together)

1 2 3 4
/ x x x
x / x x
x x / x where x = not supplied
/ = supplied a value

(and so the list continues)
Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements.

thank you

View 3 Replies View Related

Stored Procedure With 'TOP' Clause

May 10, 2004

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

View 8 Replies View Related

IN Clause In The Stored Procedure

Mar 2, 2006

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??

View 7 Replies View Related

Stored Procedure Using IN Clause

Jul 4, 2001

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

View 1 Replies View Related

Use A Stored Procedure In A Where Clause

Feb 27, 2008

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

View 2 Replies View Related

Use A Stored Procedure In A Where Clause

Feb 27, 2008

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

View 1 Replies View Related

Stored Procedure Where Clause

Jul 23, 2005

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.

View 2 Replies View Related

Help With WHERE Clause In Stored Procedure

Jul 23, 2005

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

View 2 Replies View Related

Stored Procedure Parameter And IN Clause

Dec 9, 2003

This works:

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?

View 4 Replies View Related

Like '%abc%' Clause In Stored Procedure Problem?

Mar 22, 2004

I write a stored procedure as:

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)

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

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()

Return myDS
End Function

View 10 Replies View Related

How To Add A Where Clause By Parameter In A Stored Procedure

Aug 1, 2005

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?

View 1 Replies View Related

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.

Thanks,

View 7 Replies View Related

Multiple Stored Procedure...or 1 Dynamic Procedure?

Jul 3, 2007

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
Justin

View 2 Replies View Related

Select Stored Procedure With One Parameter And A Where Clause

Jan 13, 2007

Here is my procedure:
ALTER PROCEDURE dbo.SelectMeds
@RX int
AS
SELECT RX FROM tblMeds WHERE RX= @RX
 
RETURN
but it return no rows, how do I fix this?

View 4 Replies View Related

Stored Procedure With Variable As Only Parameter In Where Clause

Feb 1, 2008

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

View 7 Replies View Related

How To Pass Values For The In Clause To The Stored Procedure?

Apr 7, 2008

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 

View 4 Replies View Related







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