Syntax Error When Building Up A Where Clause In Stored Procedure

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 = ' + @PersonTypeId

If NOT @Firstname IS NULL
BEGIN
 SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'
END

If NOT @FamilyName IS NULL
BEGIN
 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


ADVERTISEMENT

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

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Alias + Stored Procedure Syntax Error

Jun 21, 2008

Hi,

Im new to this forum and new also to SQL SERVER Edition Express.

Im trying to creat stored procedure. My main problem is that I need to display an alias consisting of 2 fields in a combobox (VB.Net) using also an innerjoin. Can anyone help me find my mistake please


My code is here and the error is :
-----------------------------------------------------------
Msg 156, Level 15, State 1, Procedure LA_suppName, Line 16
Incorrect syntax near the keyword 'INNER'.

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

@supplierFID int,
@supplierID int,
@LANo nvarchar(15) OUTPUT,
@suppName nvarchar(MAX) OUTPUT

AS

BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT tb_LA.LANo, tb_supplier.suppName AS OrderSupplier
INNER JOIN tb_supplier ON tb_LA.supplierFID=tb_supplier.supplierID
Order by tb_LA.LANo

END

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

Eventually I would need to use tb_LA.LANo and make a query to populate the tb_LABooks in another combobox on selectvaluechanged. Is this possible please???


Many thanks

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

Creating A Table Through Stored Procedure Shows An Syntax Error

May 26, 2000

hai guys,
i have written a stored procedure which creates a table ex:
USE PUBS
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC')
DROP PROCEDURE RC_STRPROC
GO
USE PUBS
GO
CREATE PROCEDURE RC_STRPROC
(@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35))
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME')
DROP TABLE @TBLNAME
CREATE TABLE @TBLNAME
(@COLVAL1, @COLVAL2)
GO
it gives an syntax error at '@tblname'
can u guys tell me the problem

thanks
hiss

View 2 Replies View Related

T-SQL Error In Creating A Stored Procedure That Has Three Parameters: Incorrect Syntax Near 'WHERE'

Feb 17, 2008

Hi all,

I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS

IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)

SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.

Please help and advise.

Thanks,
Scott Chang

View 18 Replies View Related

Receiving Error 156 - Incorrect Syntax When Compiling Stored Procedure

May 14, 2008

The following query works fine in query analyzer, but when I add it to my stored procedure I receive an error 156. How do I work around this?

select distinct(dateposted)
from billingprocedures bp1,
billingprocedureordercomponentvalues bpocv,
ordercomponentvalues ocv
where bp1.billingid = @billingid
and bp1.procedureid = bpocv.billingprocedureid
and bpocv.ordercomponentvalueid = ocv.ordercomponentvalueid

Thanks,
Bryan

View 12 Replies View Related

Building A Dynamic Stored Procedure

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

Building A Dynamic Sql Statement Into Stored Procedure

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

Building A Dynamic Query Into A Stored Procedure

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

Analysis :: Building A Cube Based On Stored Procedure

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

Syntax Error On Where Clause

Feb 6, 2005

I have the code below and the where clause is giving me errors, I have already setup FT Index, I think the error is lack of ' or " or "" or some kinda syntax pls help... as I can figure it out

I passed the word MBAISE



where CONTAINS(*, '+@searchstring+' )


Declare @myStatement varchar(8000)
Declare @LastRec int
Declare @FirstRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec =(@CurrentPage * @PageSize + 1 )

SELECT @myStatement = ' SELECT SalaryStatistical, EmployerID,JobLocation, location, SectorID
FROM #TEMP

where CONTAINS(*, '+@searchstring+' )

AND (LastLogin >= dateadd(d, -' + @HowManyDays + ', getdate())
AND (ID > '+ convert(varchar(20),@FirstRec) +')
AND (ID < '+convert(varchar(20),@LastRec) +')
AND (SalaryStatistical > '+ convert(varchar(20),@SalaryStatistical)+')

'
IF @JobLocation != @myzero

BEGIN
SELECT @myStatement = @myStatement + ' AND (JobLocation IN ('+convert(varchar(90),@JobLocation)+')) '
END

IF @sectorID != @myzero
BEGIN
SELECT @myStatement = @myStatement + ' AND (SectorID IN ('+convert(varchar(90),@sectorID)+')) '
END

SELECT @myStatement = @myStatement
print @myStatement

exec(@myStatement)


GO

View 1 Replies View Related

Syntax Error In FROM Clause - Help Needed Please!!

Jun 8, 2008

 Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated.     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)        Dim ds As New DataSet()        Dim sGetRooms As String = "SELECT RoomID, RoomType, " _          & "RoomName FROM Rooms2 " _          & "WHERE RoomType LIKE @RoomType " _          & "ORDER BY RoomType"        Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _          & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _          & "WHERE RoomType LIKE @RoomType " _          & "ORDER BY RoomPrice"        Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString        Using con As New OleDbConnection(sConnect)            Dim da As New OleDbDataAdapter(sGetRooms, con)            Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10)            param.Value = Request.QueryString("RoomType") & "%"            da.SelectCommand.Parameters.Add(param)            Try                da.Fill(ds, "Rooms2")                da.SelectCommand.CommandText = sGetroomsizeandprice                da.Fill(ds, "roomprices")            Catch ex As Exception                Label4.Text = "ERROR: " & ex.Message                Exit Sub            End Try        End Using        Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID")        Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID")        Dim dr As New DataRelation("MenuLink", pkcol, fkcol)        ds.Relations.Add(dr)        DataList1.DataSource = ds        DataList1.DataMember = "Rooms2"        DataList1.DataBind()    End Sub  

View 2 Replies View Related

SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

Jul 20, 2005

Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated

View 5 Replies View Related

Building A Where Clause

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

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

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

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

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

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

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

Stored Procedure Syntax

Jun 27, 2006

This is probably a very simple question but i would appreciate some help
I have the following stored procedure and I am getting the error message
"The objects "aspnet_Users" And "aspnet_Users" in the FROM clause have the same exposed names. Use correlation names to distinguish them"
Can anyone tell me where I am going wrong.
many thanks
Martin
ALTER PROCEDURE [dbo].[SP_CreateOrder](@CartID char (36),@UserId Varchar (50),@OrderTotal decimal (8))
AS
Declare @OrderID int
INSERT INTO  Orders (UserID,Name,Address1,Address2,City,State,Country,Zip,OrderTotal)
SELECT aspnet_Users.UserId,aspnet_Users.Name,aspnet_Profile.Address1,aspnet_Profile.Address2,aspnet_Profile.City,aspnet_Profile.State,aspnet_Profile.Country,aspnet_Profile.Zip,@OrderTotal
FROM aspnet_Users,aspnet_Profile Join aspnet_Users On aspnet_Profile.UserId=aspnet_Users.UserId
WHERE asp_Users.UserId=@UserId
SET @OrderID = @@IDENTITY
INSERT INTO OrderDetails (UserId,OrderID,ProductID,Name,Cost,Quantity)
SELECT  @UserID,@OrderID,Products.ProductID,Products.Name,Products.Cost,ShoppingCart.Quantity
From aspnet_User,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE  aspnet_User.Userid=@UserId AND ShoppingCart.CartID = @CartID
EXEC SP_DeleteShoppingCart @CartIDSELECT @OrderIDGO
 

View 2 Replies View Related

Does This Stored Procedure Syntax Look Okay?

Dec 7, 2006

Does this Stored Procedure Syntax look okay?I'm trying to call it using classic asp with the following code but get an error on calling the stored procedure:set objComm    = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = sstrDsnString 'You can also just specify a connection string here
objComm.CommandText = "editorTAmonthlyReport" ' stored procedure name
objComm.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
'Add Input Parameters to date and from date variables - dtFrom and dtTo
objComm.Parameters.Append.CreateParameter("@dtFrom", adDate, adParamInput, 9)
objComm.Parameters.Append.CreateParameter("@dtTo", adDate, adParamInput, 9)
 CREATE PROCEDURE editorTAmonthlyReport
@FromDate DateTime,
@ToDate DateTime
As

Select
U.UserID,
U.Title,
U.FirstName,
U.Surname,
TAU.UserPosition,
U.Email,
A.AgencyName,
A.AddressLine1,
A.AddressLine2,
A.Town,
A.County,
A.Postcode,
C.Country,
Coalesce(U.Phone,A.Phone) As Phone,
TAU.DateOfCreation
From
[user] U LEFT OUTER JOIN MyTrafalgarUser MTU
   ON MTU.userID=U.UserID
INNER JOIN TravelAgencyUser TAU
   ON U.UserID=TAU.UserID
INNER JOIN Agency A
   ON TAU.AgencyID=A.AgencyID
INNER JOIN Country C
   ON A.CountryID=C.CountryID
Where ( u.userid > 34657 AND DateDiff(d,TAU.DateOfCreation, @FromDate) < 0 )
and datediff(d,TAU.DateOfCreation, @ToDate) > 0
ORDER BY TAU.DateOfCreation
GO

View 2 Replies View Related







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