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
ADVERTISEMENT
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
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
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
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=@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?)
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 24, 2004
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?
Thanks for your help!!
View 6 Replies
View Related
Jul 30, 2007
Hi All :)
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)
and
'KA1001', 'KA1002'
Both fail :(
Any ideas?
Regards
Xo
View 11 Replies
View Related
Sep 12, 2007
Hi all,
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?
View 5 Replies
View Related
Feb 8, 2008
Hello All,
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.
Please help me in this regard.
Thanks,
Subba Rao.
View 11 Replies
View Related
May 24, 2008
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'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]
(@ShippingStateProvince VARCHAR(50),
@ShippingCountry VARCHAR(50),
@StartDate smalldatetime,
@EndDate smalldatetime)
AS
SELECT OrderID,
DateCreated,
DateShipped,
Comments,
Status,
CustomerID,
AuthCode,
Reference,
ShippingCounty,
ShippingStateProvince,
ShippingCountry,
ShippingID,
TaxID,
ShippingAmount,
TaxAmount
FROM Orders
WHERE (DateCreated BETWEEN @StartDate AND @EndDate)
AND (ShippingStateProvince = @ShippingStateProvince)
AND (ShippingCountry = @ShippingCountry)
ORDER BY DateCreated DESC'
END
View 4 Replies
View Related
Aug 9, 2006
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)
View 1 Replies
View Related
Mar 6, 2008
I have a table that looks like this:
RecordId
PictureId
KeywordId
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.
View 7 Replies
View Related
Jan 31, 2006
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
Which is not working.
View 1 Replies
View Related
Jun 11, 2004
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?
Thanks!
View 2 Replies
View Related
Nov 7, 2007
Hello
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?
Kind regards
Jens Ivar
View 2 Replies
View Related
Jun 26, 2007
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?
Your assistance would be greatly appreciated.
View 3 Replies
View Related
