Building Where Clause Dynamically In Stored Procedure
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
ADVERTISEMENT
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 30, 2005
Hi
I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success
For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)
My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),
@smoker tinyint),
@keyword
varchar(250))
AS
DECLARE @SQL Varchar
(4000)
SELECT @SQL = 'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM dbo.user_accounts INNER
JOIN
dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE (dbo.user_accounts.profileComplete =
1)'
IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'
IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'
IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'
IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'
IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'
EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need
Am I Missing something glaringly obvious?
Any help or advice gratefully received
Thanks
View 3 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 10 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;
Code:
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)
AS
declare @StaticStr nvarchar(5000)
set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userID
FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID '
if(@regionID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 2 Replies
View Related
Jul 29, 2015
I am new to SSAS. I have requirement to build a cube based on SQL Stored procedure. This Stored Procedure contains lot of temp tables, which are aggregated as measure columns.
Initially I have done creating views on each temp table, finally I created a view which calls like 15 views. when I try to execute the view, it is taking long time to execute the view.
I tried building cube on this view, when I try to deploy, even it is taking long time to deply..I have waited for 2 hours, still the deployement process going..
What I wonder is, is there any other way I can build cube based on SQL stored Procedure.
View 2 Replies
View Related
Mar 9, 2006
I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF
SET @where = ''
IF @JobNoStart !='' SET @where =+ ' AND LJOB BETWEEN @JobNoStart AND @JobNoEnd'
IF @OrderDateStart !='' SET @where =+ ' AND JOBDATE BETWEEN @OrderDateStart AND @OrderDateEnd'
IF @DueDateStart !='' SET @where =+ ' AND DUEDATE BETWEEN @DueDateStart AND @DueDateEnd'
IF @ProofDateStart !='' SET @where =+ ' AND PROOFDUE BETWEEN @ProofDateStart AND @ProofDateEnd'
IF @CloseDateStart !='' SET @where =+ ' AND CLOSEDATE BETWEEN @CloseDateStart AND @CloseDateEnd'
IF @CogsDateStart !='' SET @where =+ ' AND COGSDATE BETWEEN @CogsDateStart AND @CogsDateEnd'
IF @ProductName !='' SET @where =+ ' AND PRODUCT = @ProductName'
IF @CustomerNumber !='' SET @where =+ ' AND FCUSTNO = @CustomerNumber'
IF @SalesPerson !='' SET @where =+ ' AND FSALESPN = @SalesPerson'
IF @CSR !='' SET @where =+ ' AND JOBPER = @CSR'
IF @Closed = 0 SET @where =+ ' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'
ELSE IF @Closed = 1 SET @where =+ ' AND CLOSEDATE IS NULL'
ELSE IF @Closed = 2 SET @where =+ ' AND CLOSEDATE IS NOT NULL'
IF @Canceled = 0 SET @where =+ ' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'
ELSE IF @Canceled = 1 SET @where =+ ' AND CANCDATE IS NOT NULL'
ELSE IF @Canceled = 2 SET @where =+ ' AND CANCDATE IS NULL'
IF @FinalShip = 0 SET @where =+ ' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'
ELSE IF @FinalShip = 1 SET @where =+ ' AND FINALSHIP IS NOT NULL'
ELSE IF @FinalShip = 2 SET @where =+ ' AND FINALSHIP IS NULL'
SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN WHERE LJOB IS NOT NULL @where
END
View 4 Replies
View Related
Oct 31, 2005
Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1( id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.
View 1 Replies
View Related
Dec 3, 2006
I want to dynamically determine which column I select data from. The table I want to select from looks like this:tblSexualitySexualityID int PKEN nvarchar(20)NL nvarchar(20)DE nvarchar(20)Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"In the sample below the value of @LanguageColumnName is "NL".Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?ALTER PROCEDURE [dbo].[spFindUsers]@LanguageColumnName nvarchar(3),@startRowIndex int,@maximumRows intASBEGINSET NOCOUNT ON;
declare @tblSexualityName nvarchar(40)set @tblSexualityName='tblSexuality.' + @LanguageColumnName SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,IsMale,NearestBigCity,DistanceToNearestBigCity,Description,IsDonator,IsVIP,SexualityName FROM(SELECT ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID) as MemberInfoWHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)END
View 1 Replies
View Related
Feb 28, 2005
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
(
@pageSize int,
@previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN
View 4 Replies
View Related
Sep 22, 2014
I set up a stored procedure. My stored procedure has 8 variables declared at the beginning like this:
===========================
declare@IncurredDateStartsmalldatetime
,@IncurredDateEndsmalldatetime
,@PaidDateStart_CUsmalldatetime
,@PaidDateEnd_CUsmalldatetime
,@PaidDateStartsmalldatetime
,@PaidDateEndsmalldatetime
[Code] ....
All of these dates are based on the update of our data warehouse. This stored procedure runs a 5 step process and produces data for 8 - 10 monthly reports.
I was wondering if these variables can be updated dynamically and if they can how it is done.
View 1 Replies
View Related
Feb 28, 2007
I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable
OBJECTIVE:
Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')????)
Thanks for any help
View 1 Replies
View Related
Feb 1, 2007
I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure?
Thanks,
CG
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
Jul 23, 2005
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies
View Related
Apr 2, 2008
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance
ripern
-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS
DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '
SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END
View 12 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
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
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
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
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
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
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
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
